Delete a Transaction Log from MS SQL Server

Just in case you run into the same problem I had – tiny 100 MB database with a 1750 MB transaction log. From dBforums:

If you don’t care about saving the trans log and just want to start over, a really easy way to do this is:

1. From the Enterprise Manager, right click on your dB and select All Tasks->Detach Database

2. Go to the storage location for the database on your machine (probably located under the directory …\Program Files\Microsoft SQL Server\MSSQL\Data. From here, find the log file for that particular database which will probably end with extension .ldf.

3. Delete that file (make sure this is the correct log file!)

4. Go back to Enterprise Manager and right-click the ‘Databases’ folder under your SQL Server instance and select All Tasks->Attach Database

5. In the next dialog that pops up, find the file for your database (probably ending in extension .mdf) and located in the same folder as the log file. Enter any other info you might need in this dialog (you might not need to enter anything) and click OK. You might get some kind of error about the log file missing and it will probably ask if it can create a new one. Go ahead and let it.

This looks like a lot of info, but once you do it once it’s really easy. You might want to test this on some other database first to get the feel for it. Also consider backing up your dB before this also.

About Kevin

Kevin Jarnot is a technologist who lives just South of Boston, MA. He is currently employed as Chief Technology Officer at DebtX, a financial services technology company based in Boston.
This entry was posted in Tech. Bookmark the permalink.

Leave a Reply