3

I'm with a simple task to migrate databases.

I'm doing the backup in the primary database, setting it to OFFLINE, and then, migrating it to the secondary.

No problem at all.

but I notice something strange.

I check if someone is using the database with sp_WhoIsActive, and it shows nothing.

Then I use alter database X set offline.

Then it doesn't change it at all, and the query keeps running. Then I check again to see if something is using the database, and for my surprise, it shows something running for 22hrs, 20hrs, or 44min.

Why doesn't sp_WhoIsActive show it?


Example:

1st run:

enter image description here

Then Alter etc etc set offline. Then it locks, and I check again with sp_WhoIsActive

enter image description here

Bam. Something running.

6

Nothing is wrong -- you just have to read the documentation. By default, you don't see sleeping SPIDs unless they're blocking another request.

The SPID you're looking at is likely a sleeping session.

Try running

EXEC dbo.sp_WhoIsActive @show_sleeping_spids = 1

More to your problem, you might want to set the database to single user before trying to set it offline.

1

More to your problem, you might want to set the database to single user before trying to set it offline.

It will not solve the problem, it's the same: trying to set database offline or in single_user, if there are (sleeping)sessions [any user connected to that database, for example, just open a query window using context of that database], they have S-lock on the database, and the session executing ALTER DATABASE will be block because it needs X-lock on the database; what you should do instead is to use

alter database ... set offline with rollback immediate

So the blocking sessions will be rolled back and release S-locks on that database

  • 1
    This could be a comment on erik's answer, but thanks. I completely forgot about rollback immediate. – Racer SQL May 17 '17 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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