Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to centralize your SQL Server Event Logs

By Geoff Albin, (first published: 2010/11/08)

How to centralize your SQL Server Event Logs

SQL Server running on Windows provides a wonderful feature many DBA's overlook. It's called the Event Log. SQL Server will automatically record all sorts of info about what SQL is doing into the Event Log. I am sure we have all read the Event Log looking for things like why a server rebooted unexpectedly or to try to figure out why something is not working. But there is a lot of info in there that we may overlook. Especially since most of it does not really apply to SQL.

There are many modern day tools that will make this function very easy, but they typically cost a lot of money. Not that I am opposed to spending the boss's money, but sometimes the boss will not let me. So I came up with a process that will get some important info about SQL into my hands without having to spend time on every single instance setting up individual alerts and SQL Agent jobs to find this information.

Step 1 - create the database

You will need a database we can use to create a table to place the logs. I like to call it DBA.

-----------------------------------
USE [MASTER]
GO
-----------------------------------
CREATE DATABASE [DBA]
GO
-----------------------------------

Step 2 - Create the Table

This will be the table that holds the event logs. I like to call it EventLogStaging. Go into SSMS and open a New Query . Copy and paste the code below into the SSMS window.

USE [DBA]
GO
-----------------------------------
SET ANSI_NULLS ON
GO
-----------------------------------
SET QUOTED_IDENTIFIER ON
GO
-----------------------------------
CREATE TABLE [dbo].[EventLogStaging](
 [RecordNumber] [int] NOT NULL,
 [Category] [int] NOT NULL,
 [ComputerName] [nvarchar](250) NOT NULL,
 [EventCode] [int] NOT NULL,
 [EventType] [int] NOT NULL,
 [Message] [nvarchar](4000) NULL,
 [SourceName] [nvarchar](250) NOT NULL,
 [TimeGenerated] [datetime] NULL,
 [TimeWritten] [datetime] NOT NULL
) ON [PRIMARY]
-----------------------------------
GO

Now you have a table

Step 3 - Create a VB script as EventLog2DB.vbs

Copy and paste the code below into a new Notepad file. Save it as EventLog2DB.vbs. Remember that in Windows, you will have to choose "Save as type: All Files" or it will become a text file. Copy it over to the SQL Server you are going to monitor. (Remember the location where you copied it to.) I always have an E: drive on my servers, so I create a directory called E:\Monitor and I run EventLog2DB.vbs from there. If you do not have an E: drive, use C:\Monitor.

strComputer = "."
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open "Provider=SQLOLEDB.1;Data Source=.;Initial Catalog=DBA;Integrated Security=SSPI"
objRS.CursorLocation = 3
objRS.Open "SELECT * FROM EventLogStaging" , objConn, 3, 3
' Get to the Event Log
Set objWMIService = GetObject("winmgmts:" _
 & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
' get the events we want 
query = "Select * from __InstanceCreationEvent" _
 & " " & "where TargetInstance isa 'Win32_NTLogEvent'" _
 & " " & "and TargetInstance.Logfile = 'Application'" _
 & " " & "and (TargetInstance.EventType = 1 or TargetInstance.EventType = 2)" _
 & " " & "and (TargetInstance.SourceName like 'MSSQL%')"
' get ready to insert into our DBA table 
Set colMonitoredEvents = objWMIService.ExecNotificationQuery(query)
Do
 Set objLatestEvent = colMonitoredEvents.NextEvent 
 objRS.AddNew 
 objRS("RecordNumber") = objLatestEvent.TargetInstance.RecordNumber
 objRS("Category") = objLatestEvent.TargetInstance.Category
 objRS("ComputerName") = objLatestEvent.TargetInstance.ComputerName
 objRS("EventCode") = objLatestEvent.TargetInstance.EventCode
 objRS("EventType") = objLatestEvent.TargetInstance.EventType
 objRS("Message") = objLatestEvent.TargetInstance.Message
 objRS("SourceName") = objLatestEvent.TargetInstance.SourceName
 objRS("TimeGenerated") = WMIDateStringToDate(objLatestEvent.TargetInstance.TimeGenerated)
 objRS("TimeWritten") = WMIDateStringToDate(objLatestEvent.TargetInstance.TimeWritten)
 objRS.Update
Loop
' if we ever finish, we close cleanly.
objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
'******************************************************************************
'* This conversion is necessary because WMI uses a different date/time format *
'******************************************************************************
Function WMIDateStringToDate(dtmInstallDate)
 WMIDateStringToDate = CDate(Mid(dtmInstallDate, 5, 2) & "/" & _
 Mid(dtmInstallDate, 7, 2) & "/" & Left(dtmInstallDate, 4) _
 & " " & Mid (dtmInstallDate, 9, 2) & ":" & _
 Mid(dtmInstallDate, 11, 2) & ":" & Mid(dtmInstallDate, _
 13, 2))
End Function

Hey, what's this VB script doing to my system? Is it safe?

Let's break it down.

strComputer = "."
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open "Provider=SQLOLEDB.1;Data Source=.;Initial Catalog=DBA;Integrated Security=SSPI"

Here we are simply making a connection to the local instance on the server where this script is run from. You can see that we are using integrated security and the database we created called DBA. The strComputer = "." is a variable that we pass later on in the script.

objRS.CursorLocation = 3
objRS.Open "SELECT * FROM EventLogStaging" , objConn, 3, 3
' Get to the Event Log
Set objWMIService = GetObject("winmgmts:" _
 & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

The connection we made on the previous step is connecting the table we created earlier called EventLogStaging. It is also going to connect to the local Event Log service.

' get the events we want 
query = "Select * from __InstanceCreationEvent" _
 & " " & "where TargetInstance isa 'Win32_NTLogEvent'" _
 & " " & "and TargetInstance.Logfile = 'Application'" _
 & " " & "and (TargetInstance.EventType = 1 or TargetInstance.EventType = 2)" _
 & " " & "and (TargetInstance.SourceName like 'MSSQL%')"

Here you see we connect to and read from the Event Log. We are only interested in the Application log. That is where SQL likes to put its info. We are only interested in event types of 1 and 2. Those are warnings and errors. The source name of MSSQL% will guarantee that we get the event logs of all the instances installed.

' get ready to insert into our DBA table 
Set colMonitoredEvents = objWMIService.ExecNotificationQuery(query)
Do
 Set objLatestEvent = colMonitoredEvents.NextEvent 
 objRS.AddNew 
 objRS("RecordNumber") = objLatestEvent.TargetInstance.RecordNumber
 objRS("Category") = objLatestEvent.TargetInstance.Category
 objRS("ComputerName") = objLatestEvent.TargetInstance.ComputerName
 objRS("EventCode") = objLatestEvent.TargetInstance.EventCode
 objRS("EventType") = objLatestEvent.TargetInstance.EventType
 objRS("Message") = objLatestEvent.TargetInstance.Message
 objRS("SourceName") = objLatestEvent.TargetInstance.SourceName
 objRS("TimeGenerated") = WMIDateStringToDate(objLatestEvent.TargetInstance.TimeGenerated)
 objRS("TimeWritten") = WMIDateStringToDate(objLatestEvent.TargetInstance.TimeWritten)
 objRS.Update
Loop

Here you see a Loop. All the loop is doing is reading the event log with the selected criteria that we described (Application, errors and warnings, etc...) and are being inserted into the EventLogStaging table.

This will loop forever and every time something comes into the Windows Event Log, it will automatically be inserted in our EventLogStaging table.

' if we ever finish, we close cleanly.
objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
'******************************************************************************
'* This conversion is necessary because WMI uses a different date/time format *
'******************************************************************************
Function WMIDateStringToDate(dtmInstallDate)
 WMIDateStringToDate = CDate(Mid(dtmInstallDate, 5, 2) & "/" & _
 Mid(dtmInstallDate, 7, 2) & "/" & Left(dtmInstallDate, 4) _
 & " " & Mid (dtmInstallDate, 9, 2) & ":" & _
 Mid(dtmInstallDate, 11, 2) & ":" & Mid(dtmInstallDate, _
 13, 2))
End Function

The rest of the code just ensures we close the database connection cleanly if we ever exit the process and does some date and time conversion.

I have run this script at several locations for the last 10 years. It was written originally for a Windows 2000 and SQL 2000 server. The amount of memory that is consumed by the csript.exe process is always less than 5,000K. Even with a server with only 2 GB of ram, it's not too much to worry about. Currently it is running on a Windows 2008 R2 x64 server with SQL 2008 R2 x64.

Step 4 - Create the SQL agent job to insert records in real time.

Pay attention to the line that says @command=N'cscript "E:\Monitor\EventLog2DB.vbs"',
That location E:\Monitor is where I placed the VB script we made. You can put yours wherever you want, but change that line to where you put it. Like C:\Monitor.

Also, just in case you do not run your SQL Server in mixed mode authentication, you will have to change the line that says @owner_login_name=N'sa', to a user that can run the job.

Open another New Query window and copy and paste the code below into your window.

USE [msdb]
GO
------------------------------------------------------
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
------------------------------------------------------
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
------------------------------------------------------
END
------------------------------------------------------
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Monitor Event Log', 
 @enabled=1, 
 @notify_level_eventlog=0, 
 @notify_level_email=0, 
 @notify_level_netsend=0, 
 @notify_level_page=0, 
 @delete_level=0, 
 @description=N'No description available.', 
 @category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
------------------------------------------------------
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'always running', 
 @step_id=1, 
 @cmdexec_success_code=0, 
 @on_success_action=1, 
 @on_success_step_id=0, 
 @on_fail_action=2, 
 @on_fail_step_id=0, 
 @retry_attempts=0, 
 @retry_interval=0, 
 @os_run_priority=0, @subsystem=N'CmdExec', 
@command=N'cscript "E:\Monitor\EventLog2DB.vbs"', 
 @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'always', 
 @enabled=1, 
 @freq_type=64, 
 @freq_interval=0, 
 @freq_subday_type=0, 
 @freq_subday_interval=0, 
 @freq_relative_interval=0, 
 @freq_recurrence_factor=0, 
 @active_start_date=20100831, 
 @active_end_date=99991231, 
 @active_start_time=0, 
 @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'check every 1 minute', 
 @enabled=1, 
 @freq_type=4, 
 @freq_interval=1, 
 @freq_subday_type=4, 
 @freq_subday_interval=1, 
 @freq_relative_interval=0, 
 @freq_recurrence_factor=0, 
 @active_start_date=20100901, 
 @active_end_date=99991231, 
 @active_start_time=0, 
 @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
---------------------------------------------
GO

What it creates is a new SQL Server Agent job called Monitor Event Log. It is set to start every time the SQL Agent starts and every 1 minute after that. Of course, every minute the SQL Agent Job sees the previous job still running, it will just skip it. Basically, we want our little VB script to always be running. And this job will do just that.

Step 5 - Test that the events are being inserted.

Open SSMS and run a query on the instance you are monitoring. Copy and paste the code into your window.

raiserror ('working great',16,1) with log

The 'raiserror' command will write an event to the Windows Application log.

Then check if the event was written. Copy and paste this into another New Query window.

SELECT * FROM [DBA].[dbo].[EventLogStaging]
order by TimeWritten desc

You should see the event 'working great' in the results window.

Well, so it's working. Great! Now I have another place to see event log info. So why do I really care about that? Let's not forget about another feature SQL has had for many years called "SQL Server event forwarding". Consider that you might have 50 instances of SQL under your watchful eye. With a quick adjustment to the SQL Server Agent properties, you can forward SQL events from the Event log to where this job and VB script script are running.

Now I have a table with ALL my events in one place. I have used this method on over 150 instances forwarding their events to a central monitor server.
In my next article, I will describe how I create triggers and Stored Procedures to notify me via email and pager when events occur that need my attention.

Total article views: 11726 | Views in the last 30 days: 33
 
Related Articles
FORUM

How notificate create database event

How notificate create database event

ARTICLE

Getting Started with SQL Server Event Notifications

Learn the basics of Event Notifications from MVP Jonathan Kehayia. This article will show you how to...

FORUM

Sql Server Event Id 9724

Sql Server Event Id 9724

FORUM

Redirecting standard SQL Event Log events

Creating a custom Windows Event Log, and have all standard SQL events log into that.

BLOG

The system_health Extended Event Session

When I first started poking around in SQL Server 2012, I noticed an extended event session called “s...

Tags
event logs    
monitoring    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones