OFFLINING and "Database 'xxx' is in transition. Try the statement later."

microsoft.public.sqlserver.server

15 Mar 2006 1:27 PM

Paul Ibison

A colleague offlined a database and subsequently ended up in a mess.

I looked at it and got the error "Database 'xxx' is in transition. Try the
statement later."

when:

using sp_who2
using the Activity Monitor.
selecting from sysprocesses

select * from sys.dm_exec_requests showed the offending 'ALTER DATABASE'
statement so I was able to get the SPID and then KILL it.

Firstly - is this a common thing with OFFLINing databases when they are in
use? There's no mention of this in BOL, but presumably the database should
be made single user before offlining it?

Secondly - I make a database RO rather than offlining it, but I would like
to know what offlining is used for by others. I assumed it was like
detaching, while retaining the metadata. BOL has a caution in the OFFLINE
section of ALTER DATABASE which makes the issue more vague ("Use this option
only when the file is corrupted and can be restored").

Cheers,

Pau lIbison