THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

Quick Tip - Speed a Slow Restore from the Transaction Log

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.

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

Published Wednesday, November 14, 2012 10:59 AM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement