How to Do Some Very Basic SQL Server Monitoring

One thing that I think is very important for a database professional to do as part of their regular work is to have some idea of how their most important SQL Server instances are running and how they have been performing over time. This helps you get a feel for what a normal workload is for your instance and what your workload extremes look like. It also helps you answer the inevitable questions about “What happened to the database server last Thursday at 2PM”?

There are many fine 3rd party products available for this purpose such as SQL Sentry for SQL Server from SentryOne and SQL Monitor from Redgate. Regardless of whether you use any of those products, I think you should also consider using something that is very simple, lightweight and free, such as my ServerMonitor database and related SQL Server Agent job, which you can download from here. This version only works on SQL Server 2008 or newer.

The zip file includes two separate T-SQL scripts. The first script creates a database called ServerMonitor in the default database location on your instance, sets the recovery model to Simple, and then creates one table and two stored procedures in that database. It also uses PAGE data compression on the indexes for that table if you have SQL Server 2008 Enterprise Edition or newer. Finally, it creates a SQL Server Agent job called “Record Instance Level Metrics” that runs once every minute, to collect a few instance-level metrics and store them in the ServerMonitor database. You can easily modify the schedule for that job if you wish.

These basic metrics include: Average Task Count, Average Runnable Task Count, Average Pending IO Count, SQL Server process CPU utilization, and Average Page Life Expectancy across all NUMA nodes. This is just some very basic, easy to collect information that can be quite useful for getting some baseline and trending information about your instance over time.

The second script just has a few example queries for pulling some useful information out of the ServerMonitor database. The ServerMonitor database is just a simple example that anyone can understand and easily extend if they want to. Please let me know what you think. Thank you!

SQL Server Agent Does Not Start After Uninstalling/Reinstalling SQL Server 2012

A little while back, I ran into an interesting little problem after uninstalling and then reinstalling SQL Server 2012 on the same machine. In order to record a nice, pretty demonstration of installing SQL Server 2012 on a test machine for one of my Pluralsight courses, I decided to completely uninstall SQL Server 2012, and then reinstall it as I recorded it for the demonstration.

After I did that fresh installation, the SQL Agent Service would not start. All the normal troubleshooting measures gave me no immediate resolution.  This was what the Windows Application Event Log had to say about it as shown in Figure 1.

clip_image002

Figure 1: Windows Application Event Log Details

This looked like a Windows Registry permission issue. Finally, I figured out that I needed to delete the old SQL Agent error log file (SQLAGENT.OUT), as shown in Figure 2.

clip_image002[5]

Figure 2: Windows Application Event Log Details

After I did that, the SQL Agent Service started working just fine. I realize that most people won’t be uninstalling and then reinstalling SQL Server 2012 on the same machine that often, but just in case you do and you run into this issue, this is one thing to try out.