Truncate SQL Logs

I am off today.

Yeah, right.

The webmaster called me in a panic because the Content Management Server was really poking along and causing people to complain of web-site slowness. He determined that we needed, "more RAM, more processors, or maybe a new machine." Which I doubted.

I looked around at the MS SQL Server 2000 that resides behind the CMS and found an 18GB log file slowly growing larger. What to do, what to do?

Truncate the Logs.

When you truncate the transaction logs for a database you are, in essence, telling the database to stop worry about any transaction logs that are older than those that will fit in the allotted space so, there are some important steps that need to be taken before you do this.

First, you must make sure you have a good backup up the db in question. If your last scheduled full backup (and you DO have scheduled backups...right) was relatively recent, you should be fine. In this case, I had one taken on Sunday night. However, if you do not have a good backup, you can quickly run one by either re-running your existing job or just creating a new one. This is important. You MUST alter the RETAINDAYS statement in your existing job to be fewer than the number of days until the next backup. For instance, if you run backups once a week on Sundays, your RETAINDAYS is probably set to 6. If you want to run a job on Wednesday, you would have to alter the RETAINDAYS value to 3, otherwise your backups on Sunday will not run, since the last backup has not yet expired.

If you would rather just run a special job, you can do so rather easily as below:

USE %databasename%;
go
BACKUP DATABASE [%databasename%] TO [%backupdevice%]
WITH FORMAT

%databasename% and %backupdevice% will need to replaced with parameters from your site. The 'use' statement above is a bit redundant, but I always use it in order to be POSITIVE I am dealing with the right DB. Also note that using the WITH FORMAT option will overwrite anything you had previously stored to that device.

Assuming you do not have a backup device built, you can backup to a file directly with:

USE %databasename%;
go
BACKUP DATABASE [%databasename%] TO DISK = 'C:\Path\to\file.bak'
WITH FORMAT

Again, place the appropriate names in the appropriate places.

Once that is done, you will need to actually truncate the logfile. This is done with the code shown below:

USE %databasename%;
BACKUP LOG %databasename% WITH NO_LOG
go;

Rather than using NO_LOG you could use TRUNCATE_ONLY, they are equivalent in this case.

Now that you have gotten rid of the excess transaction logs, you will need to let the database know you have done so and that it can release the space on the disk. Time for some DBCC!

USE %databasename%;
DBCC SHRINKFILE (%logfilename%|%logfileid%);
go;

You may use the fully qualified log filename or you may use the file id. I usually use the file id because it is a smallish number. In my case, it was '2.' You can find the fileid in the sysfiles table in the database you are working with. Like so:

SELECT fileid,
size,
filename
FROM sysfiles

You will now need to make another full backup, as shown above, in case you have some disaster between now and the next backup, you won't need to re-truncate your logs. Also, since most of the transaction logs were just dropped, you need to run a full backup to have a valid retore path anyway.