VLDB and Zeroing out

30 January,2014 by Tom Collins

Awhile ago I wrote a post SQL Server faster restores with instant file initialisation , which goes through the basics of instant file initialization  - the characteristics and how to set it up for your SQL Server databases.

Although the advice applies as a general rule, it can be tricky to incorporate with auto growth setting on VLDBs .

Transaction Logs on VLDBs . Before you implement anything in Production , test out in the Development environments. The last thing you need is a nasty surprise – while Production locks up and users complain.

For VLDB Transaction Logs , my general rule is to start with 8 GB, and set autogrowth at 8 GB. These are general guidelines.  The real key with implementing successfully is to predict the autogrowth – because that is a point where the zero-filling occurs and can have a severe performance impact

For VLDB Data files – they are instant initialized. Note: Only if instant initialization is enabled! These gives you loads more scope on autogrowth – meaning you can set much higher numbers.

Keep in mind if instant file initialization is not enabled – both data and transaction log files are zeroed out. In this situation – same rules apply for autogrowth levels – test in the lower environments.

Read More

Database autogrow and slow database recovery ... - SQL Server DBA

SQL Server faster restores with instant file initialisation

Storage Checklist FAQ - SQL Server DBA

SQL LOG FILE AUTOGROW performance ... - SQL Server DBA

Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

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

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.


Post a comment on VLDB and Zeroing out

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