SQL 2005 – Truncate Log Files and Recover Space

Here is a useful set of scripts to help you to shrink up your log files for SQL 2005 databases.  I used this recently on my HP SIM (Systems Insight Manager) database and it freed up over 4 Gigs.  I want to note that using this method won’t be ideal for every environment, situation or database, but as the DBA you should know what will and won’t work for your setup.  If you don’t then you need to educate yourself about your environment first.  That being said:

Steps to follow for truncating log files and shrinking a database:

1.  Get the physical names of your database file (MDF) and log file (LDF):
Open up the SQL Management Console and create a new query:
Run the following system stored procedure, replacing <…> with your database name:

use <yourdatabasename>
exec sp_helpfile

This command returns the size of the database (“size” column) and the name of your database and log files (“filename” column) 

With that Query open, create a new query for step 2.

2. Truncate the database and shrink the database
Paste the following SQL into your new query and fill in the parameters which are marked by <…>
You can get the database filename and the log filename from the first query.  Copy Paste and make sure you delete the file path and the file extension (mdf, ldf) from the filename.  If you leave that in, then the query will bomb.

USE <yourdatabasename>
GO
BACKUP LOG <yourdatabasename> WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (<yourdatabaselogfilename>, 1)
GO
DBCC SHRINKFILE (<yourdatabasedatafilename>, 1)
GO
exec sp_helpfile

Run It.  When complete, the script will tell you what the new files sizes are.

Note: You may have to substitute the file_id for the filename for the db and log file on the SHRINKFILE command lines.