Shrinking SQL Server Databases


I was having difficulty shrinking my VSIP SQL Server 2005 Databases to remove unnecessary transactions and empty space from the log file. It took me a while to solve so I thought I should post my results here.  I got the answer from  http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/  and here is the resulting script (which I have since put in a stored proc – but I will not include that detail here):


Note that you will need your database name **and** the SQL Server name of your log file (different to the NTFS name) for this look at  sys.database_files. In this script my database name is “MyDatabaseName” and the SQL Server name of my log file is “MyDatabaseName_log” :


          USE MyDatabaseName


          DBCC SHRINKFILE(MyDatabaseName_log, 1);


          BACKUP LOG MyDatabaseName WITH TRUNCATE_ONLY


          DBCC SHRINKFILE(MyDatabaseName_log, 1);


          DBCC SHRINKDATABASE(MyDatabaseName) — Optional – not sure how much this helps


With this done I got back 50% of the entire space on my disk – guess I needed it 🙂 !! 


Please read http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/ for more info – particularly if your log file contains data that is important to you.

Comments (2)

  1. So many good articles and resources I have found over the last month or so. Here is a good sampling of