Tuesday, August 9, 2022

MSDB transaction log was full due to 'CHECKPOINT' despite disk has space. Had to rebuild from scratch

Today I came across something new in my 20 years of DBA life - MSDB transaction log was full due to 'CHECKPOINT'

Due to the secure environment I'm not able to copy-and-paste out anything but you'll get the idea

Issue

  • We had a customer disk running out of space, at 0 bytes, it caused everything to stop
  • After some cleanup and emergency actions
    • Removed pagefile
    • Emptied Recycle Bin and disabled saving space for the drives
  • We managed to free up 20~80GB+ space, yet the SQL Agent still won't start up, and the error log/event view shows - MSDB transaction log was full due to 'CHECKPOINT'

Things Tried (and did NOT work)

  • Obviously restart SQL Server and Agent
  • Put MSDB into Emergency mode - nope, not allowed
  • Rebuild Log
  • Backup Log
  • Adding new Log file to MSDB
  • Change MSDB recovery model (from SIMPLE to FULL or anything)
  • Reboot the physical server
  • ^ all above made sense because the LOG is full and cannot do any action

Resolution

Things to Do AFTER or Prepare beforehand

  • Script out your SQL Jobs if possible
  • Backup if possible, for restore AFTER the rebuild
  • AFTER - had to re-grant permissions to MSDB on SQL Logins

No comments:

Post a Comment