Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I've been researching a problem which was diagnosed as someone changing the compatability mode for the SQL Server database from 80 to 90 on SQL Server 2005 for a database which had it's compatibility mode set to 80 due to legacy stored procedure code not having been upgraded.

I found that when changing the compatibility mode this is not logged in the SQL Server logs.

Has anyone seen this issue and is this a bug or is there an alternative method to track these changes?

share|improve this question

2 Answers 2

Of course is logged. Easy breazy validation test:

create database foo
alter database foo set recovery full
backup database foo to disk='nul:'
go

exec sp_dbcmptlevel 'foo', '80'
go

use foo
select * from fn_dblog(null, null)
go

The change will be there to see:

LOP_MODIFY_ROW  LCX_BOOT_PAGE ... 0x50

The boot page of the database has updated the compatibility level to 0x50 (or '80').

Besides, the change is logged into ERRORLOG:

2010-04-08 16:15:39.37 spid52      Setting database option COMPATIBILITY_LEVEL to 80 for database 'foo'.

And it creates an system NT event log event as well:

Log Name:      Application
Source:        ...
Date:          4/8/2010 4:15:39 PM
Event ID:      5084
Task Category: Server
Level:         Information
Keywords:      Classic
User:          ...
Computer:      ...
Description:
Setting database option COMPATIBILITY_LEVEL to 80 for database 'foo'.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="..." />
    <EventID Qualifiers="16384">5084</EventID>
    <Level>4</Level>
    <Task>2</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2010-04-08T23:15:39.000000000Z" />
    <EventRecordID>4965</EventRecordID>
    <Channel>Application</Channel>
    <Computer>...</Computer>
    <Security UserID="S-1-5-..." />
  </System>
  <EventData>
    <Data>COMPATIBILITY_LEVEL</Data>
    <Data>80</Data>
    <Data>foo</Data>
    <Binary>...</Binary>
  </EventData>
</Event>
share|improve this answer

I don't believe it is logged anywhere. It might be in the default trace, but I wouldn't think so. Permissions for sp_dbcmptlevel default to only members of the sysadmin fixed server role. That narrows down who can execute the stored procedure.

By running server side traces you could detect the command being executed, but this can get hard to manage. It's probably the only sure way, though. Another option is to periodically run queries against sys.databases checking for compatibility level and other changes.

share|improve this answer
    
Isn't it crazy that this is not logged. Whilst this is limited to the SA account, you can do it with an account with SA role. If you have x number of DBAs you would not be able to pin in on one of them. –  smithsi Mar 4 '10 at 11:20
    
One of the guys working with me, came up with the solution of rolling the transaction logs forward on a copy to discover when the change occurred. Basically: 1. Load from last know good dump. Check compatibility. 2. Roll forward transaction logs until compatibility mode changes. There is no logging of changes to compatibility mode. Not sure which stack traces would reveal this information. –  smithsi Mar 4 '10 at 15:28
    
Now I'm scratching my head because I'm seeing the logging Remus indicated. But when I ran through this before answering the first time, I didn't... not sure why now. –  K. Brian Kelley Jul 21 '10 at 15:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.