|
| Recent Posts | - July, 2024-7,(1)
- May, 2024-5,(2)
- May, 2023-5,(1)
- February, 2023-2,(1)
- November, 2022-11,(1)
- July, 2022-7,(2)
- March, 2022-3,(1)
- November, 2021-11,(2)
- August, 2021-8,(2)
- July, 2021-7,(2)
- June, 2021-6,(1)
- May, 2021-5,(1)
- March, 2021-3,(1)
- February, 2021-2,(2)
- January, 2021-1,(7)
- December, 2020-12,(3)
- March, 2020-3,(2)
- February, 2020-2,(1)
- December, 2019-12,(2)
- November, 2019-11,(1)
- October, 2019-10,(1)
- September, 2019-9,(1)
- August, 2019-8,(1)
- May, 2019-5,(1)
- April, 2019-4,(2)
- March, 2019-3,(2)
- December, 2018-12,(1)
- November, 2018-11,(4)
- July, 2018-7,(1)
- May, 2018-5,(3)
- April, 2018-4,(2)
- February, 2018-2,(3)
- January, 2018-1,(3)
- November, 2017-11,(2)
- August, 2017-8,(1)
- June, 2017-6,(3)
- May, 2017-5,(5)
- February, 2017-2,(1)
- December, 2016-12,(1)
- October, 2016-10,(2)
- September, 2016-9,(1)
- August, 2016-8,(1)
- July, 2016-7,(1)
- March, 2016-3,(2)
- February, 2016-2,(3)
- December, 2015-12,(5)
- November, 2015-11,(5)
- September, 2015-9,(1)
- August, 2015-8,(2)
- July, 2015-7,(1)
- March, 2015-3,(2)
- February, 2015-2,(1)
- December, 2014-12,(4)
- July, 2014-7,(2)
- June, 2014-6,(2)
- May, 2014-5,(3)
- April, 2014-4,(3)
- March, 2014-3,(1)
- December, 2013-12,(2)
- November, 2013-11,(1)
- July, 2013-7,(1)
- June, 2013-6,(2)
- May, 2013-5,(1)
- March, 2013-3,(3)
- February, 2013-2,(3)
- January, 2013-1,(1)
- December, 2012-12,(3)
- November, 2012-11,(1)
- October, 2012-10,(1)
- September, 2012-9,(1)
- August, 2012-8,(1)
- July, 2012-7,(6)
- June, 2012-6,(1)
- April, 2012-4,(1)
- March, 2012-3,(3)
- February, 2012-2,(3)
- January, 2012-1,(4)
- December, 2011-12,(3)
- October, 2011-10,(3)
- September, 2011-9,(1)
- August, 2011-8,(10)
- July, 2011-7,(2)
- June, 2011-6,(7)
- March, 2011-3,(2)
- February, 2011-2,(3)
- January, 2011-1,(1)
- September, 2010-9,(1)
- August, 2010-8,(2)
- June, 2010-6,(1)
- May, 2010-5,(1)
- April, 2010-4,(3)
- March, 2010-3,(2)
- February, 2010-2,(3)
- January, 2010-1,(1)
- December, 2009-12,(3)
- November, 2009-11,(3)
- October, 2009-10,(2)
- September, 2009-9,(5)
- August, 2009-8,(3)
- July, 2009-7,(9)
- June, 2009-6,(2)
- May, 2009-5,(2)
- April, 2009-4,(9)
- March, 2009-3,(6)
- February, 2009-2,(4)
- January, 2009-1,(10)
- December, 2008-12,(5)
- November, 2008-11,(5)
- October, 2008-10,(13)
- September, 2008-9,(10)
- August, 2008-8,(7)
- July, 2008-7,(8)
- June, 2008-6,(12)
- May, 2008-5,(14)
- April, 2008-4,(12)
- March, 2008-3,(17)
- February, 2008-2,(10)
- January, 2008-1,(16)
- December, 2007-12,(6)
- November, 2007-11,(4)
|
|
|
|
|
SQL Agent will not start when a user database is in recovery !
|
2/6/2008 9:42:45 AM
|
|
|
This is crap, SQLAgent not starting because a user database is in recovery mode, after restarting sql server. Usually recovery / checkpointing of user databases on start-up is really quick, but if one runs into an issue it can take a long long time, this particular user database has been recovering for 4+ hours. Now I have issues with maintenance jobs and reporting services jobs not running because sql agent will not start.
I can understand if msdb or some other system database is in recovery or suspect, but not a damn user database !
SQL Server Error Log:
2008-02-06 09:38:44.07 spid10s Recovery of database 'ClientAssistant_CY' (20) is 27% complete (approximately 38597 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
READ THAT, 38000+ seconds, over 10 HOURS, we can't wait that long !
SQL Agent Error Log:
2008-02-06 08:44:55 - ? [393] Waiting for SQL Server to recover databases...
This is unreal, i'm going to have to de-attach / interrupt the recovery process; i think, but to be sure i placed a support call to Microsoft.
According to Microsoft, this is a new feature of SQL Server 2005. If SQL Server Agent starts and not all the user databases on-line / recovered than sql server agent stays in a starting mode and no jobs run.
The way around this is to "interrupt" the services, so you must stop sql server, which requires finding the process id (pid) and killing it via task manager or kill.exe; rename the mdf and ldf of the database taking the extremely long recovery time and then restart the services, everything should come on-line. Now you can either restore from a backup or re-attach the mdf / ldf and let it start recovering again....
very very bad solution as interrupting sqlservr.exe with a kill of the pid is a high risk operation that could corrupt other databases, so proceed with caution and get a critical support case opened with Microsoft, you do not want to be doing this alone, have them on the phone with you, severity 1.
RESPONSE FROM MICROSOFT (Names removed)>
From: [nameremoved@microsoft.com]
Sent: Friday, February 08, 2008 9:02 AM
To: Horkay, Robert
Cc:
Subject: RE: Case number SRX080206600293 - SQLAgent will not start on a cluser - Open
Hi Bob,
We have decided to file a design change request to see if our development team will build in functionality to SQL Agent so that a registry key, trace flag or some startup parameter can be added to have SQL Agent go ahead and start up while databases are in recovery. If this is approved then a knowledge base article would be published describing how to do this and the caveats involved. Instead of taking SQL Server down to stop recovery we would only have to take SQL Agent offline to add the required parameters to change the code path in SQL Agent and let it start up immediately or at least 5 minutes after starting the service. I will continue to work on the design change request and hopefully it will be approved.
Thank you,
{removed}, MCDBA
Microsoft SQL Server Support Escalation Engineer
SQL Server Escalation Services Team
Product Support Services (PSS) - Charlotte, NC
Hours: M-F 9:00am - 6:00pm Eastern | Phone: {Removed} | Email: {Removed}
|
Blog Home
|
|