Strange Situation with sp_WhoIsActive

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:

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

Bam. Something running.

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.

StackExchange Database Administrators Q#173708, answer score: 6