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.


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.