12 Tasks for Monitoring SQL Server

12 Tasks for Monitoring SQL Server

SQL monitoring best practices

Some alerts work as the best native SQL tools you’ll ever set up.

A SQL DBA is responsible for many SQL Servers — especially these days. The days of having just one critical server are long gone.

That’s why it’s important to deploy some custom SQL monitoring jobs that cover the basics.

Because there’s always that one server you didn’t buy a monitoring license for. Or some other odd scenario.

We tend to deploy a handful of custom SQL monitoring jobs on nearly every SQL Server we maintain.

The custom SQL Server monitoring jobs we deploy:

1. SQL Agent restart notification: Send an email whenever SQL Agent restarts.

2. SQL Engine restart notification: Send an email whenever the SQL Engine service restarts. Very handy for catching random restarts, or catching whoever forgot to mention it to the team about a server.

3. HDD space monitoring: This is configurable disk-space monitoring that sends an email once a threshold is reached.

4. DDL event monitoring: Whenever any SQL object (tables, stored procedures, views) is altered, created, or dropped, keep a log of all those changes. Extremely handy!!!

5. Snapshot of running T-SQL commands: Capture what’s happening on the server every few minutes, log it to a table, purge irrelevant and old data, so you can go back and answer a question about why the server was slow last Tuesday at 3pm. Very handy for low-end performance monitoring.

6. SQL Agent job change tracking: Capture every job and job-step change and log it to a history table.

7. SQL configuration change tracking and notification tool: Capture everything you can, such as SQL instance configuration changes, and send an email once a day so you know whether you’re dealing with server settings someone forgot to mention to you.

8. Single-use plan cache cleanup: If single-use plans are a problem, this job runs and clears the useless plans out of the cache, freeing up more RAM for the rest of the server. If your server doesn’t have that problem, this won’t cause any harm.

9. DB and table growth monitoring: Periodically log how large the databases are and how big the tables are getting, so after a while you can easily predict what the growth patterns look like.

10. Index usage and missing-index monitoring: Capture this data and store it in tables. Daily. That way, if there’s ever any doubt, we can quickly find out whether an index was created to handle month-end reporting, or something similar.

11. Failed login attempt reports: Get a daily email report with the number of failed attempts and the IPs they came from. Log this to a table.

12. Capture alerts from the SQL ErrorLog: This is your early warning system. Think of it as a red light that starts blinking right before an outage. Extremely useful for avoiding downtime.

That’s it. A handful of automated jobs that pretty much every SQL Server should probably have.

This way, you can avoid manual SQL Server monitoring. Or skip monitoring altogether — though we wouldn’t recommend that.