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
- Had to rebuild MSDB using this method
- We lost the Backup plan, all SQL Jobs
- had to re-create Backup plan using Maintenance Wizard
- created the jobs elsewhere and paste and re-create
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
