All geek questions in one place

SQL Server: Impact of Recovery Model on TempDB

Does the database recovery model create the size tempdb.mdf?

We have a database that includes a lot of processing and bulk inserts. We are having problems with the fact that the tempdb file grows to extremely large sizes (more than 70 GB). A full recovery has been installed for this database. Will this change to a simple recovery (in a database with all transactions, not tempdb) to prevent tempdb from being used for these large inserts and bulk downloads?

+3
source share
2 answers

Database recovery mode does not affect the use of tempdb. Using tempdb is most likely from the "processing" part: static cursors, temporary tables and variable tables, sorting operations and other query operators that support the working table, large variables and parameters.

The bulk of the insert (that is, the part that will be affected by recovery mode) has no tempdb effect.

+3
source

tempdb Size and placement guidelines

For optimal tempdb performance, we recommend the following configuration for tempdb in production:

Install tempdb recovery model in SIMPLE. This model automatically restores log space to ensure minimal space requirements.

http://msdn.microsoft.com/en-us/library/ms175527.aspx

+1

Source: https://habr.com/ru/post/1730081/

More articles:


All Articles