locked
Database compability changed to 9.0 from 8.0 RRS feed

  • Question

  • I have searched through this forum so I'm hoping somone might be able to shed milght onto my problem.

    I attached a database backup from a SQL Server 2000 database to a SQL Server 2005 and all was working well. It is a simple database, tables, stored procs, a few functions. Reporting Services 2005 are also being used to create reports.

    The database was working correctly and then, according to the DBA, the database somehow got changed into 9.0 compability mode. As soon as the database is in 9.0, it stops functioning.

    I know for a fact, there are no calls in any of the stored procedures or functions to change the compability mode and no one is owning up to changing the setting. Is it possible that reporting services or something in the database engine could have changed this without intervention of an administrator? Or can this only be changed by an explicit call from a user?

    Wednesday, December 2, 2009 4:24 PM

Answers

  • SQL Server doesn't change compat level by itself. Somebody did this. It is possible that you can find out more from errorlog file/eventlog/default trace.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Proposed as answer by sqldbg Friday, December 4, 2009 3:21 AM
    • Marked as answer by Todd James Monday, December 7, 2009 3:03 PM
    Wednesday, December 2, 2009 4:39 PM
  • It is not logged to the ErrorLog in 2005, and it is not explicitly tracked by the default trace.  Instead you would see a series of AUDIT DBCC Events:

    dbcc showfilestats
    dbcc sqlperf(logspace)
    dbcc flushprocindb(@dbid)  


    The following would help find that information:

    DECLARE @FileName VARCHAR(MAX)  
    
    SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
    FROM sys.traces   
    WHERE is_default = 1;  
    
    SELECT   --*,
    	gt.EventClass,
    	e.name as EventName,
    	gt.TextData,
    	gt.ObjectID,
    	gt.ObjectName,
    	gt.DatabaseName,
    	gt.SessionLoginName,
    	gt.StartTime,
    	gt.ApplicationName,
    	gt.HostName,
    	gt.NTUserName,
    	gt.NTDomainName
    FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt
    JOIN sys.trace_events e 
    	ON gt.EventClass = e.trace_event_id
    WHERE gt.EventClass = 116  -- Audit DBCC Event

    However, as consistent as those events are in testing this, unless someone confessed to doing it based on the evidence I wouldn't consider it to be rock solid because its auditing associated events not the specific change.  That's up to you.  The first 2 events occur inside the database that has the compatibility change happen, and the last is done in master.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by Todd James Monday, December 7, 2009 3:08 PM
    Friday, December 4, 2009 12:39 AM

All replies

  • SQL Server doesn't change compat level by itself. Somebody did this. It is possible that you can find out more from errorlog file/eventlog/default trace.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Proposed as answer by sqldbg Friday, December 4, 2009 3:21 AM
    • Marked as answer by Todd James Monday, December 7, 2009 3:03 PM
    Wednesday, December 2, 2009 4:39 PM
  • AFAIK, the only way to change this is with an explicit call the sp_dbcmptlevel or through the GUI (Management Studio). I don't changing this is logged anywhere either unfortunately.


    every day is a school day
    Wednesday, December 2, 2009 4:40 PM
  • I know for sure that on 2008, it is logged to errorlog/eventlog. I'm not sure whether it is on 2005. Also, I haven't myself looked in default trace - which in case also would say by whom.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, December 2, 2009 4:49 PM
  • Perfect, thanks for the quick response.

    Wednesday, December 2, 2009 5:19 PM
  • It indeed only can be done by a manual action of somebody.

    Anyway I advise you to use compatibility level 90 when you run on SQL 2005. Try to find the problem issues with SQL Server 2005 upgrade advisor:
    http://www.microsoft.com/downloads/details.aspx?FamilyID=1470e86b-7e05-4322-a677-95ab44f12d75&displaylang=en
    Thursday, December 3, 2009 9:55 PM
  • It is not logged to the ErrorLog in 2005, and it is not explicitly tracked by the default trace.  Instead you would see a series of AUDIT DBCC Events:

    dbcc showfilestats
    dbcc sqlperf(logspace)
    dbcc flushprocindb(@dbid)  


    The following would help find that information:

    DECLARE @FileName VARCHAR(MAX)  
    
    SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
    FROM sys.traces   
    WHERE is_default = 1;  
    
    SELECT   --*,
    	gt.EventClass,
    	e.name as EventName,
    	gt.TextData,
    	gt.ObjectID,
    	gt.ObjectName,
    	gt.DatabaseName,
    	gt.SessionLoginName,
    	gt.StartTime,
    	gt.ApplicationName,
    	gt.HostName,
    	gt.NTUserName,
    	gt.NTDomainName
    FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt
    JOIN sys.trace_events e 
    	ON gt.EventClass = e.trace_event_id
    WHERE gt.EventClass = 116  -- Audit DBCC Event

    However, as consistent as those events are in testing this, unless someone confessed to doing it based on the evidence I wouldn't consider it to be rock solid because its auditing associated events not the specific change.  That's up to you.  The first 2 events occur inside the database that has the compatibility change happen, and the last is done in master.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by Todd James Monday, December 7, 2009 3:08 PM
    Friday, December 4, 2009 12:39 AM