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 we’re not going to attempt to make “one size fits all” statement on database maintenance plans, we though 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).

Important:
Record the name of the file from the “filename” colunm, excluding the path and file extension (e.g. if 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, one for the data file and the other one for the log file, be very careful when typing in the file names:

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 one.

If you get an error like:
"Cannot shrink log file because all logical log files are in use"
I solved doing the following:

1. open enterprise manager.
2. right click on the database you wanna shrink.
3. click on properties.
4. from the data properties go to options.
5. in the middle you will see recovery model. Make it "simple" then click on "ok" and try again.

Once done, it's recommended to take the parameter back to Recovery model = Full.
You can also setup the maximum log file size at this point, so you won't be facing the same problem again !