I’ve had a lot of problems, but some of them are strange and can happen if certain default values are left on. Either they were enabled later for some reason and not restored, or they were skipped during the post-config check after SQL was installed.
One lovely day, a SQL DB Server that was intended to be a Role Model server, in the sense that it went about its business without bothering the DBAs, was generating many alerts that were odd, with alert text stating
“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 does not appear to be a SQL server alert, but being the curious cat that I am, I decided to investigate further. I checked the SQL server error log and everything appeared to be in order at first glance. My next destination was Windows Event Viewer, which took a long time to launch, which piqued my interest. Further investigation revealed that the Application log in Windows Event viewer was being filled with below events 1000 times per minute once it was opened.
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; however, because this database server is related to an ISV (Independent Software Vendor), I had to check with them to see if this was something they needed for their application. No, said the support person. So I changed the configuration to audit just unsuccessful logins, and the server stopped generating alerts and resumed its role as Role Model :D.