SQL Server’s utterly pointless event logging

If you’ve ever run Microsoft SQL Server you’ll probably be aware of its tendency to flood the Application event log with utterly trivial bilge. Yet for all the informational messages it logs, SQL Server never actually seems to log anything important when it happens. I was poring through said log last week (for reasons that will become clear later), and here are some of the most pointless messages that very frequently appear on my server:

  • Event 18264 tells me about each individual database that has been backed up, while 18265 immediately afterwards tells me that its log has been backed up. Thrilling!
  • Event 15457 will frequently tell me something cryptic like “Configuration option ‘xp_cmdshell’ changed from 1 to 1. Run the RECONFIGURE statement to install.” I have no idea why I should even remotely care about this.
  • When it starts up, it will log event 17069 to tell me the SQL Server version, then 17101 to tell me “(c) 2005 Microsoft Corporation”, then, utterly inexplicably, a COMPLETELY SEPARATE event 17103 just to continue with “All rights reserved.”

But is there ANY LOG AT ALL to tell me when a database has been dropped? You know, something that could massively impact the operation of the system and which is apparently not recorded in any persistent log anywhere else?

NO.

Microsoft themselves define an event as “any significant occurrence in the system or in a program that requires users to be notified, or an entry added to a log”[1]. I don’t know about you, but I wouldn’t classify an “All rights reserved” notice as ‘significant’, and the deletion of an entire database from a database server as ‘not significant’.

Maybe its just me.

Or maybe the person in charge of SQL Server event logging needs a SEVERE KICK UP THE BACKSIDE.

About The Angry Technician

The Angry Technician is an experienced IT professional in the UK education sector. Normally found in various states of annoyance on his blog. All views are those of his imaginary pet dog, Howard.

9 responses to “SQL Server’s utterly pointless event logging”

  1. Daniel Beardsmore says :

    You say that like there’s something else that uses the Event Log subsystem in a useful manner. It’s not just shining turds like Catalyst Control Center (why does that have its own event log to itself, and why has it filled it with complete and utter rubbish), but pretty much every Windows subsystem and technology.

    The Server 2008 Event Viewer clearly demonstrates that Microsoft don’t even use it — everything from the intractible chaos, to the unusable filter dialog box (more not-invented-here fake controls), and lack of right-click > Filter based on selected > events | sources.

    Useful logs in Windows like SMTP and printing are always off by default as well, so you only realise when you want to read something off that it wasn’t logged anyway. (SMTP logging, using physical log files, is another matter — I don’t know if IIS 7 finally has log rolling capability, which is a truly laughable omission in IIS 6 and earlier.)

    I’d settle for 5 p for every time I’ve looked in the Event Log for a clue to a problem and found no trace of any trouble (“I know when there is the trouble, and when there is not the trouble”), and for every entry where it can’t find the DLL that would contain the message if the installer laid off the weed. Yes it saves disk space to put messages in DLLs, but there are products where generation after generation still have common messages that are missing from the messages DLL. (DLL?? Why are messages in a DLL anyway?)

    Logging is a chief bane of my life — even under Linux, seldom are logs clear, accurate or useful.

    (I looked in the CCC log in the hope that it would tell me why my ASUS AH3450 sends a completely black picture down the VGA cable — I’ve got hsync and vynsc, just no picture.)

    • AngryTechnician says :

      Applications that use event logging well are indeed few and far between. The Media Center log is not too bad, I’ve found, and UAC-FileVirtualization for troubleshooting. The less said about the other services logs, the better. The PrintService Operational log being off by default certainly caught me out the first time I needed to check on the print server.

      I think the reason I decided to pick on SQL Server is that on all of my servers that have it installed, the Application Event log is more than 90% MSSQLSERVER events.

    • AlwaysLearning says :

      > Why are messages in a DLL anyway?

      It’s not about saving disk space, although that is a side-effect. It’s because not everybody speaks (or reads, in this case) the same language.

      The idea is that the Windows Event Log itself stores just the MessageID and with series of related parameter values. The DLL then holds the “language-specific %d messages with %s replaceable %d parameters” in string resources keyed by the MessageID. This allows you to take the Event Logs from an en-US machine and use Event Viewer to view the same event log information on an fr-FR machine (for example) but in your native language.

      • Rob says :

        Yes….yes I can see where being able to translate an event log into a different language would be more important than getting useful information in the language I already use on the server it originates on.

  2. The_Crust says :

    Even as far back as the NT4 days I’ve thought that the usefulness of the event logging process is “somewhat questionable”.

    I remember one instance from when I started out in IT. Back then, SCSI storage was expensive and the admins got by through regularly clearing down the logs. Despite this regular love and attention, one of our servers used to log that it was running out of drive space. The event log would get purged to the disk, which used up a bit more space and caused the server to whine again that it was still running out of space. This in turn was logged and…. you get the picture.

    One week the cleardown process got missed. Eventually the server stopped after a fit bought on by continual logging of “insufficient drive space” messages used up all the available drive space.

  3. AnotherAngryTech says :

    Gosh, I don’t believe it, there are people like me in the world. Reading some of your posts is like reading something I should write! I hold a similar role at a high archiving state school. Although we have ditched the RM rubbish and have a large number of tasty HP servers on a Server 2008 domain, we do have some constraints of the dreaded LA. I should much rather work in an independent school.

  4. M (@mattwhothat) says :

    You can use startup traceflag -T1222 to suppress successful backup messages (db and log) from appearing in the event log (and the SQL Server error log).

    ‘xp_cmdshell’ allows SQL server to run command line processes. You likely have some sort of job or stored procedure trying to enable that feature over and over whenever it is run. Could be some lazy code trying to force that setting instead of checking to see if its enabled, thus the 1=1 part. Reconfigure is the command you would run to ‘commit’ a settings change like that.

    SQL Server has its own internal alerting and auditing engine you can use to report and record various events that happen. You can log to the eventlog, the SQL Server error log, a table, send emails and so on. By default all drop database commands (and any similar commands, create database, drop table, etc) are recorded in the default trace log.

  5. jimbobmcgee says :

    The Event Log pretty much just echoes the lines that are written to the ERRORLOG file which, in itself, is somewhat annoying. Since ‘All rights reserved’ is the standard header line for the ERRORLOG file, it ends up in the Event Log, too.

    In theory, it is only supposed to write exceptional messages to the ERRORLOG file (and backup may or may not be exceptional, depending on whether you initiated it or one of your delightful students/staff members got in and is stealing your data!). DROP TABLE/DATABASE-type stuff is in the normal operation of SQL engine, so might not trigger an ERRORLOG write (I’m not saying I necessarily agree with this, just playing devil’s advocate).

    It is possible to log most of these actions with DDL triggers or Service Broker, if you are willing to put the effort in. Once you’ve done it a few times, you can script most of it to ease your deployment.

    http://www.mssqltips.com/sqlservertip/2121/event-notifications-in-sql-server-for-tracking-changes/ has some details for the Service Broker approach, although you’ll want to back it up with some deeper knowledge from http://technet.microsoft.com/en-us/library/ms189453.aspx.

    Should you have to jump through these hoops, though?