| Paul Ibison 2006-03-15, 9:23 am |
| 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
|