June 14, 2012 by Mike Hillwig
In the next couple of months, I’m presenting a new session at least twice. “Seven SQL Agent Jobs You Should be Running” has been accepted for both SQL Saturday 126 in Indianapolis and SQL Saturday 158 in New York City. This is a new session for me, but I have a pretty clear idea of what I plan on presenting. I believe there are several jobs you should run on every instance just to make sure that your environment is behaving as expected. Here is the abstract I’ve submitted:
See those things around your ankles? You hope they’re your socks because no DBA wants to be caught with their pants down. Whether you’re an accidental DBA, a DBA who has just inherited a bunch of servers, or someone who works in a shop that won’t buy monitoring tools, this session will help you get basic monitoring in place to make sure you know what’s going on in your environment. You’ll learn how to implement seven simple scripts that perk their ears up and start barking like a dog after a stranger walks in the house. They are the canary in your coal mine. You’ll know when backups fail, when they run longer than usual, when data files are getting full, when transaction logs have excessive VLFs, and more.
My premise is that these jobs should run all the time and never actually do anything. But when they detect something that isn’t going as expected, they’ll poke up their head and let you know what’s happening. Here are the seven alerts for this session:
- Database hasn’t done a full backup in the last X hours
- Database in full recovery mode hasn’t done a transaction log backup in the last X hours
- Database transaction log has excessive virtual log files
- Database has no data file that can auto-grow, and none of these files has much free space left
- Autoshrink enabled
- Failed jobs in the last 24 hours
- Jobs that are running more than x% over average
My target audience here really is junior DBAs and accidental DBAs. More experienced DBAs could write these themselves. Junior DBAs will walk away with a framework of how to implement these monitors in their own environment. And accidental DBAs will walk away with ready-to-run scripts that they can implement in their own environments.
As I’m developing this presentation, I thought I would throw this out to the other SQL bloggers for comment. What are the other “oh my god we’re going to die” types of alerts I should be monitoring?