SOP: Truncating SQL Log Files and Recovering Space

** Make sure before you attempt this procedure that all processes using the database are stopped. (ie: runtimer, GDS scheduler, etc) Also make sure the files are backed up.

A common issue the SQL Server databases is disk space and the size of the physical log file and database. This not always a fix log file growth, but will help you cut the size of your file when 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 returns 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 filename contains “C:\sqldatabases\yourdatabase_data.mdf” ). You want to save the string “yourdatabase_data” for the file you will be working with.

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 <…>.  Use the two filename value you saved 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.

An example used on the RadioBeacon database:

USE rbeacon

GO

BACKUP LOG rbeacon WITH TRUNCATE_ONLY

GO

DBCC SHRINKFILE (rbeacon_log, 1)

GO

DBCC SHRINKFILE (rbeacon, 1)

GO

EXEC sp_helpfile