Monday, October 1, 2007

How To Truncate Log File In SQL Server 2005

I as part of my development role, often perform database backup/restore operations and move the databases from one server to another (ie: development to test, to QA...). One issue I often encounter is the amount of data I have to transfer from one server to another. If the server is on a local network then it's not a big deal but if it goes out to a remote data center then I want to cut the file size down as much as I can.

I accomplished by eliminating the log file thus trim down a nice portion of unnecessary data. Here are the steps:

1. Take the database offline
2. Detach the database
3. Delete or rename the log file
4. Attach the database without the log file (highlight the log file and click Remove button)

SQL Server should create a new log file for your database. At this time, I backup the database and send it to the new server. I find this process has helped me save a quite a bit of time, especially when setting up the database on a remote server.

2 comments:

Anonymous said...

This feels like such a Hack.. Is this the only way.

Anonymous said...

You will find someday a corrupt database. The correct way is to issue a DBCC SHRINKFILE(Logical_Log_Name, New_Size_in_MB) after a BACKUP LOG.