SQL Server Tips

12 SQL Server Monitoring Jobs

Mark Varnas
2 comments

The best SQL monitoring practices

I don’t care how much third party software monitoring you have going on — even if Santa brought you all the third party software licenses you asked for — some alerts work best as SQL native tools.

SQL DBA are responsible to many SQL Servers. Especially today. Days, when you had one important server are over, buddy.

So it is important to deploy some custom SQL monitoring to cover the basics.

Because there is always some server you didn’t purchase a monitoring license for. Or some other weird scenario.

I tend to deploy a few custom SQL monitoring jobs on pretty much any SQL Server we maintain.

SQL Server custom monitoring jobs we deploy

  1. SQL Server Agent restart notification
    When SQL Server Agent restarts, send an email out.
  2. SQL Engine restart notification
    When SQL Engine service restarts, send an email out. Very useful to capture random reboots or someone messing with the server, which they forgot to tell me about.
  3. HDD space monitoring
    This is configurable HDD space monitoring, when threshold is hit, it emails out.
  4. DDL events monitoring
    When any SQL objects (tables, stored procedures, views) are modified, created or deleted, keep a log all of those changes. Very damn useful!!
  5. Snapshotting of currently running T-SQL commands
    Capture what is happening on the server every few minutes, capture that into a table, delete what isn’t relevant and any old data, so I can go back and answer a question, about why the server was slow at 3PM on Tue last week. Very useful for low end performance monitoring.
  6. SQL Agent job change monitoring
    Capture and log all job and job step changes into a log table.
  7. SQL config change track and notifier
    Capture everything I can like SQL instance configuration changes and send out an email once per day, so I will know if someone is messing with server settings and forgets to tell me about it. Like reducing RAM on AWS EC2 instance, or something similar
  8. Cache single plan cleanup
    If single use plans are a problem, this will run and delete useless plans from cache making more RAM available to the rest of the server. If a problem doesn’t exist on your server, then this won’t do any harm.
  9. DB and table growth monitoring
    Periodically log database sizes, how big tables are, so after a while I can easily estimate what the growth patterns are looking like.
  10. Index usage and missing index monitoring
    Capture this data and store in the tables. Daily. So if there is any doubt that an index was created to address month end reporting, or if we need to quickly find that out.
  11. Failed login attempt reports
    Email me a daily report with the number of failed attempts and IPs where they came from. Log that to the table.
  12. Capture alerts from SQL error log
    This is an early warning system. Think of it like a red light that starts blinking when we are about to have an outage. Very useful for preventing downtime.
  13. I really don’t like stopping at #13, but what da hell. This is all I can think of right now.

When any SQL objects are modified, created or deleted DDL events monitoring keeps a log all of those changes.
Very damn useful!!

So there you have it. A few of the automated jobs that each SQL Server probably needs to have.

And that is how you avoid manual SQL Server monitoring. Or not monitoring at all.

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

2 thoughts on “12 SQL Server Monitoring Jobs”

Leave a Comment

Managed SQL Server services, consulting, and emergency support from expert DBAs to improve performance, predictability, and cost.

Get started with Red9 today.

Contact us

Discover More

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials