SQL 2005: Truncating Log Files and Recovering Space

A common issue for users of SQL Server databases is disk space and the size of the physical log file and database. While I’m not going to attempt to make a “one size fits all” statement on database maintenance plans, I thought it would be helpful to provide a few suggestions that will help you trim the size of your files when you are in a pinch.

Steps to truncating log files and shrinking your database:

1.  Get the physical names of your database file (MDF) and log file (LDF):

Run the following system stored procedure:

use <yourdatabasename>
exec sp_helpfile

This command will return a variety of information, including the physical size (the “size” column) and the path and name of your database and log files (in the “filename” column). 

Record the name of the file from the “filename” column, excluding the path and file extension (e.g. if the filename contains “C:\sqldatabases\yourdatabase_data.mdf” you want to save the string “yourdatabase_data”)

2. Truncate the database and shrink the database

The following set of SQL will shrink your database and “truncate” the log file. File in the parmaters surrounded by <…>.  Note that you’ll need the two filename values from step 1.

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

When complete, this script will output the same information as in step 1. 

Compare the new size with the old.