Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
laddu
Constraint Violating Yak Guru
332 Posts |
 Posted - 2011-11-30 : 21:07:12
|
| Hi,We had a power outage in datacenter(hosting company) on 11/24 . Our production server is cluster active/passive, windows SERVER 2008 R2,SQL2008 R2 VERSIONas per windows log- The previous system shutdown at 7:25:06 AM on ?11/?24/?2011 was unexpected.SQL Server logs: SQL server has started in normal at 11:43am and all committed transactions are rolled forwarded,0 transactions are rolled back.I couldnot see any other errors in log. and ran DBCC checkdb also goodMy hosting company is saying they did't recover the database up to point of failure,During automatic recovery process all transaction which were committed would be committed to the database and all transactions which were uncommitted would be rolled back.My question is ...did we lost only uncommitted transactions at time of failure?what if tomorrow client asks some report and he don't see data that he entered at time of failure. Please advice how to proceed.. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-12-01 : 00:15:07
|
| Well yes you would lose uncommitted transactions if there were any at the time of the crash. SQL has to roll these back, no way around this. However, your log indicates you had none rolled back.Rolling back uncommitted transactions is completely normal for crash recovery though. It is nothing to worry about as the transaction hadn't completed, so SQL can't commit it for you as that could leave your data in an inconsistent state. SQL has to protect the consistency of the data.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-01 : 10:12:17
|
| IF uncommitted transactions were rolled back (i.e. notwithstanding that Tara says that none were rolled back) then your client would never have been able to see that data anyway, in reports and the like, so you would be fine on that point anyway.That is provided you are NOT reporting on uncommitted data - e.g. using the WITH NOLOCK hint - which is a very bad idea IMHO, but sadly one that is widespread in its use.If you have WITH NOLOCK in your queries get rid of it! and consider setting the database to READ_COMITTED_SNAPSHOT if the reason you have WITH NOLOCK is because Reads are blocking Writes |
|
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2011-12-01 : 14:53:54
|
| Thanks!Now I understand that uncommitted transactions will be rolled back during recovery.Question: I could see in logs for databases. xx number of transactions are rolled forwarded0 transactions are rolled back (for all databases) --- so these 0 transactions belongs to committed or uncommitted? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-12-01 : 15:06:42
|
| Rolled forward = committed transactions that may or may not have been written to disk, hence must be redoneRolled back = uncommitted transactions that may or may not have been written to disk, hence must be undone--Gail ShawSQL Server MVP |
|
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2011-12-01 : 16:17:26
|
| Sounds Good.Since we have 0 transactions rolled back, which mean we did't lost any data. Great. Thank you all. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-12-01 : 19:37:12
|
| Well even if transactions had to be rolled back because they hadn't been committed doesn't classify as losing data. It never completed, so there was nothing to lose. If the user was in the process of doing something, like saving something, then the application should have been smart enough to tell the user it couldn't be saved as the application would have returned an exception. It's not like the data had been saved and then lost, that's not what a rollback is about.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
|
|
database recovery after power outage