TempDB Recovery Model

Issue description

This check determines whether the TempDB recovery model is set properly.

Explanation

SharePoint Server uses SQL Server to store configuration and user data. SQL Server stores data over the various file types. The TempDB system database is a global resource that is available to all users connected to the instance of SQL Server. It is used to hold the following:

  • Temporary user objects that are explicitly created, such as global or local temporary tables, temporary stored procedures, table variables, or cursors.
  • Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
  • Row versions that are generated by data modification transactions in a database that uses read-committed row versioning isolation or snapshot isolation transactions.
  • Row versions that are generated by data modification transactions for features, such as online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

The simple recovery model is required; therefore, tempdb log space is always automatically reclaimed. You cannot backup tempdb.

Solution

Check the recovery model on the tempdb database and set it to Simple, if required. To do so, start SQL Server Management Studio and connect to the SQL server instance which hosts your content databases. In the Object Explorer tree, navigate to Databases > System Databases, select the tempdb database, right-click on it and click Properties. In the Database Properties window, select Options page. Click the Recovery model drop down box and select Simple. Click OK to exit and save the changes.

Additional information

Additional information can be found in the following articles: