I have faced numerous issues , but some are strange and may occur if certain default values are left enabled. Either they were skipped during post config check after SQL was installed or were enabled later for some issue and weren’t reverted.
One fine day a SQL DB Server which was supposed to be Role Model server, as in it went on with its business without ever bugging the DBA’s, and on this day it was generating multiple alerts which were sort of strange with alert text being
“The Windows Event Log Provider monitoring the System Event Log is 11 minutes behind in processing events. This can occur when the provider is restarted after being offline for some time, or there are too many events to be handled by the workflow. One or more workflows were affected by this. “
The text doesn’t seem like a SQL server alert but being the curious cat I am, I thought lets see whats happening on the server. I checked SQL server error log everything at first glance seems fine and dandy , my next stop was Windows Event Viewer, it took considerable time opening, which alerted my senses. Once it opened on further digging O found that the Application log in windows Event viewer was getting flooded with below event 1000 times per minute.
Login succeeded for user ‘NT AUTHORITY\SYSTEM’. Connection made using Windows authentication.
This made me realized that SQL server is auditing login for both failed and successful logins and dumping them in EventViewer.
By default the setting is for failed logins only, this DB server was tied to an ISV(Independent Software Vendor) so had to check with them if this is something that is required for their application. The support guy said no. So I updated the setting to start auditing failed login only and the server stopped generating the alerts and went to being the Role Model it was :D.