SET SINGLE_USER stuck on wait type PRINT_ROLLBACK_PROGRESS

Question

I want to drop a database on one of our systems (SQL Server 2016 SP2 CU17), but the process to set the database in single user mode is stuck on the wait type PRINT_ROLLBACK_PROGRESS. I use the statement below.

GO
ALTER DATABASE MyDatabaseName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

Also the messages tab generates this message a million times:

"Nonqualified transactions are being rolled back. Estimated rollback completion: 0%."

We also left the statement run for 1,5 hours and no luck unfortunately.

So I check to see if there were any open transactions with DBCC OPENTRAN for the database, but there are none. Secondly, sp_WhoIsActive does not show any active process on the database. Sp_who2 also does not show any connection anymore to the database in question. I found a thread somewhere that suggested to set the database option AUTO_UPDATE_STATISTICS_ASYNC off, but that was already off.

I really do suspect however that there is some system process that is doing something that prevents the statement above to successfully set the db in single user mode. I also tried WITH NO_WAIT, but that gave me an error message after the timeout as expected. I also turned off AUTO_UPDATE_STATISTICS just to be sure, but that didn't help ofcourse.

I think that a server reboot will be possible since there are no user processes that are rolling back, so I don't expect any extreme long startup time. However, a reboot will only be possible in 2 weeks time from now, since this is a production server, so Iam looking for a more clean way to make this work. Hope someone can help.

score 1 · Accepted Answer · answered Jun 07 '23 at 13:02

IN cases like this one I think you can have a better view of who is using the database via sys.dm_tran_locks.

-- Could not obtain exclusive lock on database model - who is using it

--Your model database is locked now so what you should do is to find out --the session that has a lock on it, this can be done using sys.dm_tran_locks:

select request_session_id
from sys.dm_tran_locks
where resource_type = 'database' and
      --resource_database_id = 3 and
      request_type = 'LOCK' and
      request_status = 'GRANT';

SET SINGLE_USER stuck on wait type PRINT_ROLLBACK_PROGRESS

1 Answers1