Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB

Question

The error log on my sql server throws this warning every 5 seconds

'Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.'

From what I can tell this may be due to my environment having a mix of 2005 and 2008 SQL Servers, with linked servers between them and reports that pull from each, but I am not sure exactly what this message means.

Is this message warning me of a problem? If so how do I resolve the underlying issue. If not, how do I get this message to stop flooding my error logs.

Answer 1

We get this as well in our SSRS 2008 environment (although luckily only once a day). Apparently it is a known bug, but will not affect the functionality of Reporting Services.

http://connect.microsoft.com/SQLServer/feedback/details/488169/setting-database-option-compatibility-level-to-100-for-reportserver

From Microsoft: "Thanks for posting this issue to me. We have fixed this bug in SQL Server 2008 R2 CTP2 but we decided not fix it in SQL Server 2008 due to concerns of breaking backward compatibility. Thanks again for the feedbacks."

Answer 2

If you run a trace (SQL Server Profiler), you can confirm the bug.

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 

USE [ReportServerTempDB] 

IF @currMajorVerInt = 10 
  BEGIN 
      SELECT @dbname = DB_NAME() 

      EXEC sp_dbcmptlevel 
        @dbname, 
        100; 
  END 

USE [ReportServer]

You can also confirm the 12-hour restart behaviour is as per default installation settings:

<Configuration>
    <Service>
        <RecycleTime>720</RecycleTime>
    </Service>
</Configuration>

Specifies a recycle time for the application domain, measured in minutes. Valid values range from 0 to maximum integer. The default is 720.

http://msdn.microsoft.com/en-us/library/ms157273(v=SQL.100).aspx

No further action is required, unless you consider the merits of changing the RecycleTime.