DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

Home All Articles SQL Server TempDb – what’s it for?

SQL Server TempDb – what’s it for?

Posted on December 13, 2011 Written by Andy Hayes 3 Comments

SQL Server TempDB is a system database, automatically created when you install SQL Server.

sql server tempdb

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

Related Posts:

  • How to Setup MySQL Master Master ReplicationHow to Setup MySQL Master Master Replication
  • How to Take a Backup of a Table in MySQLHow to Take a Backup of a Table in MySQL
  • How to set up MySQL Replication TutorialHow to set up MySQL Replication Tutorial
Share this...
Share on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin
Pin on Pinterest
Pinterest
Share on Reddit
Reddit
Share on StumbleUpon
StumbleUpon
Share on Tumblr
Tumblr
Buffer this page
Buffer

Filed Under: All Articles, SQL Server Administration Tagged With: tempdb

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Comments

  1. Nicky says

    June 13, 2013 at 9:46 am

    Good Explanation Andy.

    Would you be able to explain why it grows and how to reduce tempdb log files without restarting SQL server?

    Reply
    • Andy Hayes says

      June 16, 2013 at 9:03 am

      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

      Reply
  2. Sandeep says

    August 26, 2013 at 1:22 pm

    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 ….

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

CAPTCHA
Refresh

*

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • How to move tempdb
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright © ‘2019’ DBA Diaries built on the Genesis Framework

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close