SQL Server TempDB is a system database, automatically created when you install SQL Server.
So what is it used for? Well a few things actually but first I have to tell you that Microsoft didn’t name tempdb because they couldn’t think of a suitable name. It is a temporary database which is re-created every time the SQL Server service is started and at a higher level, it could be considered to be the page file for sql server.
Lets look at when SQL Server TempDB gets used
Global and local temporary tables are created in here. So for example if you write a create table statement starting like this global temporary table:
CREATE TABLE ##Table....
or this local temporary table…
CREATE TABLE #Table....
When you execute the create table script, the temporary table will be created in tempdb.
Other objects created in tempdb would be temporary tables, temporary stored procedures, table variables, cursors and internal objects created by the database engine. These would typically be work tables created to store intermediate result sets for spools or sorting. So you might want to create an index using tempdb and use SORT_IN_TEMPDB in your CREATE INDEX statement to store those result sets. You would typically do this because you want to take the load off the main database files whilst the index is being created and hope to speed up the creation of the index in the process. There are disk space considerations to bear in mind with that though and I’ll discuss that in another post.
Finally, its other function is to maintain multiple versions of rows.
The following features make use of tempdb for row versioning
- Online index operations
- MARS – (Multiple Active Result Sets)
- Snapshot Isolation and Read-Committed Snapshot Isolation
- Triggers
SQL Server TempDB – what can’t you do?
- You can’t drop it
- You can’t back it up
- You can’t change its recovery model from simple
- You can’t persist user created objects in the database without hassle
- You can’t create multiple filegroups, only the PRIMARY filegroup is available
So you now know that it is quite a busy little (sometimes large, very large in fact) database on your SQL Server and you’re probably thinking that if tempdb is so busy, what are the performance implications on your SQL Server instance of a busy tempdb? Well I’m not going to talk about that here but instead I will talk about it here in my post about tempdb best practices
Good Explanation Andy.
Would you be able to explain why it grows and how to reduce tempdb log files without restarting SQL server?
Hi Nicky
Thanks for your comment.
Logging operations happen in tempdb for transaction rollback, although they are minimally logged. For some ways to shrink tempdb check out my post http://dbadiaries.com/how-to-shrink-tempdb
Hi Andy
I read your article.It was well explained.But one question arises that How MARS occupies space in TempDB ?
Please let me clear ….