This is a great question that I got a while back during a web cast that I did during the 24 Hours of PASS. Most storage arrays, at least the better quality ones, have a battery within them which will keep a portion of the array online in the event of a power outage. While the array won’t be available for use, this will give the array enough time to safely take its write cache from memory and write it down to the disk, then gracefully shutdown.
In the case of the transaction log, every write into the log is put down to disk immediately, and then written into the database file. SQL tells Windows to do the write, and Windows tells the disk to do the write. When the write is written to the write cache the disk tells Windows that the write has been completed, and Windows passes that information to SQL which tells SQL that the write to the log is done, and that it can now write to the database files as well. If the power fails in the middle of this, after the transaction has been committed to the log, but before it has been committed to the database the data still has been written to the disk. When the array sees that the power has gone out it’ll write the write cache to the disk, then power down.
When the power comes back up the array will load up the write cache back into memory, and flush the write cache down to where it needs to be written (in the similar way that SQL does a roll forward when it restarts). Once the writes have been completed the array will allow the hosts (the servers) to see the LUNs and the SQL Server can then fire up and do its normal roll back and roll forward of the data within the transaction log.
Hopefully this helps fix some confusion.
Denny
P.S. Yes I am well aware that I have greatly over simplified the process of how SQL writes to the log and the database, but that isn’t the point of the article. I’m sure that Paul has some great articles on the internals of how this works over on his blog.
