SQL Server Performance Forum – Threads Archive
Transaction Failure – What to expect
I am unable to find any info on microsoft web site regarding what to expect when a failover occurs? Do running transaction just crash? Currently I have a job which crashes when a failover occurs. If someone can shed some lite on what to expect that would be great. I would carry on my search and if I do find something myself I will update the post. ThanksCan you post the relevant information about that job, such as error messages etc.
Also look into event viewer, SQL server error log about why failover is occuring. Is it because of hardware or any malfunction of network or software itself. If running a long transaction is crashing SQL then make sure it is equipped with latest service pack levels to avoid the fallout. Setting a timeout on long running queries will also increase the level of efficiency for the larger user community. Long running queries or application requests from clients can degrade performance of a SQL Server by utilizing resources and memory. If a server that is running SQL Server experiences an abrupt shutdown, it may take a database a long time to recover at start up if all the following conditions are true:
• The shutdown occurs shortly after an extremely large increase in transaction rate, and checkpoint has not run since the transaction rate increased dramatically.
• When SQL Server unexpectedly shut down, there were several active transactions (more than several thousand).
• SQL Server was not shut down "cleanly." Some examples of this type of shutdown include a SHUTDOWN WITH NOWAIT command, a sudden hardware failure, or a power outage that affected the server.
• The sp_configure stored procedure setting recovery interval is set to a non-default value (the default value is 0).
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>