Leadership

Configure IT Quick: Autoconfigure SQL Server logging to eliminate manual updates

Keep yourself from having to manually log events by configuring automatic logging

Many SQL database administrators (DBA) have been known to change configuration settings of their SQL Servers before they really have adequate information to do so. However, Microsoft SQL Server will often allow you to make ill-suited changes, so you should create a baseline for comparison by collecting information from SQL Server on how it is functioning before you make any modifications. As you may have witnessed, folks love to blame SQL Server for their problems when their two, two-and-a-half, or three-tier application isn’t working.

Say, did you make a change to SQL Server yesterday?
Having a solid SQL Server baseline provides the empirical evidence necessary for supporting changes. It also helps you justify the performance of SQL Server to others. Unfortunately, creating a baseline can be a cumbersome or overlooked process.

Windows NT’s Performance Monitor would be a useful tool for collecting data to create a baseline, but you can't log out while you’re running the tool, and it creates one gigantic log file. In this article, we will show you how you can auto-generate a baseline for your SQL Server, either 6.5 or 7.0, by collecting performance data using Performance Monitor, Windows NT Server 4.0, Microsoft SQL Server, and the Windows NT 4.0 Resource Kit’s MONITOR.EXE. We'll also explain how to avoid manually logging in to get results in Performance Monitor log format and how to automatically split Performance Monitor’s log files into manageable pieces. You can then use Performance Monitor to easily manipulate the data you have collected, as well as view or export that data to a format easily accepted by products like Microsoft Excel.

MONITOR.EXE
While Performance Monitor does not run as a service, you can use a tool that does—MONITOR.EXE. To use MONITOR.EXE correctly, you need to first run Performance Monitor and configure it to log the counters for the SQL Server you want to monitor. I recommend logging every available counter. You may not know which counter will be useful until it’s too late.

Simply set up the file where you want the Performance log to be saved and name the log, but don’t start logging—MONITOR.EXE will do that. Then, save the Performance Monitor settings as a PMW file and copy it to Windows NT Server’s SYSTEM32 directory. (I called my log ActivePerf.log and my PMW file was called ActivePerf.PMW.) MONITOR.EXE will run as a service on Windows NT to collect the performance information contained in the PMW file. Once you’ve completed these steps, run the following commands on the Windows NT Server where MONITOR.EXE will be installed:



Command Description
Monitor [\\computer] Setup Sets up the service
Monitor [\\computer] <PMW File Name> Associates Performance Monitor PMW file
Monitor [\\computer] Automatic Changes service to automatic
Monitor [\\computer] Start Starts service
For more detailed directions on MONITOR.EXE, review RKTOOLS.HLP in the Windows NT Resource Kit.
After you have setup MONITOR.EXE to run automatically as a service on Windows NT, you can verify that it is functioning properly by viewing the growth of the log file. This log file will grow unreasonably large, even over the course of a day’s time. In fact, I've monitored a few SQL Servers and collected over 10 MB of data in a single day.

You can get around this problem manually by stopping the monitor service, renaming the log file, and then restarting the monitor service. But rather than perform this action manually, you can create a stored procedure in SQL that executes the function for you automatically using the current day’s date as a portion of the new filename. You can use the following script:


if exists (select * from sysobjects where id =
object_id('dbo.sp_date_perfmon_data') and sysstat & 0xf = 4)
drop procedure dbo.sp_date_perfmon_data
GO
 
CREATE PROCEDURE sp_date_perfmon_data AS
declare @cmdstring varchar(255)
select @cmdstring= "ren \\sql1\f$\sqladmin\perflogs\ActivePerf.log PerfMon"
+ rtrim(convert(char(2),datepart(month,getdate())))+
rtrim(convert(char(2),datepart(day,getdate())))+".log"
print @cmdstring
EXECUTE master..xp_cmdshell "monitor \\SQL1 stop", NO_OUTPUT
EXECUTE master..xp_cmdshell @cmdstring, NO_OUTPUT
EXECUTE master..xp_cmdshell "monitor \\SQL1 start", NO_OUTPUT
GO

 

Here, the stored procedure, sp_date_perfmon_data, creates a string, @cmdstring, that creates a rename statement for Windows NT. The statement renames the log file, ActivePerf.log, to the current day’s date. Then, three EXECUTE statements run. The first stops the monitor service, the second issues the rename command string, and the third restarts the service. You can run sp_date_perfmon_data once a day to maintain an orderly and sized audit trail of Performance Monitor logs in the directory of your choosing.
If you want to monitor multiple SQL Servers' performance information, you will need to create a monitor service account for the server running MONITOR.EXE. Then, you will need to add this service account to the local Administrators group of the target servers whose information is being monitored. This is because by default, only the local Administrators group has the NT right to "Profile System Performance."

Mark Solomon has earned MCSE+I, MCT credentials from Microsoft.

If you'd like to share your opinion, please post a comment below or send the editor an e-mail.
0 comments

Editor's Picks