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.

Paul White
  • 94,921
  • 30
  • 437
  • 687
Racer SQL
  • 7,546
  • 16
  • 77
  • 140

2 Answers2

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.

Paul White
  • 94,921
  • 30
  • 437
  • 687
Erik Reasonable Rates Darling
  • 45,549
  • 14
  • 145
  • 532
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

sepupic
  • 11,267
  • 18
  • 27