Database Reference
In-Depth Information
Temp DB File Sizing
The size of your Temp DB files should be based on the high watermark usage you
estimate for your queries and the overall size of your database. This can be hard to
estimate without knowledge of your workload because different queries will impact
your Temp DB usage in different ways. The best way to determine the appropriate size
will be to monitor Temp DB usage during a proof of concept test, or benchmarking and
baselining activities.
As a starting point, we recommend you consider sizing Temp DB to 1% the size of your
database. Each file would then be equal to Total size of Temp DB divided by the
number of files. For example, if you had a 100GB database with four vCPUs
configured, you would have an initial total Temp DB size of 1GB, and each Temp DB
data file would be 250MB in size. If you see significantly more Temp DB use during
ongoing operations, you should adjust the preset size of your files.
Note
Temp DB files are cleared, resized, and reinitialized each time the
database is restarted. Configuring them to be preset to the high water mark
usage will ensure they are always at the optimal size.
Transaction Log File Sizing
The total size that your database transaction log file should be preset to will primarily
be based on the actual or estimated high water mark of transaction storage required
before the next backup or transaction log truncation. We are assuming for the purposes
of this section that you care about data protection and preventing data loss of your
database and are therefore using the full recovery model. Data loss is a risk when using
the other available recovery models.
Tip
If you care about data protection and preventing data loss, use full recovery
mode.
If you are doing daily backups, you will need to ensure that your log file is sufficiently
sized to allow up to at least a day's worth of transactions. This will allow you to
recover back to the point in time your database goes down by using the last backup and
replaying the transaction logs. In some large database systems, you will need to back up
the transaction logs much more frequently than every day.
Caution
