Solutions Through IT

April 5, 2009

Setup Performance Monitoring to SQL Database (Can’t start logging to SQL with perfmon)

Filed under: Administration — solutionsthroughit @ 23:18

This was one of those things that I thought was going to be easy.  Funnily enough, reading the documentation wasn’t enough! (Thanks Microsoft!).  Turns out there are a few hidden steps that, clearly, I didn’t glean from the documentation (or – and I’ll give them the benefit of the doubt – some things have changed since Server 2000).  If you’ve got it setup already, go straight to the fix at the bottom of this page.

First of all, everything was by the book. 

Setup a System DSN ODBC Data Source.  Make sure that you do NOT specify a database, all logging goes to the master database (note: I will attempt to do some testing here, to see if I am able to setup a PerfMon database purely for this, so that the master database doesn’t have to be used for logging).  I’m not going to detail these steps, unless I get enough requests – most admins should know how to do this.  Just remember you want to point this to a SQL database on the LAN.

Create a new Counter Log.

Add Counters and/or objects to the log.

You may have to set the RunAs account, if you are connecting to a non-local SQL server (unconfirmed at this stage).

Move to the Log Files tab, and change the log file to SQL Database and press the Configure button.  Choose your System DSN that you previously created and click OKUntick End file names with: – we’re not going to need this (initially).

Now move to the Schedule tab.  Depending on the requirements of the logging, you’ll want to setup when the monitoring starts and stops.  The defaults are to start “now” and to stop manually.

Ok.  That was by the book.  If you’re looking for assistance at this point – you are scratching your head just like I was!

The Fix

You have to change the account that the Performance Logs and Alerts service runs as.  The best advise that I’ve found so far is to change it to Local System.  This is done by going to the services mmc, double-clicking the Performance Logs and Alerts service and navigating to the Log On tab.  Change the log on as to Local System account.

 

IMPORTANT: Make sure you STOP and then START (don’t restart) the service.  If you don’t do this, it won’t (appear) to work.

 

Now, you can go back to perfmon, go to Counter Logs and start your log.  It will now fire off, and start logging data as per your specified counters and objects, at your specified intervals.

Oh – and you now owe me a coffee 🙂  Once you’ve organised me a coffee, click here to go to the next article, how to read your results.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: