SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database in Recovery mode from last 7 days.


Database in Recovery mode from last 7 days.

Author
Message
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14406 Visits: 12214
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

hemanth.damecharla
hemanth.damecharla
Mr or Mrs. 500
Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)

Group: General Forum Members
Points: 504 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 Smooooth ~Hemanth
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88288 Visits: 45277
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, MVP, M.Sc (Comp Sci)
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


parminder.parmindersingh
parminder.parmindersingh
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 250
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88288 Visits: 45277
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

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


parminder.parmindersingh
parminder.parmindersingh
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 250
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88288 Visits: 45277
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, MVP, M.Sc (Comp Sci)
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


parminder.parmindersingh
parminder.parmindersingh
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 250
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.
parminder.parmindersingh
parminder.parmindersingh
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 250
Hello Gila,

If you required any other information please let me know will provide for the same relevent for this error.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88288 Visits: 45277
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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search