Interesting things about Tempdb database

While I was preparing for my Tempdb presentation, I learned many interesting facts about Tempdb. Thanks so much Bob Ward (t|g) for providing me with the resources to prepare for my presentation. Bob Ward has presented an amazing 3 hour session about Tempdb for the PASS Summit couple of years ago. This information is invaluable.

Why I am blogging about the interesting things about Tempdb then?

There were lot of things about Tempdb that surprised me as I learn about tempdb from Bob Ward presentation. Here is the link to the complete session. I thought of writing a blog post about these interesting and cool things so you all can know about them as well.

Source: https://www.pexels.com/photo/green-pineapple-fruit-with-brown-framed-sunglasses-beside-yellow-surface-1161547/

Do you know?

  1. Each time you restart SQL Server, tempdb gets created from the model database and acquire all the properties from the model database including any objects you created in model database? First the primary data file gets created from model database copying one extent at a time from model database to tempdb database. Later transactional log file gets generated. After that, secondary database files will be created for tempdb. Until the tempdb is created, the model and the tempdb databases will be locked and no one can connect to it though user connections are allowed at this point of time as master database will be first opened before tempdb.
  2. You can create tempdb very quickly by using the -f startup parameter but still retain the tempdb original sizes. This will help when you are having problems starting tempdb.
  3. If you enabled Instant file initialization, creating data files will be quick because we do not have to zero out the database files and immediately start using these files. Instant file initialization doesn’t support zeroing out of the transaction log files. If you would like to increase the size of the log file later, then we have to zero out the entire log. When you restart the tempdb and if you already have the big transactional log file available, we do not have to zero out the entire log. We just have to zero some of the records out the first virtual log files.
  4. When a temp table is dropped, the meta data of the temp table is cached. Temp table data is truncated keeping the temp table meta data for reuse.
  5. You cannot cache the temp table when it is not associated with stored procedure, functions and triggers. Temp table caching is not supported for ad-hoc queries.
  6. If multiple people are using the same stored procedure having the same temp table at the same time, multiple temp tables can get created and they can be reused as all these tables are cached.
  7. Tempdb do the minimum logging. We do very less logging because we do not keep the data persisted when we restart the SQL Server. We create tempdb each time we restart with SQL Server. We don’t need a crash recovery for this database. We need transaction log for rollbacks. There is no redo for tempdb because the objects we create are not persisted on restart.
  8. When the Checkpoint process runs on SQL Server automatically, it skips the checkpoint on tempdb. When you manually run a checkpoint, then the checkpoint process happens on tempdb which can take some time to run because we do not do the checkpoint by regular automatic process. Checkpoint process will truncate the t-log of tempdb.
  9. Table variables are stored in tempdb and there is no rollback support for table variables.
  10. Index sorting is done in the user databases and not in tempdb unless you use SORT_IN_TEMPDB option. If the sort is not enough to fit in memory, it will spill to disk and use temporary space in tempdb.
  11. You are enabling the version store in tempdb if you are using snapshot isolation, read committed snapshot isolation, online index rebuild and triggers.
  12. If you try to change the size of the tempdb file using management studio, altering of the file to change the size doesn’t happen. SHRINKFILE happens behind the scenes. When you manually alter the file using the script, the alter database and changing the file will be successful and will change when you restart the server.
  13. Auto shrink is not supported for tempdb and we cannot shrink the internal objects inside the tempdb.
  14. When you run CHECKDB on tempdb, we run with implicit TABLOCK because a database snapshot is not supported for checkdb on tempdb. Online checkdb is not supported for tempdb. Checkdb on tempdb will run check tables on all the tables including the temp tables we create. We cannot do repair on tempdb because we cannot place the tempdb in single user mode. CHECKSUM is supported in tempdb.
  15. We can change the location of the tempdb files while in use but it is effected only after the SQL Server restart.

These are some of the interesting and cool things I learned about tempdb! I hope you learned something new today. Thanks for reading!

3 thoughts on “Interesting things about Tempdb database

  1. Hi Deepthi,

    First, thank you for the article and thank you for the multiple presentations I’ve been to. You’re helping to make a difference!

    In point #2 in your article, you mention the “-F” startup parameter. Looking at the MS documentation on startup parameters ( https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/database-engine-service-startup-options?view=sql-server-ver15 ), I see that there is a “-f” (lowercase) parameter which starts SQL Server in a “minimal” mode that’s also a single user mode, but I see no documentation on the “-F” (upper case) parameter.

    What does the uppercase “-F” startup parameter actually do and what is the source of that definition (a link or two would be appreciated)?

    For point #12, I’m not sure when they started it but, if you put a smaller value in for file size and there’s nothing in the (tempdb mdf, ndf, or ldf) file that prevents it from doing so, the file will become smaller immediately according to what is returned from sys.master_files AND the intrinsic DISK USAGE report in the Object Explorer window. I’ve also verified the immediate file size changes in Windows Explorer. Try it and see. I’m using SQL Server 2017 Developers Edition and have NOT tested it in other environments, yet, because, thanks to your article, I just realized that such a change has apparently occurred. I’ve also found nothing obvious that mentions the change in any of the MS documentation on the subject (yet).

    Liked by 1 person

    1. Hello Jeff, thanks for the feedback. -F is a typo and I changed it to -f.
      For the point #12, I tried it. When I change the size using GUI, the file becomes smaller and does show in the GUI with smaller value and also reflects in sys.master_files. Changing through GUI shrinks the file and changes immediately. I tried doing it through ALTER command:
      USE MASTER
      GO
      ALTER DATABASE TEMPDB MODIFY FILE (NAME=’logicalfilename’, SIZE=MB)
      This returns the updated value from sys.master_files but when you check the GUI, the value remains unchanged. After the SQL Server restart, the new value is reflected. I tested this on SQL Server 2012.
      I did not see the documentation mentioning about file shrinking task when using GUI but I see these blogs mentioning about it:
      https://www.sqlservercentral.com/articles/re-size-tempdb-files
      http://sqlblog.karaszi.com/managing-tempdb/
      Hope this helps!

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s