Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

If I'm failing over my server due to a OS patch, are in-process transactions allowed to finish before stopping the SQL service?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

No. Transaction state is "incomplete" or "uncommitted"

When the SQL Server Instance starts, it goes through recovery to redo completed/undo uncommitted transactions

All work is deferred to start up because the reasons for SQL Server shutdown can be many and varied. This isn't known at start up: so redo/undo always happens

share|improve this answer
    
So all transactions in the buffer pool since the last checkpoint are lost, correct? I would have thought that sql would run a checkpoint before shutting off. –  Sam Jul 21 '11 at 21:12
    
A Checkpoint isn't a commit: it flushes dirty pages to disk –  gbn Jul 22 '11 at 6:26

Either using cluster administrator to manually failover or having a cluster failure it's all the same. As the databases are brought online on the new node they will go through a recovery where completed transactions are rolled forward and incomplete transactions are rolled back.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.