Database Reference
In-Depth Information
Note
If you are creating a database to support SAP, we recommend you review
the following link with regard to transaction log sizing in addition to SAP
Note 1671126:
virtual-log-files-vlfs-can-cause-slow-database-recovery.aspx
.
Caution
There is a bug when growing log files by multiples of exactly 4GB that affects
SQL Server 2012. If you attempt to grow the log by a multiple of 4GB, the first
attempt will fail to extend the file by the amount specified (you might see 1MB
added), but will create more VLFs. The second or subsequent attempt will
succeed in growing the file by the specified amount. This bug is fixed in SQL
Server 2012 SP1. As a workaround, if you are still using SQL Server 2012, you
should grow in increments of 4,000MB or 8,000MB rather than 4GB or 8GB. See
of-4gb/
.
Even if your database were relatively small, we would recommend that you start with a
4GB or 8GB (4,000MB or 8,000MB) transaction log file size. You should proactively
and manually manage the size of your transaction log. Proactive management will avoid
Auto Grow kicking in during production periods, which will impact performance. This
is especially important when considering the transaction log will be growing at 4GB or
8GB at a time and having all those blocks zeroed out. However, just as with data files
and Temp DB files, you should have Auto Grow enabled as a safety net and set it to
either 4GB or 8GB, depending on the growth size you have selected.
Instant File Initialization
When a database file is created or extended, SQL Server will by default zero out the
newly created file space. This will cause performance to degrade if it occurs during
periods of intense database write activity, which is most likely if database files are not
proactively managed and Auto Grow is extending the files. There is also the length of
time required to write zeros to all the new blocks during which access to the file is
blocked, as mentioned previously. To greatly improve the speed and reduce
performance impacts of file growth operations, you can configure SQL Server to
instantly initialize the database files without zeroing them out.
To allow your database instance to leverage Instant File Initialization (IFI), you need to
add the SQL Server Service account to the Perform Volume Maintenance Tasks security
