views:

197

answers:

1

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?

+2  A: 

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.

Remus Rusanu