I have 7 databases which are inaccessible.
In the sql server error log, I see them starting up but they never become available. In sysprocesses, I see them stuck in IO_RETRY wait type.
select spid, lastwaittype,cmd, status from master..sysprocesses order by spid;
The above situation is causes CPU consumption which increases over time.
What do I want now?
Remove these databases in any way possible, so that they don't consume any resources. I don't mind trying unconventional methods.
How did they end of in such a state
- We used Idera Virtual restore tool to create these dbs from a backup file.
- We renamed those DBs using SSMS directly.
- DBs were still accessible and readable.
- We altered some data on those dbs.
- Now DBs became inaccessible.
- We kills spids and restarted sql server without patience.
- Now Idera virtual restore service cannot remove them, neither can we drop them.
- Sql server cannot bring them up.