Data in cache transaction

Question

Hello,

Can someone explain (that even I can understand :-) what exactly happens... <o:p></o:p>

As of my knowledge data is first written to SQL the cache and after the checkpoint it is written to the transaction log and after to the data file.<o:p></o:p>

1. If data is in the SQL cache and there is an unexpected shutdown is the data the has not yet been written to the transaction log LOST?<o:p></o:p>

2. And if using alwayson AG`s is there data lost?<o:p></o:p>

Thank you

Daniel

Answer 1

The process is called "write-ahead logging", and it's explained here 

Write-Ahead Transaction Log

http://msdn.microsoft.com/en-us/library/ms186259(v=sql.105).aspx

Davdi

---

David http://blogs.msdn.com/b/dbrowne/

Answer 2

Ok thanks, Can you still tell me...

1. If data is in the SQL cache and there is an unexpected shutdown is the data the has not yet been written to the transaction log LOST?<o:p></o:p>

2. And if using alwayson AG`s is there data lost?<o:p></o:p>

Answer 3

The data is written to the transaction log _before_ the transaction is committed.  And in case of an unexpected shutdown the changes will read from the transaction log be replayed into the database on startup.  Only data written in the cache by uncommitted transactions could be lost on unexpected shutdown, and even if it isn't it will be rolled back on restart

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 4

Hi there,

When a client starts a transaction SQL server receives instruction of begin transaction. Then it will start writing to the log.

That information packets will be collected from a memory location what you call as cache. Now if power loss happens after restart you will lose whatever in the cache. Unless your server admin have set up to store this cache.

SQL server will start writing the information to the log file after receiving the begin instruction. After writing certain amount of information it will create a check point. Once a check point is created you can assume that this data is somewhat safe. If power failure happens at this stage you can restore the data up to this point.

Any transactions after this point will rollback. For more information check these links.

http://en.wikipedia.org/wiki/Write-ahead_logging

http://msdn.microsoft.com/en-us/library/ms186259%28v=sql.105%29.aspx

 

2. And if using alwayson AG`s is there data lost?<o:p></o:p>

AG does not support automatic failover like FCI. So it has to be manual. In future it may support automatic.

Depending on your sync method you adopted you may or may not loose data in case of power failure. Your Disaster Recovery rules will apply.

I hope this helps you.

Thanks

Kumar

Answer 5

There is no data loss of committed transactions on unexpected shutdown.  That's the whole point of write-ahead logging.

AlwaysOn AG's certainly support synchronous mode and automatic failover, see:

Failover and Failover Modes (AlwaysOn Availability Groups)

and regardless your committed data is safe from power failures. 

David

---

David http://blogs.msdn.com/b/dbrowne/