How to shrink MS SQL Server 2005 Database Log

by zebsadiq 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.

Tags:

SQL Server 2005

Comments

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Calendar

<<  September 2017  >>
MoTuWeThFrSaSu
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678

View posts in large calendar