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.
