Friday, September 11, 2009

Shrink database log file

Even though from SQL manager we have the option to shrink database log files, sometimes it does not work as the log files size is big. Then you need to use TSQL statements to do this. I have given below such a statement that can be used to do so.


USE Test1
GO
DBCC SHRINKFILE(Test1_log, 10)
BACKUP LOG Test1 WITH TRUNCATE_ONLY
DBCC SHRINKFILE(Test1_log, 10)
GO

if you do not know the log file name use the following command to find it.

select * from sys.database_files

I was helped by the following link.
http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/


No comments: