You can think of tempdb as the “scratch” database for SQL Server; it’s a temporary data store used by both applications and internal operations. It is very similar to other databases in that it has a data file and a log file and can be found in SQL Server Management Studio, but it does have some unique characteristics that affect how you use and manage it.
The first fact to note is that everyone using an instance shares the same tempdb; you cannot have any more than one within an instance of SQL Server but you can get detailed information about who is doing what in tempdb using DMVs.
Tempdb features and attributes
The following features and attributes should be considered when learning about, using, tuning, and troubleshooting tempdb:
Nothing stored in tempdb persists after a restart because tempdb is recreated every time SQL Server starts. This also has implications for the recovery of tempdb — namely, it doesn’t need to be done. See the following section.
- Tempdb is always set to “Simple” recovery mode, which means that transaction log records for committed transactions are marked for reuse after every checkpoint. This means you don’t need to back up the transaction log for tempdb, and in fact, you can’t back up tempdb at all.
- Tempdb can only have one filegroup (the PRIMARY filegroup); you can’t add more.
- Tempdb is used to store three types of objects: user objects, internal objects, and the version store.
Tempdb has fewer logging operations
When you change a value in a normal database, both the old value and the new value are stored in the transaction log. The old value is used in case you need to rollback the transaction that made the change (undo), and the new value is used to roll-forward the change during recovery (redo) if it hadn’t made it to the data file before the restart.
You still need to be able to undo a change in tempdb but you’ll never need to redo the change as everything is thrown away on restart. Therefore, tempdb doesn’t store the redo information, which can result in significant performance gains when making many changes to big columns compared to a user database.