Dropping a RECOVERING database

Hi,

Is there any way(better) to drop a RECOVERING database?

We tried to change a db from READ ONLY state to READ/WRITE state and this took forever (db is very small 40 MB) and we tried to kill this process and this went to KILLED/ROLLBACK state and blocking other processes.

We tried to even stop the sql server but that didn't work. We had to reboot the windows server. Now that particular database is in 'RECOVERING' state and we want to get rid of it. We've a backup of it.

Any ideas how to drop this database?

Because of this RECOVERING database, SQL Agent is not starting. The Agent error log says, it's waiting on SQL server to complete the recovery.

We've SQL 2005 with service pack1.

Any ideas are highly appreciated.

Thanks,

Siva.

You can stop sqlserver service. Rename the log file. Restart sqlserver service - this will put the database into suspect mode. You should be able to drop the database now.
(do be sure you have a good backup before forcing the deletion outlined above).|||

Thanks........that worked....

Still don't understand why it took so long to change from READ ONLY to READ/WRITE and also why it took so long for rollback as well.

|||Do you still have the errorlogs?

--

Peter Byrne

Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

wrote in message

news:1acc8880-1e4a-44e2-80d0-1cdb75e87ad6@.discussions.microsoft.com...

> Thanks........that worked....

>

> Still don't understand why it took so long to change from READ ONLY to

> READ/WRITE and also why it took so long for rollback as well.

>

>

>

>

>

>|||

We did see in the error log the STACK DUMP thing and the following message many times:

Process 71:0:0 (0x69c) Worker 0x78FF80E8 appears to be non-yielding on Scheduler 2. Thread creation time: 12804246994487. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 0%. System Idle 98%. Interval: 7331203 ms.

71 was the process we killed but was unyielding.....

|||Can you post the errorlog and the .mdmp from the stack dump?

--

Peter Byrne

Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

wrote in message

news:270127c2-61e0-44c0-bda8-fa176b32924f@.discussions.microsoft.com...

> We did see in the error log the STACK DUMP thing and the following

> message many times:

>

>

>

> Process 71:0:0 (0x69c) Worker 0x78FF80E8 appears to be non-yielding on

> Scheduler 2. Thread creation time: 12804246994487. Approx Thread CPU

> Used: kernel 0 ms, user 0 ms. Process Utilization 0%. System Idle 98%.

> Interval: 7331203 ms.

>

>

>

> 71 was the process we killed but was unyielding.....

>

>|||

I've 4 .mdmp files each is about 2.5 MB.

I don't know how I can post it here...

Do you need any specific info?

|||Can you please mail one to me directly? The one with the lowest numbered

extension would likely be the best one.

--

Peter Byrne

Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

wrote in message

news:9aeb4aec-aa39-43b3-9ab2-658e5d06f246@.discussions.microsoft.com...

> I've 4 .mdmp files each is about 2.5 MB.

>

> I don't know how I can post it here...

>

> Do you need any specific info?

>

>

>

>|||

What's your e-mail address?

Is it 9aeb4aec-aa39-43b3-9ab2-658e5d06f246@.discussions.microsoft.com ?

or ?

|||Its peterbyr@. microsoft.com.

--

Peter Byrne

Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

wrote in message

news:a76af29a-9309-4a90-b52f-b469160de25a@.discussions.microsoft.com...

> What's your e-mail address?

>

>

>

> Is it 9aeb4aec-aa39-43b3-9ab2-658e5d06f246@.discussions.microsoft.com ?

>

> or ?

>

>sql