November 28, 2017 at 8:28 am
Dear all,
I am changing the compatibility level of one of my databases , but passed already 30 minutes and the operation is still ongoing is this normal?
Here is the script used:
DECLARE @name VARCHAR(50) -- database name
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name IN ('dwhLogging')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
exec ('ALTER DATABASE ' + @name + ' SET SINGLE_USER')
exec('ALTER DATABASE ' + @name + ' SET COMPATIBILITY_LEVEL = 130')
exec ('ALTER DATABASE ' + @name + ' SET MULTI_USER ' )
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
November 28, 2017 at 8:46 am
Could be several things. You have transactions that you're waiting on to complete before the database goes into single user. Alternatively, someone else connected ahead of you and you're waiting for access to connect to the database to put it into single user.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 28, 2017 at 9:08 am
Done. Added the rollback immetiate to the set in single user mode command. It worked. Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply