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.
