CODEWITHSUNDEEP
sql-servertempdbrecoverymodel
ctrlShiftBryan
ctrlShiftBryan

Reputation: 27740

SQL Server: Recovery model effect on TempDB

Does the recovery model of a database effect the size of the tempdb.mdf?

We have a database which involves a lot of processing and bulk inserts. We are having problems with the tempdb file growing to extremely large sizes (over 70 gb). The database in question is set to Full Recovery. Will changing it to Simple Recovery(on the database with all the transactions not the tempdb) prevent it from using the tempdb on these large inserts and bulk loads?

Upvotes: 3

Views: 6367

Answers (2)

gaiusgracchus
gaiusgracchus

Reputation: 11

tempdb Size and Placement Recommendations

To achieve optimal tempdb performance, we recommend the following configuration for tempdb in a production environment:

Set the recovery model of tempdb to SIMPLE. This model automatically reclaims log space to keep space requirements small.

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

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294177

The recovery mode of the database doesn't affect its use of tempdb. The tempdb usage is most likely from the 'processing' part: static cursors, temp tables and table variables, sort operations and other worktable backed query operators, large variables and parameters.

The bulk insert part (ie. the part which would be affected by the recovery mode) has no tempdb impact.

Upvotes: 3

Related Questions