Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Quick Tip - Speed a Slow Restore from the Transaction Log
During some restore operations on Microsoft SQL Server, the transaction log redo step might be taking an unusually long time. Depending somewhat on the version and edition of SQL Server you've installed, you may be able to increase performance by tinkering with the readahead performance for the redo operations.
Kevin Kline, SolarWinds
November 14, 2012
1 Min Read
Here's a quick tip for you:
During some restore operations on Microsoft SQL Server, the transaction log redo step might be taking an unusually long time. Depending somewhat on the version and edition of SQL Server you've installed, you may be able to increase performance by tinkering with the readahead performance for the redo operations. To do this, you should use the MAXTRANSFERSIZE parameter of the RESTORE statement. For example, if you set MAXTRANSFERSIZE=1048576, it'll use 1MB buffers.
If you change the MAXTRANSFERSIZE, keep an eye on the PerfMon objects for Buffer Manager and Readahead IO. You may also wish to keep an eye on LOGBUFFER wait stats.
Related: SQL Server Database Corruption, Part X: Page level Restore Operations
I'd love to hear your feedback. Have you tried this technique? Did it work as advertised? Did it require some changes to work on a specific version or edition?
Many thanks,
-Kev
About the Author
You May Also Like
