Change compatibility level of SQL Server Database

Objective: Change compatibility level of database

Step I: Change database to single user before changing the db compatibility

ALTER DATABASE [DBNAME] SET MULTI_USER WITH NO_WAIT

----

ALTER DATABASE DBNAME SET SINGLE_USER WITH NO_WAIT

------

EXEC sp_dboption 'DBNAME', 'single user', 'true'

GO

Step II: Kill all spids for that database except your spid

Select spid from master..sysprocesses where dbid = db_id('DBNAME') and spid <> @@spid

--------

Kill those spids using kill command:

For eg: kill 94

--------

Or you can close all connections using the below script:

/**

DECLARE @DatabaseName nvarchar(50)

DECLARE @SPId int

SET @DatabaseName = N'DBNAME'

DECLARE my_cursor CURSOR FAST_FORWARD FOR

SELECT SPId FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DBNAME) AND SPId <> @@SPId

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @SPId

WHILE @@FETCH_STATUS = 0

BEGIN

KILL @SPId

FETCH NEXT FROM my_cursor INTO @SPId

END

CLOSE my_cursor

DEALLOCATE my_cursor

********/

Step III Change compatibility level:

EXEC sp_dbcmptlevel AdventureWorks, 80;

GO

----SQL Server 2000 database compatible level to SQL Server 2005

EXEC sp_dbcmptlevel AdventureWorks, 90;

GO

Step IV: Change single user to multi user:

EXEC sp_dboption 'c_data1dk', 'single user', 'false'