How It Works: What Are The Event Source Names Used By SQL Server

  • Article

 

Sent: Tuesday, March 17, 2009 4:24 AM
Subject: Event Source changed from MSSQLServer to MSSQLSERVER to all upper case  

Our different SQL server versions write to the Eventlog in a mixture of upper/lower case.  One version writes as Event Source “MSSQLServer” the other one writes all upper case “MSSQLSERVER”, and so on.  

I have a problem monitoring because my rules for Eventlog Monitoring and Event Collections are case sensitive and is not getting all the entries I would like to see.  Is possible to get an example of what SQL Server writes to the event log?

=================================================================================

Sent: Tuesday, March 17, 2009 8:43 AM
Subject: RE: Event Source changed from MSSQLServer to MSSQLSERVER to all upper case  

Sure, SQL 2005 and newer versions made a large push to revamp how we reported events. Prior to SQL 2005 most events were reported under the same event id, in fact, even SQL Setup borrowed an event from the SQL Server and used it for logging activity in some locations. As you can imagine this meant lots of rules needed pattern matching of the text and parameters and it was difficult to distinguish.  

I did a bit of research and the instance names throw a curve into the question. With the instance names there has to be wild card logic involved in matching the Event Source Name no matter what the case results are. The RegisterEventSource API is used to register the application name. Here are just a few examples to help illustrate my answer.  

SQL 2000 SP4 Based Research

                WriteEvent from XPROCS = MSSQLSERVER but I did find a location where it appears it could be MSSqlServer as well
                SQLProfiler = SQLServerProfiler
                SQLAgent.exe = Default instance is "SQLSERVERAGENT" and named instance starts with L"SQLAgent$"  

SQL 2005 and SQL 2008 – Logic was consolidated and these are the basic formats

L"MSSQLSERVER"
L"SQLSERVERAGENT"
L"MSFTESQL"
L"MSSQLServerOLAPService"
L"ReportServer"
L"MsDtsServer"
L"SQLBrowser"
L"MSSQLServerADHelper"
L"ReportServer"  

L"MSSQL$"
L"SQLAgent$"
L"MSFTESQL$"
L"MSOLAP$"
L"ReportServer$"
L"MSDTS$"
L"MSBrowser$"
L"MSADHelper$"
L"ReportServer$"

 

So the answer is that you should look at pattern matching to encompass the proper case and instance naming conventions.  

Bob Dorr - Principal SQL Server Escalation Engineer