Wednesday, September 16, 2009

Shrinking Transaction Log File in SQL SERVER

Sometimes it is necessary to shrink a transaction log in a database manually if the log file grows unexpectedly in order to save some disk space.

Here are the steps use to truncate transaction log in a database:

BACKUP LOG <databasename> TO DISK = '<backupfile>'
DBCC SHRINKFILE (<filename>, <targetsize>) WITH NO_INFOMSGS


e.g.:
BACKUP LOG NORTHWND TO DISK = 'C:\NorthwindLog.bak'
DBCC SHRINKFILE (Northwind_log, 100) WITH NO_INFOMSGS


To check your logical file name of your log file, you can run the query as below to get the logical file name something like 'xxx_log':
sp_helpdb '<databasename>'


More information:
http://msdn.microsoft.com/en-us/library/ms189493.aspx
http://support.microsoft.com/kb/907511
 

Get paid for your opinions! Click on the banner above to join Planet Pulse. Its totally free to sign up, and you can earn UNLIMITED. Find out more by visiting PLANET PULSE.
July Code Blog Copyright © 2010 Blogger Template Designed by Bie Blogger Template