i've some ldf file very big: 45 gbyte.. against a mdf data file of 'only' 1gbyte. My questions are:

  1. Is it safe to use DBCC ShrinkFile for the log file (ldf) ?

  2. How to programmaticaly limit the ldf file size ?

Thank you

  • 1
    Did you back up the ldf files first? ;)
    – TomTom
    Nov 14 '11 at 9:10
  • Backup + Truncate the Log files via SQL Management Studio (Backup DB, than Backup log with Truncate Options -> after this shrink files)
    – patricks
    Nov 14 '11 at 9:29

The short answer is "No, It's not recommended to shrink your log file(s)"

What is a solution is to run a full backup of your database, then detach your database, rename the log file to be something like database.ldf.old then reattach the database, without specifying the new location of the log file. This will create a new, empty log file.

The correct way to ensure that your log files do not grow excessively is to carry out regular backups of the transaction log. This is in addition to carrying out the regular full backups. This however, only applies if your database is using the Full recovery model. You can find which model it is using by right clicking on the database in Management Studio, then clicking on properties. Navigate to the options page and on that screen you will see the Collation Type, Recovery Model and Compatibility Level.

Unless you require the ability to restore to any point in time by replaying the transaction log to a specific transaction, using Simple recovery mode and ensuring that you backup your database regularly by doing a full backup will prevent your log file from growing excessively. It is also recommended that you have your log files on a separate volume (at a minimum) to stop a log file from possibly preventing login to your server (i.e. if it is on your boot partition, and fills the disk completely)


It Depends on How critical your SQL Databases are,,, in our case we are doing olny DB backup, and then we shrink the Log files regularly, keep in mind that log file will not be shrinked case the Database it self in not backedup,

so, first backup the database, the shrink the Database

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.