14.8.13

SQL: How to shrink LDF (transactional LOG file)

A lot of us who uses complex systems of DevOps, ALM, deployment etc. usually need to do a basic maintenance for the system's database.

One of the common issues that we are dealing when using an old Microsoft SQL server is the size of the LDF file (transactional log).
There are a lot of post that explain what it means to shrink, what're the theories behind etc.
So instead of the yada yada, just copy and paste (don't forget to replace the placeholder DatabaseName with your own database name value):

USE DatabaseName
GO
-- change the database recovery model to SIMPLE.
ALTER DATABASE DatabaseName
SET RECOVERY SIMPLE;
GO
-- Empty/Shrink the LDF file to 1MB
DBCC SHRINKFILE (DatabaseName_Log, 1);
GO

*** The lines below are optional, since you've already empty the log file, it will be a little bit risky to change it back to full recovery model.
If you would like to restore to the old recovery model, you should backup your DB before the shrink for a case of corruption

-- Restore the database recovery model.
ALTER DATABASE DatabaseName
SET RECOVERY FULL;
GO


The amazing part that every LDF file, no matter what was its size (50GB, 200GB), it will be resized to 1MB

Enjoy (carefully :) )


2 comments:

  1. The delegon was very excellent,i like that post more information to get after reading that article,then the easily to understand all that article and more information to find out.


    informatica training in chennai

    ReplyDelete
  2. Again, I strongly believe, in order to be efficient, one must be able to run a program directly from the command line without needing to go elsewhere to edit config files.
    seo log analyzer

    ReplyDelete