Have you ever encountered a situation where the compatibility level of your SQL Server database keeps changing automatically? In this blog post, we will explore the circumstances under which SQL Server automatically changes the compatibility level and how you can track these changes.

One of our readers recently shared their experience with us. They were working on a SQL Server 2008 instance that had a user database with a compatibility level of 80. Despite setting the compatibility level to 100 using the ALTER DATABASE command, the next day it would revert back to 80. This puzzled our reader, and they wanted to know why this was happening.

Firstly, it’s important to note that SQL Server does not automatically change the compatibility level on its own. There must be some automated activity or process responsible for this change. To track down the source of these changes, we can create a DDL trigger on the ALTER DATABASE command.

Here is an example of a DDL trigger that captures information about the ALTER DATABASE command:

IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'DDL_Database_Trigger')
    DROP TRIGGER DDL_Database_Trigger ON ALL SERVER;

GO

CREATE TRIGGER DDL_Database_Trigger ON ALL SERVER FOR ALTER_DATABASE
AS
DECLARE @cmd VARCHAR(200)
DECLARE @who VARCHAR(200)
DECLARE @fromwhere VARCHAR(200)

SELECT @cmd = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
SELECT @who = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)')
SELECT @fromwhere = EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(max)')

RAISERROR(N'!!!Database ALTERED!!! Command - %s By - %s From - %s.', 10, 1, @cmd, @who, @fromwhere) WITH LOG

GO

By implementing this trigger, you can identify who fired the ALTER DATABASE command, from which application it was fired, and what was the exact command that was executed.

For testing purposes, you can try executing the following commands:

USE [master]
GO
ALTER DATABASE [SQLAuthority] SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [SQLAuthority] ADD FILE (NAME = N'MM', FILENAME = N'E:\InvalidPath\MM.ndf', SIZE = 5120KB, FILEGROWTH = 1024KB) TO FILEGROUP [PRIMARY]

If you have configured the DDL trigger, you will see the captured information in the ERRORLOG. However, if you haven’t set up the trigger, don’t worry. You can still find some information logged in the default trace. You can refer to our previous blog post on “SQL SERVER – SSMS: Schema Change History Report” to learn more about the standard reports available in SQL Server.

Tracking changes to the compatibility level of your SQL Server databases can help you identify any unexpected modifications and take appropriate action. By implementing the DDL trigger, you can gain valuable insights into who is making these changes and from where.

Have you ever encountered similar situations on your SQL Server? Let us know in the comments!