Friday, September 10, 2010


The option to take a log backup with TRUNCATE_ONLY is removed in SQL 2008 and future versions. This is used most of the times while we shrink a log file of a database (which is not recommended). You can use the below approach to free space on log files (worked for me).

  1. take a log backup
  3. shrink the log file.
If it still not works out throwing the message the log file is in use, follow the below steps
  1. Make sure there are no open transactions on the
  2. Restrict the current log file from growing further.
  3. Run some create, insert and drop table statements to make use the rest of the space on the first log file.
  4. create a new log file with auto growth file setting or create with enough space to be used for next 30min.
  5. Then shrink the first log file.
  6. Take a log backup
  7. Remove growth restrictions on first log file and restrict the second log file from growing
  8. Issue the CHECKPOINT command.
  9. Delete the second log file when not in use.
This is one of many approaches you can use. Have fun administering databases. :)

No comments:

Post a Comment