June 15, 2009 at 1:50 pm
Recently, we ran a 24-hr disaster recovery exercise, where we failed over our SQL Server 2005 database from the principal to the mirrored instance. The application(s) were able to access the data in the new location; we failed back the next day and there was no data loss. We were all happy about that.....
HOWEVER....., one thing we discovered is that after the "fail over" and also the "fail back", the jobs inside SQL Agent did not start. The time would pass their "next run time", and then jobs would just sit. They could be double-clicked and run manually, but they would not launch from the SQL Agent schedule. Stopping / Restarting the SQL Agent Service got the jobs to start again on their normal schedule.
Anyone else have this problem? If so, what did you do? We would prefer to not have to stop/restart the SQL Agent service as part of our failover procedures. Thanks in advance for any advice offered.
Regards,
Craig Cortez
DBMS Version: Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Replication type: DB Mirroring
June 17, 2009 at 3:03 am
I've also experienced the problem. Not with a failover, but on a system where the time was changed.
In my experience there are only two solutions:
- stop and start the SQL Server Agent (as you also found)
- start each job manually once (after that it schedules in a future time again and runs normally)
I don't see any way of solving this without manual action. Sorry.
June 17, 2009 at 8:08 am
DDonck..... Thank you for the response.
June 17, 2009 at 8:18 am
did you see any errros in the SQL Agent Log or SQL Server Error Log. ?
Any resource errors or permissions Errors. ?
Are you Running the SQL Agent under a domain account or local Account.?
Maninder
www.dbanation.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply