1

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.

1 Answer 1

1

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';
3
  • 1
    Thanks for the reply Marcello! We eventually did restart the SQL server and fixed the issue that way. Iam not sure anymore if we did try this, because we later on did try some similar queries, but I cannot recall which ones. I will mark your reply as anwser anyway, since this definitely would have been helpfull. Jun 13, 2023 at 13:06
  • 1
    Yes Niels, specially when restarting the sql server (either server or service) is not a cheap option, better to find the process that is using the resources and deal with it. dont forget this check this script. It has helped me a few times. cheers! Jun 13, 2023 at 16:57
  • 1
    Thanks Marcello! Another great tip! Jun 15, 2023 at 5:11

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.