SQL 2005: Truncating Log Files and Recovering Space
April 4, 2008 · Print This Article
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).
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.
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.
These guys get it done. My data was saved and I was back up and running FAST!!!
Thank you so much for this. I reclaimed half of the total disk space allocated for SQL data! Fantastic! Have been using this script for all other database servers we have!
This worked like a charm I shrunk 175 gig log file to 1024 K
Thanks
Count another success for this process: I had 9mb of free space on my C:\ drive, where we had accidentally installed the SQL databases. One database one 24Gb, and the log file was 2.5 Gb. I tried a number of things to free up space, including a ‘delete old files’ option from the application using the db. I ran this process and then the logfile went down to 11mb. The DB file size didn’t really change though, but thats fine – freeing up the logfile allowed the application cleanup process to run (once again increasing the logfile size in the process! haha, guess I’ll need to run this again afterwards
THANK YOU!!!!
Had same problem with log file size and after lots of searching and trying this and that without success until I came across this page……..and BINGO! problem solved 85gig log down to 8 meg. Thanks again guys.