Friday, April 25, 2014

Shrink transaction log

When transaction log has grown to size that is too big to take backup, or space just isn’t available AND the database just isn’t that important that it should drag all others to lag-hell this might be done to solve the issue.

Note that there is a point to transaction files. If the database is so important that you can’t loose a minuts worth of transactions its really a necessary feature. But if that need isn’t there, the recovery mode could easily be changed to Simple to avoid the risk of losing space on an old server and therefore halting critical applications.

But here it is anyways.

  1. Change recovery mode to Simple on the overgrown database.
  2. Run query: dbccshrinkfile (mydatabase_Log,0)
  3. Change recovery mode to Full again to restore the logging.

If the transaction logs grows, its usually because their isn’t a real backupsolution running for the database. Use maintenance jobs or stored procedures if a real one isn’t available.

No comments: