March 26, 2005 at 12:03 pm
My question is :
What would happen to the jobs which are running,while failover occurs?
I have a Active \ Passive SQL Server 2000 EE SP3a with Hotfix MS03-031 on Windows 2003 Enterprise Edition.
When I testesd the Fail over from the Primary node (Node1) to Secondary Node (Node2) there was a backup job running.
The backup job created using using 'database Maintenance plan which backups all the user database. There are 10 databases to backup.
The thing is that when the database1 being backed up the "FAIL OVER" happend and SQL Server failed over to Node2.
When I checked on the 'Job History' there was no entry at all for this job.
But the database backup except to the database2 (i,e during the time fail over happened) all the databases were backed up.
The strange thing is there was no entry on 'Backup job history' at all for the job ran.
There are entries in NT event viewer log.
Can anyone explain on How SQL Server would handle the Jobs which are running while the fail over is happening?
March 28, 2005 at 7:44 am
The same thing that would happen if you shut down SQL and restarted it. The jobs stop executing. I believe you don't have a job history becuase it is written last but you did get the backups that had completed because SQL doesn't delete them.
You must be careful with Maintenance plans and clusters. If your SQL instance fails over with a maintenance plan running on one node (and that node is not rebooted), the maintenance plan can sit out there indefinately as a process (sqlmaint.exe) and hold the port open that SQL was using. If you fail back and that SQLMaint.exe is still out there, you will likely get a TCP bind failure in SQL startup. You must kill these SQLMAint.exe processes or reboot that server to get TCP to work.
March 29, 2005 at 1:13 am
Actually in my case when the fail over happened to the Node 2, the backup job for the user database was running.
What happened was the databse backed up during the time when the actual fail over occurs was aborted. All other database were backed up.
There was no entry on job history. But the backup status were recored on the respective databases, Windows Event viewer and SQL server Error log.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply