Had to seep through loads of info trying to restore the sequence of events that led to a crash yesterday, and got really interested in finding a solution to offload events from SQL Server to a custom event log. Google yields only a single promising result, with the link to a guide on creating custom event logs..
While i wouldn't go that far as to call SQL events pointless (though agreed, 17101 and 17103 spelling out "(c) 20?? Microsoft Corporation" and "All rights reserved." upon each restart are a definite waste!), <strong>IMHO it would certainly be useful and beneficial to re-route SQL events to its own log!</strong> Hell, even IE has got one, built-in! Why can't SQL Server take that as a better practice to implement? Especially on Vista/Win7, which provide tons of individual logs for loads of other apps - quite useless IMHO (never had any need to dig in there), but forcing UI to slow to a crawl each time you open it:
<img src="https://i.stack.imgur.com/TB3Ib.png" alt="Snapshot of Event Log view in MMC on Win7">
I successfully follow the guidelines of creating a 'SQLServer' custom log, add the source definitions to it. Unfortunately, any attempts to re-route SQL events to it seem to bump into an issue of MSSQLSERVER (the log source matching the default name for the SQL service) being some kind of a built-in source:
EventCreate /l "SQLServer" /t Information /so MSSQLSERVER /id 1 /d "Log created" ERROR: Source parameter is used to identify custom application/scripts only (not built-in sources).
When i mark MSSQLSERVER under my log as CustomSource (DWORD=1), the error above disappears:
EventCreate /l "SQLServer" /t Information /so MSSQLSERVER /id 2 /d "New entry" SUCCESS: A 'information' type event is created in the 'MSSQLSERVER' log/source.
and indeed an event with ID=2, desc='New entry' is added to the custom event log! <strong>However, in this configuration the real MSSQLSERVER service does not write events to either this new log or to the standard 'Application' log :(.</strong> Functionality is restored upon reverting log definitions in registry (no reboots needed!), so it is a reversible scenario.
Also, from the above it looks as any source can only be associated with a single log.. Logical enough. But what defines these built-in sources then, if i remove the explicit registry entries? Maybe I should've restarted the machine after making these changes (though that was not necessary to revert back)?
Has anybody explored this further and maybe had any success?
<strong>EDIT</strong>: So far, like i said, there seems to be the only way to deal with this by filtering out MSSQLSERVER (or other SQL service name) events from view, like so:
<img src="https://i.stack.imgur.com/KrDYY.png" alt="Filter out MSSQLSERVER events">
But the XML tab exposes what goes under the hood, and it's quite ugly (as in extremely inefficient):
<img src="https://i.stack.imgur.com/yoQfx.png" alt="Query that's being processed for such filter">
I want a better way to manage this event data, and am sure i'm not the only one. So if any folks at Microsoft are reading this - take note!
If you wish to create an event viewer filter to exclude a particular source, here is the XML (Suppressing SQL Express events from the 'Application' log).
<QueryList> <Query Id="0" Path="Application"> <Select Path="Application">* </Select> <Suppress Path="Application">*[System[Provider[@Name='MSSQL$SQLEXPRESS']]]</Suppress> </Query> </QueryList>