White Papers

42 BP1032 | Best Practices and Sizing Guidelines for Transaction Processing Applications with Microsoft SQL Server 2012
using EqualLogic PS Series Storage
most preferred recovery model is FULL to minimize downtime and data loss. Microsoft’s
recommendations for the transaction log are:
• Place the log and the data files into separate volumes to avoid both random and sequential I/O
going to the same volume.
• Set the original size of the transaction log to a reasonable size to avoid constant activation of the
auto grow feature, which creates new virtual files and stops logging activity as space is added.
• Set the auto grow percent to a reasonable but small enough size to avoid frequent activation of the
auto grow feature and to prevent stopping the log activity for too long a duration
• Use manual shrinking rather than automatic shrinking.
7.4.7 Tempdb file growth
The tempdb database is a global resource that holds all the temporary tables and stored procedures for all
users connected to the system. The tempdb database is recreated every time SQL Server starts so the
system starts with a clean copy of the database. Determining the appropriate size for tempdb in a
production environment depends on many factors like the existing workload and the SQL Server features
that are used. Microsoft recommends the following:
• Set the recovery model of tempdb to SIMPLE. This model automatically reclaims log space to keep
space requirements small.
• Pre-allocate space for all tempdb files by setting the file size to a value large enough to
accommodate the typical workload in the environment. This prevents tempdb from expanding too
frequently, which can affect performance. The tempdb database should be set to auto grow to
increase disk space for unplanned exceptions.
• Set the file growth increment to a reasonable size to avoid the tempdb database files from growing
by too small a value. Microsoft recommends the following general guidelines for setting the
FILEGROWTH increment for tempdb files.
Tempdb file size File growth increment
0 to 100 MB 10 MB
100 to 200 MB
20 MB
200 MB or more
10%