SQL Server File Expansion Performance

Moving some old notes over to blog posts and I ran across the problem of SQL Server needing to increase file sizes.

Make sure the user running SQL Server also has permission to: Perform Volume Maintenance Tasks

The reason for this is the following:

Data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. Data and log files are first initialized by filling the files with zeros when you perform one of the following operations:

  • Create a database.
  • Add files, log or data, to an existing database.
  • Increase the size of an existing file (including autogrow operations).
  • Restore a database or filegroup.

In SQL Server, data files can be initialized instantaneously. This allows for fast execution of the previously mentioned file operations. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log files cannot be initialized instantaneously.

In order to make use of Instant File Initialization the SQL Server user must be a member of the Perform Volume Maintenance Tasks group.


Leave a Reply

Your email address will not be published. Required fields are marked *