Expected or Unexpected data file growth – how to manage?

As the business is growing, the database needs to grow at the same time. It is common to pre-size the data file beforehand based on various recorded activities. Usually the DBA manually increases the data file size on a regular basis in a maintenance window which reduces the dependency on SQL Server auto growth.

Although “Auto Growth” is reliable and a desirable mechanism, however a large growth causes performance issues. While data file growth kicks in, the target database will be locked during the expansion and the end-users suffer from time-out and inaccessibility issues. The database growth in the middle of business hour is not expected and not desirable. How do you manage a sudden or unexpected data file growth?

Database growth support:

There are two ways to manage database growth:

<!--[if !supportLists]-->(a)   <!--[endif]-->Traditional Path: Proactively increase the database size, or

<!--[if !supportLists]-->(b)   <!--[endif]-->SQL Server Auto Growth: Depends on the SQL Server auto growth mechanism.

The following are a few scenarios where database growth is required in order to support specific business activities:

<!--[if !supportLists]-->(a)   <!--[endif]-->Creating a large size database.

<!--[if !supportLists]-->(b)   <!--[endif]-->Restoring a backup in a staging server.

<!--[if !supportLists]-->(c)    <!--[endif]-->Performing massive ETL process.

<!--[if !supportLists]-->(d)   <!--[endif]-->Migrating data from one file to another file group.

<!--[if !supportLists]-->(e)   <!--[endif]-->DML operations.

<!--[if !supportLists]-->(f)     <!--[endif]-->ALTER the Database or adding file.

Intelligent way of managing:

Starting from Windows 2003, Microsoft introduced a new feature which is known as “instant file initialization”. This is a Windows feature which can be applied to the SQL Server Service account in the “local security policy”. This OS feature can be utilized in SQL 2005 and upper version.

Let’s explain what exactly it is. Say for example if you want to create a database of 10GB in size. When you execute CREATE database SQL Server will create the MDF file of 10GB and fills the empty space with zero (zeroing out).

But if the “instant file initialization” right is enabled with SQL Server then the data files will be initialized instantaneously and without filling any empty spaces with zeros.

Keep in mind that “instant file initialization” feature does not work on log file.

Enabling “instant file initialization”:

To take advantage of this feature, the “Perform volume maintenance tasks” windows right must be assigned to SQL Server Service Account. You can follow the below steps to assign the “Perform volume maintenance tasks”:

Start ->

  Run… ->type -  gpedit.msc

     Computer Configuration ->

       Windows Settings ->

          Security Settings ->

             Local Policies ->

               User Rights Assignment

Once the right is assigned, restart the SQL Server Service to take effect.

Figure: Local group policy editor:

<!--[if !vml]--><!--[endif]-->

ERRORLOG and “instant file initialization”:

The Trace flag 3004 can be used to see the information in the SQL Server errorlog regarding instant file initialization. “Trace flag 3004” only shows information about backups and file creations and “Trace flag 3605” redirects the output to the SQL errorlog.

dbcc traceon(3605, 3004, -1)

dbcc tracestatus

Please note that the Trace flag 1806 is used to disable instant file initialization.

Good practices:

Pre-allocating data file size is a traditional way to control database growth and it was mostly used in the SQL 2000 era. However as SQL Server now supports the “instant file initialization” feature, it eliminates manual growth management activity. Therefore, considering the following a good way to handle database growth:

<!--[if !supportLists]-->(a)   <!--[endif]-->Assign “Perform volume maintenance tasks” to SQL Server service account.

<!--[if !supportLists]-->(b)   <!--[endif]-->Make sure Trace flag 1805 is not enabled.

<!--[if !supportLists]-->(c)    <!--[endif]-->Try not to use percentage based data file growth, such as 10%.

<!--[if !supportLists]-->(d)   <!--[endif]-->Use smaller data file growth per increment, such as 100MB per increment.

<!--[if !supportLists]-->(e)   <!--[endif]-->Make sure the log file is reasonably pre-sized.

<!--[if !supportLists]-->(f)     <!--[endif]-->Do not create multiple transaction log files.

Reviewing “Zeroing Out” behavior:

Without the “Perform volume maintenance tasks” right, SQL Server will fill the blank spaces with zero and this behavior can be found in the SQL Server Errorlog when 3605 and 3004 trace were enabled as stated above. Following is a screenshot:

<!--[if !vml]--><!--[endif]-->

Reference:

Database File Initialization

http://msdn.microsoft.com/en-us/library/ms175935(v=sql.90).aspx