Skip to main content

Growing Log file issues - How to shrink a SQL server Log and data file.


Sometimes it is necessary to do this via a script as the UI does not work for reasons such as the DB is being in used. This method sets the recover mode and then shrinks the log and data file.

Before doing this backup your databases using Management studio
Open up a query window associated with the database with the large transaction log. (Right-click on the DB and choose new query.)

Get the logical name of the transaction log file. (Right-click on the DB, select Properties, then in the Files screen, grab the Logical Name… probably ends in something like _Log.)



Execute the following, substituting with the appropriate logical name of the database log file, no quotes needed:

Use [Mydatabase]

ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE

DBCC SHRINKFILE(<log_file_name_Log>)

ALTER DATABASE [mydatabase] SET RECOVERY FULL




 The following screen shows the log file before the shrink.
Log file after shrink
Points of interest
  • Transaction logs have to be backed up in order for them to ‘truncate’. If they are not backed up regularly, they will eventually fill the disk
  • a full backup alone will not do the trick – that does not touch the transaction log
  • transaction logs must be backed up via ‘BACKUP LOG’
  • if the dba does not require transaction log backups, it is advisable to move to Simple Recovery
A little more info here:



Comments

Popular posts from this blog

Using SSRS web services to render a report as a PDF

I have been looking around the net for some decent code which would explain how I could render a report, using SSRS 2008 web services as a PDF. The need was to extract reports sitting on a SSRS 2008 server sitting on a NT domain on a trusted network, whereas my web server was sitting in a DMZ. Where the only communication allowed by the network admin was port 80. To do this you will need to use the SSRS2008ReportExecution2005.asmx web service. This could be accesses using the following URL assuming your SSRS server was installed using the default settings. http://YourServerIP/reportserver/reportexecution2005.asmx?wsdl 1.Create a user on your AD domain with the least amount of privileges (say ReportUser) 2.Give this account browse access on the reporting server for the desired reports. 3.To get this working in visual studio 2010 (I am using the Premium edition) Right click on your project and add a service reference to the above web service URL. (YourSSRSServer) 4.Once the above is done.…

Integrating MIGS eGate Payment gateway using C# for Oz businesses (Server-Hosted Payment method)

If you ever wanted to include the eGate payment gateway in your ASP.Net site and want to save yourself the time in creating the class, here’s how to do it.
First things first you will need a merchant account with ANZ who will give you the following ·Merchant Account ·User name ·password The details could be downloaded from the ANZ web site http://www.anz.com.au/australia/business/merchant/DownloadDevKit.asp I am doing this for an Aus base client thus the process might be different to your own country. Please check with the issuing bank for details.
Have a read of the Merchant Admin guide. Login to the merchant account and create an operator ID, Login as the operator and obtain the access code. I will not going to the details as this is all documented in the admin guide.
What you will need for the implementation would be vpc_AccessCode vpc_Merchan MIGS gateway URL:https://migs.mastercard.com.au/vpcdps

The site has numerous examples of the PHP, HTTP post and Asp examples. The class was based on …

Creating a Link Server to MYSQL from MSSQL

There might be situations where you need to apply CRUD  operations (
Create,Read,Update,Delete) on data residing in a MYSQL server database from SQL server. In order to do this you will need to create a link service from SQL server to MYSQL. The Steps are outlined below:

Steps:
1. Install the MYSQL ODBC driver on the server with the MSSQL server installation.
2. Enter details for the MYSQL ODBC settings.
3. Create a link service to MYSQL using SSMS ( SQL Server Management Studio)
4. You will need an user account on the mysql server which can perform CRUD operations
 along with enable remote connections to the MYSQL installation. (See Troubleshooting steps below)
4. Test the connection.


Install MYSQL ODBC Driver Download  (http://dev.mysql.com/downloads/connector/odbc/) and install the latest MYSQL ODBC driver from the MYSQL server site. Select the version which corresponds to your installation of MSSQL usually the 64bit version. Note This needs to be done on the server with the MSSQL installed…