Technology, Smartphones, Games


SQL Server 2008 - Reducing log file size

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.

Read more and see the sample queries too here