1

Situation

  • 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;

enter image description here

Effects

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

  1. We used Idera Virtual restore tool to create these dbs from a backup file.
  2. We renamed those DBs using SSMS directly.
  3. DBs were still accessible and readable.
  4. We altered some data on those dbs.
  5. Now DBs became inaccessible.
  6. We kills spids and restarted sql server without patience.
  7. Now Idera virtual restore service cannot remove them, neither can we drop them.
  8. Sql server cannot bring them up.
0
0

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.

To remove them just stop SQL Server and delete MDF and LOG files. I assume you have good backups.

1
  • I didn't care about those databases, so this solution was perfect. Thank you. – Nikhil S Aug 4 '16 at 8:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.