<< Free Ad-Aware Pro Internet Security 1 year genuine license key worth $29.95 | Two New Windows 7 themes from Microsoft - Arctic and Fireworks >>

SQL Server 2008 - Reducing log file size

Posted on Tuesday, December 28, 2010 by Krish Tags: sql server, programming
Category: Programing, SQLSERVER

Today we had a server space issue, and I found that Log file of a Database is nearly 16GB and which caused the issue. As usual I tried to Truncate the log file using

DBCC SHRINKFILE(Log-Name, Size)

Which did not do the trick. So I had a search and found a solution on Sql Authority which says that following code will fix the issue

USE DatabaseName GO DBCC SHRINKFILE(<TransactionLogName>, 1) BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY DBCC SHRINKFILE(<TransactionLogName>, 1) GO

again that showed an error 'Truncate_Only' is not a recognized BACKUP option., So searched again and found that that code is for the SQL Server versions prior to 2008. So searched again and found a blog Experiencing SQL Server 2008, which says on SQL Server 2008, you will have to check the Recovery Model, if it is Full you will have to change it to Simple Truncate the Log file and then again set is back to FULL if you want.