Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Database in Recovery mode from last 7 days. Expand / Collapse
Author
Message
Posted Friday, February 8, 2013 8:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 7,851, Visits: 9,602
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
Post #1417762
Posted Friday, February 8, 2013 9:49 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 1:42 AM
Points: 448, Visits: 322
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.


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?


-Hope is a heuristic search ~Hemanth
Post #1417800
Posted Friday, February 8, 2013 10:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 40,385, Visits: 36,829
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.


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?


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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1417811
Posted Friday, February 8, 2013 10:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 30, 2014 5:23 AM
Points: 6, Visits: 209
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
Post #1417812
Posted Friday, February 8, 2013 10:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 40,385, Visits: 36,829
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1417816
Posted Friday, February 8, 2013 10:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 30, 2014 5:23 AM
Points: 6, Visits: 209
In SQL Error Log its show processing,because 2 days back recovery processing show 98% today it show 99%, tomorrow i will post some of error log on post for your reference.

But cause of databases in recovery mode. SQL Agent has been disabled, other databases backup jobs not performed from last 6 days.

But Thanks for the support dear. I like your prompt reply too.

Thanks Dude...
If i want to direct Chat with you please send the details too on my email ID parminder.parmindersingh@gmail.com.
Post #1417824
Posted Friday, February 8, 2013 10:42 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 40,385, Visits: 36,829
If it's progressing and time's not of the essence (which it apparently isn't), just wait.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1417832
Posted Tuesday, February 12, 2013 1:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 30, 2014 5:23 AM
Points: 6, Visits: 209
Hello Sir,

FYI, Below SQL error log.

2013-02-12 13:26:51.05 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 187 ms, user 12448 ms. Process Utilization 0%. System Idle 98%. Interval: 849570342 ms.
2013-02-12 13:27:51.14 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 187 ms, user 12448 ms. Process Utilization 0%. System Idle 98%. Interval: 849630433 ms.
2013-02-12 13:28:51.23 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 187 ms, user 12448 ms. Process Utilization 0%. System Idle 98%. Interval: 849690524 ms.
2013-02-12 13:29:51.32 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 187 ms, user 12448 ms. Process Utilization 0%. System Idle 98%. Interval: 849750615 ms.
2013-02-12 13:30:51.41 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 187 ms, user 12448 ms. Process Utilization 0%. System Idle 98%. Interval: 849810706 ms.
2013-02-12 13:31:51.50 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 187 ms, user 12448 ms. Process Utilization 0%. System Idle 98%. Interval: 849870796 ms.
2013-02-12 13:32:51.59 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 187 ms, user 12448 ms. Process Utilization 0%. System Idle 98%. Interval: 849930887 ms.
2013-02-12 13:33:51.68 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 187 ms, user 12448 ms. Process Utilization 0%. System Idle 98%. Interval: 849990978 ms.
2013-02-12 13:34:51.77 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 187 ms, user 12448 ms. Process Utilization 0%. System Idle 98%. Interval: 850051069 ms.
2013-02-12 13:35:51.86 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 187 ms, user 12448 ms. Process Utilization 0%. System Idle 98%. Interval: 850111160 ms.
2013-02-12 13:36:51.95 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 187 ms, user 12448 ms. Process Utilization 0%. System Idle 98%. Interval: 850171251 ms.
2013-02-12 13:37:52.05 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 187 ms, user 12448 ms. Process Utilization 0%. System Idle 98%. Interval: 850231341 ms.
2013-02-12 13:38:52.14 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 187 ms, user 12448 ms. Process Utilization 0%. System Idle 98%. Interval: 850291432 ms.

Please help me out.
Post #1418781
Posted Tuesday, February 12, 2013 1:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 30, 2014 5:23 AM
Points: 6, Visits: 209
Hello Gila,

If you required any other information please let me know will provide for the same relevent for this error.
Post #1418782
Posted Tuesday, February 12, 2013 1:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 40,385, Visits: 36,829
My previous advice stands. If no work is being done (and since you didn't post the recovery messages I can't tell), restart SQL. Otherwise wait. Or open a case with MS support.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1418788
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse