Monday, November 17, 2014

How do you debug a Transactional Log Full Scenario ?

 I recently answered a few questions prior to an Interview. One of them stood out in my mind as worth saving:
How do you debug a Transactional Log Full Scenario ?

I would check the drive and the log_reuse_wait and log_reuse_wait_desc columns of the sys.database catalog view.
Then depending on what I saw:
Back up the log.
Free disk space so that the log can automatically grow.
Move the log file to a disk drive with sufficient space.
Increasing the size of a log file.
Add a log file on a different disk.

Complete or kill a long-running transaction.