3. February 2011 00:07
Recently I had to investigate why a certain virtual machine’s hard drive had filled up. I discovered that this was caused by a massive SharePoint database that I had imported. The log file for this database was nearly 30gb.
Please note: Since writing this post, I have discovered that this code does not work with SQL Server 2008. For code that works with SQL Server 2008, please see: How to shrink SQL Server 2008 Database log. Please also note that you should not be running the code below in a production environment. This should only be done to help with development.
The way to check the log file sizes is by going to: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\
Once you’ve determined which database’s log file needs to be shrunk, run the following bit of SQL replacing the database name with your own.
declare @dbname nvarchar(255)
set @dbname = 'REPLACEWITHDATABASENAME'
backup log @dbname with truncate_only DBCC SHRINKDATABASE (@dbname, 0)
After shrinking the log file, the size of the file went down to just 500kb.