Sql-server – SQL logs filling up with “Setting database option Compatibility_Level” messages

sql-server-2008ssrs

On several of my servers, I have "Setting database option Compatibility_Level" messages filling up the SQL log and application event log to the tune of twice every 5 seconds. This makes finding anything else in the SQL log nearly impossible.

I'm aware that this is a known bug in MS Reporting Services (marked "Closed as Won't Fix"). I'm looking for other ideas to resolve this. Using profiler, I found the code being run by the reporting server is:

DECLARE @currVer nvarchar(128), @currMajorVer nvarchar(32)
     , @idx int, @currMajorVerInt tinyint ;

SELECT @currVer = CONVERT(nvarchar(128), ServerProperty('ProductVersion')) ;
SET @idx = CHARINDEX('.', @currVer, 0) ;
SET @currMajorVer = SUBSTRING(@currVer, 1, @idx-1) ;
SET @currMajorVerInt = CONVERT(tinyint, @currMajorVer) ;

DECLARE @dbname sysname

IF @currMajorVerInt =  10
BEGIN       
    SELECT @dbname=DB_NAME()
    EXEC sp_dbcmptlevel @dbname, 100 ;      
END

Ideas?

The listed "workaround" suggested modifying the system stored procedure sp_dbcmptlevel, which although possible, is not recommended for a variety of good reasons.

Another thread on this issue suggested changing the system account used by the reporting services service. I'm not sure if that applies to our situation. Has anyone found that this resolves the issue for you?

Only other idea I had: is there any way to intercept and either block a query from being executed, or to alter the syntax of the query before it is executed? Like a kind of INSTEAD TRIGGER or something?

Best Answer

Only a couple of things come to mind.

  1. Hack sp_dbcmptlevel and add a "IF suser_sname() = 'Your SSRS Account' RETURN" to the very top of it.

  2. Add a DDL trigger to the database that simply rolls back any alter database statements.

There's no way to intercept the database call and prevent it from being sent to the database engine.

Related Question