The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Friday, March 29, 2024 Login
Public

SQL Server Log Management 2/26/2008 1:07:45 PM

SQL Server does a great job of logging, but anyone who administers a sql server should adjust some of the "out of the box" defaults for an instance, and create some custom jobs in sql agent to control output of jobs.

By default sql server logs information to ErrorLog.  Depending on your version of SQL Server (7.0, 2000 or 2005) and a default or named instance it is located along a path similiar to the following:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL${Instance}\LOG

The ErrorLog is a great source of monitoring and should be scraped and reviewed regularly, but by default there are only 6 log files kept on disk, and a new log file is started evertime an instance is started or "cycled".  This is not adequate, an administrator applying a patch to a server seems to require 3 reboots and you don't want to find out that information needed in a log file was lost due to not having enough of them.  We run this to 30 log files kept on disk and cycle the error log daily at 12am via a sql agent job.  This is very easy and can be adjusted via gui in SQL Enterprise Manager or in SQL Server Managemetn Studio by right clicking on the SQL Server Logs and selecting "Configure", which will bring up the dialog below, adjust to any number, but definetly 6 is not enough !

It can also be adjusted via TSQL, but requires xp_instance_regwrite and is highly dependent that you know the structure of the registry for your particular instance and version of sql server, this is one case where using the GUI is the best option, but it is possible to do via tsql:

EXECUTE master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE'
,N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer' --****!!!! VERIFY
,N'NumErrorlogs'
,REG_DWORD
,30 -- number of errorlogs you want to keep
GO

After configuring the instance to retain 30 errorLogs, now you need to implement a sql agent job to cycle the error logs daily at 12am.  This is very easily implemented with a job scheduled to run:
Exec sp_cycle_ErrorLog.

Now onto the good one, SQL Server Agent and the output from jobs.

After managing SQL Server Agent jobs for many years it is apparent that the default options for logging of jobs, Overwrite and Append, is not adequate.  Most of the time we default the logs to append, but overtime and depending on the amount of output these log files, they can become very large, difficult to open, difficult to read and waste valuable time when you have to trouble shoot a job.  This applies to sql server 7.0, 2000 and 2005 and is highly dependent on having all your jobs and logging go to the same directory (if they are currently "scattered" you can correct them manually or through tsql in the sysjobsteps table, with a mass update).

We have developed a jobs that we host on every sql agent instance, Log Archive.  It is a two step job consisting of TSQL and Active X Scripting.  One step moves all the log files to a sub-directory for that day.  The 2nd step of the job removes the sub-directories older than x days (we default to 30).  This makes it very easy to find a log file for a particular day, based on the naming convention of the sub-directories.  This job runs daily at 11:58pm.  Example below:

The code for the two steps is attached, they will require editing for your specific directory structure and number of sub-directories to retain.

Archive_Daily_Logs.txt (.7 KB) 

VBScript_Delete_Old_Dirs.txt (.52 KB) 

This creates a great directory tree, easy to maintain, easy to troubleshoot:


Blog Home