Tip for database restore: Don’t get into issues with log file initialization

Whenever we want to restore a database/transaction log backup to a different server from primary server as part of requirements like setting up AG Secondary,  preparing Test/Dev Server databases, we may get into issues of log file initialization taking longer time and longer waits on PAGEIOLATCH_EX.

This can happen particularly when log file grows to large size. Ex: 100 or 200 GB

Tip in this case is shrink the original transaction log file in primary database to much lesser size (Ex: 20 GB) and then take a DIFF/FULL backup depending on the situation and then use that backup for subsequent restore in AG secondary, Dev/Test servers.

Also, if transaction log file could not be shrinked physically despite having free space within the file, please follow the tip in below article. Sometimes, multiple transaction log backups are required on primary server to be able to shrink the log file.

                                                  Physically shrinking SQL Transaction log files

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s