Sqlserver-dba.com

Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server faster restores with instant file initialisation

20 January,2011 by Jack Vamvas

Instant File Initialization can make SQL Server Restores much faster  .

A typical SQL Server Restore phase includes the following phases:

a) Copy data,logs,index from backup to the database files

b) Take logged transactions and apply to data for recovery point rollforward.

Next step  to undo phase – to deal with uncommitted transactions.

c) Undo takes uncommitted transactions and rolls back (which depending on the transaction can take awhile to rollback)– then making the database available for use

 But , previous to these steps and in the process of restoring – the initialisation process of the data\log files fills the previously deleted files with zeroes .This is a OS security feature and is designed to avoid revealing information from previously deleted files .

In terms of SQL Server  for certain situations (create database, restore database – when in conjunction with CREATE DATABASE ,increase size of a file, add files to a db)  the OS will attempt to zero out .

The file initialisation process delays the restore process - because SQL Server initialises files by filling the space with zeroes. Just to  clarify – this is in situations where a CREATE DATABASE is required as part of the RESTORE process

Is there a quicker way of restoring?

 In certain situations ,yes, by applying Instant  file initialisation .skips the the zeroing out of the data on disk – i,e it does not overwrite the existing data . In effect the OS allocates the space.

How to set up instant file initialization?

Prereqs: sql server 2005 , either Windows XP , widnows 2003 or later

1) Run the MSSQLSERVER service account with a Windows Acct

2)Assign SE_MANAGE_VOLUME_NAME special privilege to that Windows Account

Do this by :

On cmd line : gpedit.msc

Then : Windows Settings | Security settings | Local Policies | User Rights Assignments

Open “Perform volume maintenance tasks”   and and add the SQL Server service acct

3) Recycle SQL Server instance

 Note :

1) There are security consideration to analyse. The risks are minimal – but exist

2) The benefits derived from instant file initialization – only relate to the data files not the log files. You can test this out by restoring different databases with different size data and log files

3) In production environments  I use this in some situations – not all restores.

Read Also

SQL Server – Restore Database Estimated Finish Time

SQL Server - Display restore history for a single database


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Enjoy this post? Enter your email address for updates on new posts:

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer