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

USE [MASTER]
GO

CREATE DATABASE [DBA]
GO

Step 2 - Create the Table

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

Step 3 - Create a VB script as EventLog2DB.vbs

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.UpdateLoop
' if we ever finish, we close cleanly.
objRS.Close
objConn.CloseSet 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?

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.


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.UpdateLoop

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.

USE [msdb]
GO
------------------------------------------------------
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
------------------------------------------------------
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategoriesWHERE 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 OUTPUTIF (@@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 QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @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 QuitWithRollbackEXEC @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 QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT 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.
SELECT * FROM [DBA].[dbo].[EventLogStaging]
order by TimeWritten desc

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.

(To enable that option go to Sql Server Agent -> Advanced -> Check 'Forward Events to different server', select a server name where the logs needs to be inserted.

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.

(Ref http://www.sqlservercentral.com/articles/Monitoring/71390/)