Thursday, November 06, 2014

How to delete the log file from sql server databases

Disclaimer: This process should not be done with databases that you really care about. This procedure is only for dev/testing environments, not for production databases. I'm using Sql Server 2008R2.

Often the log file, ".ldf", can be quite large. If it's a production database you need it, but for databases you are using for dev and testing, you can remove it carefully to save space.
Here's the steps:
  1. In Sql Enterprise Manager "detach" the database - this won't hurt it, don't worry.
    This will bring up another window and just select "OK".
  2. Then in File Explorer append ".bak" the ldf filename - just in case we need it. Location may vary, on my box it's in "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA"
  3. Attach the database
    This will bring up a window. In the upper panel select "Add" and give the location of your .mdf file.
    Now comes the tricky part, in the lower panel select the log file and then select "Remove". Then select "attach", and voila, your old boat-anchor of a log file is now gone from your database!
  4. If all goes well, delete the renamed ".ldf" file.
Did I mention that you should not do this with databases you care about?

No comments: