February 6, 2013 at 11:49 pm
Hello,
Please any one can help me, how to recover databases or change status from in recovery to normal. i had go through all the articals which is suggested by google.
In SQL server error log shows below information, please suggest something in positive manner.
"2013-02-07 11:43:51.61 Server Process 18:0:0 (0x1598) Worker 0x00000000043B21C0 appears to be non-yielding on Scheduler 3. Thread creation time: 13004279824516. Approx Thread CPU Used: kernel 62 ms, user 4789 ms. Process Utilization 0%. System Idle 98%. Interval: 411390903 ms.
2013-02-07 11:44:51.70 Server Process 18:0:0 (0x1598) Worker 0x00000000043B21C0 appears to be non-yielding on Scheduler 3. Thread creation time: 13004279824516. Approx Thread CPU Used: kernel 62 ms, user 4789 ms. Process Utilization 0%. System Idle 98%. Interval: 411450994 ms.
2013-02-07 11:45:51.79 Server Process 18:0:0 (0x1598) Worker 0x00000000043B21C0 appears to be non-yielding on Scheduler 3. Thread creation time: 13004279824516. Approx Thread CPU Used: kernel 62 ms, user 4804 ms. Process Utilization 0%. System Idle 98%. Interval: 411511084 ms.
2013-02-07 11:46:51.88 Server Process 18:0:0 (0x1598) Worker 0x00000000043B21C0 appears to be non-yielding on Scheduler 3. Thread creation time: 13004279824516. Approx Thread CPU Used: kernel 62 ms, user 4804 ms. Process Utilization 0%. System Idle 98%. Interval: 411571175 ms.
2013-02-07 11:47:51.97 Server Process 18:0:0 (0x1598) Worker 0x00000000043B21C0 appears to be non-yielding on Scheduler 3. Thread creation time: 13004279824516. Approx Thread CPU Used: kernel 62 ms, user 4804 ms. Process Utilization 0%. System Idle 98%. Interval: 411631266 ms.
2013-02-07 11:48:52.06 Server Process 18:0:0 (0x1598) Worker 0x00000000043B21C0 appears to be non-yielding on Scheduler 3. Thread creation time: 13004279824516. Approx Thread CPU Used: kernel 62 ms, user 4804 ms. Process Utilization 0%. System Idle 98%. Interval: 411691357 ms.
2013-02-07 11:49:52.15 Server Process 18:0:0 (0x1598) Worker 0x00000000043B21C0 appears to be non-yielding on Scheduler 3. Thread creation time: 13004279824516. Approx Thread CPU Used: kernel 62 ms, user 4804 ms. Process Utilization 0%. System Idle 98%. Interval: 411751448 ms.
2013-02-07 11:50:52.24 Server Process 18:0:0 (0x1598) Worker 0x00000000043B21C0 appears to be non-yielding on Scheduler 3. Thread creation time: 13004279824516. Approx Thread CPU Used: kernel 62 ms, user 4804 ms. Process Utilization 0%. System Idle 98%. Interval: 411811539 ms."
Please help.....(:
February 7, 2013 at 1:35 am
What caused the database to go into recovery?
Someone restore the DB and not issue a RECOVERY command?
Someone backup the tail log and issues with NORECOVERY?
Restarted the SQL service?
February 7, 2013 at 7:46 am
The message in the SQL Server log might not be related the database status.
Is the database Log-Shipped (Secondary Server)?
Following command is used to bring the database online:
RESTORE DATABASE <dbname> WITH RECOVERY
February 7, 2013 at 8:25 am
Suresh B. (2/7/2013)
The message in the SQL Server log might not be related the database status.Is the database Log-Shipped (Secondary Server)?
Following command is used to bring the database online:
RESTORE DATABASE <dbname> WITH RECOVERY
That's for a database in the RESTORING state, not for a database in the RECOVERING state.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2013 at 8:31 am
anthony.green (2/7/2013)
Someone restore the DB and not issue a RECOVERY command?Someone backup the tail log and issues with NORECOVERY?
Both result in the RESTORING state, not the RECOVERING state.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2013 at 9:37 am
parminder.parmindersingh (2/6/2013)
Hello,Please any one can help me, how to recover databases or change status from in recovery to normal. i had go through all the articals which is suggested by google.
In SQL server error log shows below information, please suggest something in positive manner.
"2013-02-07 11:43:51.61 Server Process 18:0:0 (0x1598) Worker 0x00000000043B21C0 appears to be non-yielding on Scheduler 3. Thread creation time: 13004279824516. Approx Thread CPU Used: kernel 62 ms, user 4789 ms. Process Utilization 0%. System Idle 98%. Interval: 411390903 ms.
2013-02-07 11:44:51.70 Server Process 18:0:0 (0x1598) Worker 0x00000000043B21C0 appears to be non-yielding on Scheduler 3. Thread creation time: 13004279824516. Approx Thread CPU Used: kernel 62 ms, user 4789 ms. Process Utilization 0%. System Idle 98%. Interval: 411450994 ms.
2013-02-07 11:45:51.79 Server Process 18:0:0 (0x1598) Worker 0x00000000043B21C0 appears to be non-yielding on Scheduler 3. Thread creation time: 13004279824516. Approx Thread CPU Used: kernel 62 ms, user 4804 ms. Process Utilization 0%. System Idle 98%. Interval: 411511084 ms.
2013-02-07 11:46:51.88 Server Process 18:0:0 (0x1598) Worker 0x00000000043B21C0 appears to be non-yielding on Scheduler 3. Thread creation time: 13004279824516. Approx Thread CPU Used: kernel 62 ms, user 4804 ms. Process Utilization 0%. System Idle 98%. Interval: 411571175 ms.
2013-02-07 11:47:51.97 Server Process 18:0:0 (0x1598) Worker 0x00000000043B21C0 appears to be non-yielding on Scheduler 3. Thread creation time: 13004279824516. Approx Thread CPU Used: kernel 62 ms, user 4804 ms. Process Utilization 0%. System Idle 98%. Interval: 411631266 ms.
2013-02-07 11:48:52.06 Server Process 18:0:0 (0x1598) Worker 0x00000000043B21C0 appears to be non-yielding on Scheduler 3. Thread creation time: 13004279824516. Approx Thread CPU Used: kernel 62 ms, user 4804 ms. Process Utilization 0%. System Idle 98%. Interval: 411691357 ms.
2013-02-07 11:49:52.15 Server Process 18:0:0 (0x1598) Worker 0x00000000043B21C0 appears to be non-yielding on Scheduler 3. Thread creation time: 13004279824516. Approx Thread CPU Used: kernel 62 ms, user 4804 ms. Process Utilization 0%. System Idle 98%. Interval: 411751448 ms.
2013-02-07 11:50:52.24 Server Process 18:0:0 (0x1598) Worker 0x00000000043B21C0 appears to be non-yielding on Scheduler 3. Thread creation time: 13004279824516. Approx Thread CPU Used: kernel 62 ms, user 4804 ms. Process Utilization 0%. System Idle 98%. Interval: 411811539 ms."
Please help.....(:
Can you let us know --
What is the sql server version ?
Is there a dump file associated with the non-yielding scheduler ?
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
February 7, 2013 at 12:03 pm
[font="Verdana"]Parminder,
Could you provide us some more information as to how the database went into recovering mode?
Are there any messages in the errorlog that say that the DB was shutdown? Do you see any disk I/O requests taking longer than 15 seconds kind of messages?
What do you see in exec_requests (or) sysprocesses? Anything interesting?
A little bit more information from your errorlog other than the non-yeilding scheduler messages would help the community understand your problem a little bit better.
Regards,
[/font]
February 8, 2013 at 3:34 am
GilaMonster (2/7/2013)
anthony.green (2/7/2013)
Someone restore the DB and not issue a RECOVERY command?Someone backup the tail log and issues with NORECOVERY?
Both result in the RESTORING state, not the RECOVERING state.
Doh..
February 8, 2013 at 8:23 am
I was shrinking a database, where its stuck and SSMS goes in not responding. I had wait till half an hour but no luck.
then i just went to the respective server and restart the SQL server service into the SQL Configuration management. When i came back to my pc and contact to server it shows 4 database "In Recovery" mode. I was shocked, because all my live databases. These all databases on SQL Server 2005 SP1.
And checked SQL error log got those details which i had posted on my earlier post. Please guide me, how i can make it correct.
February 8, 2013 at 8:45 am
The only thing you can do for a recovering database is wait. If the schedulers are still hung may require another restart, bear in mind that'll undo any recovery work that's already completed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2013 at 8:58 am
parminder.parmindersingh (2/8/2013)
I was shrinking a database, where its stuck and SSMS goes in not responding. I had wait till half an hour but no luck.then i just went to the respective server and restart the SQL server service into the SQL Configuration management. When i came back to my pc and contact to server it shows 4 database "In Recovery" mode. I was shocked, because all my live databases. These all databases on SQL Server 2005 SP1.
And checked SQL error log got those details which i had posted on my earlier post. Please guide me, how i can make it correct.
If you backed the database up immediately before shrinking it, one possibility might be to recreate the database and then restore from the backup.
Tom
February 8, 2013 at 9:49 am
GilaMonster (2/8/2013)
The only thing you can do for a recovering database is wait. If the schedulers are still hung may require another restart, bear in mind that'll undo any recovery work that's already completed.
[font="Verdana"]We should be able to see what the scheduler is waiting on if we query dm_exec_requests based on scheduler id right? Also, since the worker address is mentioned would it be possible to query workers and threads to see what the os thread_id is and then from a performance monitor(assuming OS is win2008) can we see what the problem with the particular thread is?[/font]
February 8, 2013 at 10:00 am
hemanth.damecharla (2/8/2013)
GilaMonster (2/8/2013)
The only thing you can do for a recovering database is wait. If the schedulers are still hung may require another restart, bear in mind that'll undo any recovery work that's already completed.[font="Verdana"]We should be able to see what the scheduler is waiting on if we query dm_exec_requests based on scheduler id right? Also, since the worker address is mentioned would it be possible to query workers and threads to see what the os thread_id is and then from a performance monitor(assuming OS is win2008) can we see what the problem with the particular thread is?[/font]
Maybe with a debugger and the public symbols, but then what? The schedulers are hung, so you can't kill a session even if you can figure out which one is on the schedulers and it's a user session (which since things are in recovery is unlikely)
If it's a shrink that's getting rolled back/forward, the schedulers are probably stuck in some preemptive section of IO code
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2013 at 10:02 am
Hello Sir,
from last 6 days i am continually monitoring the SQL error log, till today evening it show 99%. i don't know how much time it will to take complete it.
But if i restart the server or SQL services all recovery will undo. Then what is benefit of restart Server? Did not get your post, will you please elaborate it.
Thanks
Parminder Singh
February 8, 2013 at 10:16 am
parminder.parmindersingh (2/8/2013)
But if i restart the server or SQL services all recovery will undo. Then what is benefit of restart Server?
Currently the schedulers are hung. If all of them are, then no work is happening. Restarting might get recovery to proceed normally (or the schedulers might hang again)
Might be worth calling MS support and opening a case with them
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply