Shrinking a transaction log file in MSSQL 2008

This is always a problem with MS SQL, the log file usually grows bigger than the data file itself.  Sure, it is very important in recovery.  But do you really need it, if you have full back-up? You will only need the log file to restore a deferential back-up.


Before in MS SQL 2000 and 2005, I use the following query (before MS SQL 2008):

BACKUP LOG DBname WITH TRUNCATE_ONLY

DBCC SHRINKFILE(DBname_Log, 1)

And that did the trick, but 'TRUNCATE_ONLY' is not recognized by MS SQL 2008 anymore.

You can use 'ALTER DATABASE' and setting the recovery to 'SIMPLE' then do the shrinking, otherwise by just using the 'DBCC SHRINKFILE(DBname_Log, 1)' query, there won't be any effect.

After you do the shrinking, return the recovery back to full, as such: 'SET RECOVERY FULL'.

This is only one of the solutions found on the web, there could be others involving running your own stored procedures.  But I prefer a simple few line approach.  Solution was found here: http://msdn.microsoft.com/en-us/library/ms189493.aspx

Here is the solution I used to shrink my log file on a MS SQL 2008.


ALTER DATABASE dbname
SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (dbname_log, 1)
GO

ALTER DATABASE dbname
SET RECOVERY FULL;
GO

what happened here is a shrinking of a log file to a specific target size.  This sample code was found in the solution link I provided above from a Microsoft website as Example B.

Others, suggest you backup the log file first before you shrink it.  But a 300MB backup took me a long time, what if it is 5GB of log file?

Anyways, if you prefer to back it up first, you could add this code before you alter the database recovery:


BACKUP LOG dbname TO DISK = N'D:\dbname_log.bak'
GO

You may also, run this to view the database info:

EXEC sp_helpfile

It will show the logical name, fieldid, filename(path), filegroup, size, maxsize, growth, and usage.

Hope you found your solution here. Till next time.