Shrinking a Large MS SQL Server Database Log File
Mar
13
Written by:
3/13/2010 2:09 PM
I recently had some trouble with some extremely large SQL Server Log Files. I tried using the normal Shrink File tool but it would not work. Here is the proper way for shrinking SQL Server Log files.
It is only possible to truncate the SQL Log file after a backup. The simple code below will do just that for you.
USE DBName
GO
DBCC SHRINKFILE(<LogName>, 1)
BACKUP LOG <DBName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<LogName>, 1)
GO
If you have a maintenance plan set for backing up your databases, you can modify it to automatically truncate the file after backup.
If you receive 'TRUNCATE_ONLY' is not a recognized BACKUP option. Please see this blog post in regards to SQL 2008, http://repetti.net/Home/tabid/40/EntryId/13/TRUNCATE_ONLY-is-not-a-recognized-BACKUP-option.aspx
1 comment(s) so far...
Re: Shrinking a Large MS SQL Server Database Log File
Thanks a lot for this simple yet very very useful solution.
By Tianna on
6/16/2010 8:33 AM
|