June 27, 2011 at 3:43 am
Hi Folks,
in one instance there is job is to restart SQL services after completion of that job, SQL Agent was disabled and one database is in Inrecovery mode.
So, Please kindly help to fix this issue..
TIA..
June 27, 2011 at 4:17 am
Your problem is not clear. Please be ellaborate to understand your problem.
---------------------------------------------------
Thanks,
Satheesh.
June 27, 2011 at 4:19 am
My problem is one database gone to Inrecovery mode.
so how much time it takes to come into recovery mode its size is 100 gb above.
June 27, 2011 at 4:52 am
Recovery means it is checking its integrity and rolling back/forward transactions that were open when it was shut down. There's no way to know for sure how long it will take. Best thing you can do is wait until it finishes.
Why was the server restarted?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 27, 2011 at 4:53 am
check whether do you have performed any dml operation (update, insert or delete) on the database and it is still running. Check you have enough free space on disk drive for both Mdf and ldf files to grow.
---------------------------------------------------
Thanks,
Satheesh.
June 27, 2011 at 5:47 am
Before services restarting, database backup happened, and there is a approx. 90gb of free space on the disk.
Is there any alternate to fix this issue. am in a critical situation now.
June 27, 2011 at 6:14 am
You had restarted the service while some operation is happening and now the process should be in roll back or roll forward mode. So sql server initiated the auto recovery mode and hence giving the status as In recovery. Once the operation is completed the db will come to recovery/normal mode. This might take more time but it will come to recovery mode.
Or else you can stop the service and move or delete the data and log file of the particular database and restore the backup in the same name might help.
But it is better to allow the sql server to complete its operation and come to the recovery mode.
---------------------------------------------------
Thanks,
Satheesh.
June 28, 2011 at 12:33 am
Hi Satish,
Thanks for your kind response..
After sometime database recovered from In Recovery mode...
Thank you one and all....
🙂
Regards,
-----------------
Sreenath....
June 28, 2011 at 12:41 am
Hi Srinath,
Happy to know that your problem got resolved. But you should consider this as a warning. Never automate such thing like restarting the service until unless it is really really required and you had tested your system completly to handle such process. And also if at all you are restarting the service manually ensure that there is no running process at the time of restart.
Good day.
---------------------------------------------------
Thanks,
Satheesh.
June 28, 2011 at 2:56 am
find the recovery process is running in the backupground,you can get the percentage of recovered database with the help of SQL Logs
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 29, 2011 at 12:11 am
Hi Sateesh,
I want to know why this happens?
Restart job is a scheduled job by windows team, and that job as well as SQL server is working fine from past two years.
And why SQL Server database gone to IN Recovery mode. and moreover i didn't get any progress of recovering status of that database in Error logs.
Now, is there any permanent resolution to avoid this issue....????
TIA..
June 29, 2011 at 12:13 am
Adding to above post, there are more than 20 Jobs which runs on 24/7 basis and they are running absolutely fine from past 2 years.
And after restarting the services only SQL SERVER service only starts and SQL Agent service not started at the same time, and when the SQL Agent service starts then the Database comes in to recovered mode.
issue was fixed temporarily, plz suggest me for a permanent resolution...
June 29, 2011 at 5:28 am
Without more information, it's hard to know. OK, the database was in recovering mode. Fine. Were there transactions being run against it when it shut down? What was the status of those transactions? Simply starting SQL Agent is not going to put databases into recovery mode, so what else is Agent doing or what other commands are associated with the Agent start up command you issued? Do you have errors in the error log, either SQL Server or SQL Agent logs?
There are lots of things going on in SQL Server all the time. It's constantly in flux. Just because a command works for 2 years doesn't mean it will work without flaw for the next two because other things can affect it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply