One of the best ways of monitoring your database server health and general well-being is to keep an eye on the SQL Server Log. A lot of information gets written to this log, and it can be quite daunting if you are trying to look for pointers in the event of a server failure or other issue. It is better to familiarise yourself with the ‘normal’ messages you would expect to see and then the ones that are different are easy to spot.
On one clients server recently I noticed a message that I hadn’t seen before
FILESTREAM: effective level = 0, configured level = 0, file system access share name = ‘MSSQLSERVER’.
After a little digging around I found that these where being generated at the same time as a RECONFIGURE statement was being executed, in fact the other messages around this one reflected exactly what I was expecting (xp_cmdshell was being temporarily enabled for some task and then disabled). What was confusing was that FILESTREAM was not configured on this server, so reporting out the config options was both annoying and simply not useful.
I raised the issue on Connect and although it was acknowledged that the message will appear, even if there is no change to the FILESTREAM configuration, it was promptly closed as ‘By Design’, but with the teaser that
… we will look for an opportunity to clean up the redunndant message logging in a future release.