Managing TempDB in SQL Server

Over past few of years, I have given TempDB presentation in many conferences and the feedback that I have received on TempDB has been very useful. Most of you understand that TempDB is a critical database that needs to be configured carefully otherwise the performance of your application may suffer. I am not suggesting that the current implementation of TempDB addresses the challenges of every application but SQL Server has made significant improvements starting with SQL Server 2005. Given there is wide level of interest in TempDB, I decided that I will publish a series of blogs to cover TempDB basics, its monitoring and troubleshooting. I encourage you to refer to a popular white paper that I co-authored with owners from development and test teams.

TempDB Basics:

TempDB is like any other user database in that it is created using the specifications in Model Database and can be used to create/query tables but unlike its user database counterparts, it is created every time the SQL Server starts. This is significant difference with following implications

·         It does not need to be recovered. So there is no reason for TempDB to be running under FULL or BULK LOGGED recovery models. In fact, the TempDB is configured to be under SIMPLE recovery model and it cannot be changed.

·         Though the TempDB does not need to be recovered, it does not mean that operations on objects in TempDB need no logging. Now here comes the tricky part. Each operation in SQL Server is done inside an explicit or implicit (each statement is encapsulated by a transaction) and that includes operations on objects in TempDB. Let us say update a row in a global temporary table and now you rollback the transaction, there will be no way to undo this change unless we have logged the ‘before value’ of the update. However, there is no need to log the ‘after value’ of the update because this value is only needed when the transaction needs to be ‘redone’ which happens during database recovery. As I indicated earlier, the TempDB is not recovered, there is no reason to LOG the ‘after value’ of the update. As another example, let us consider Insert operation. When you insert a row in say a global temporary table, the actual ‘row value’ is not logged because SQL Server does not need the ‘row’ to undo except it needs to set the offsets within the page appropriately or if this insert had caused a new page to be allocate, de-allocate the page. So key point is, we only log ‘undo’ information but not ‘redo’.   This change on logging is available starting with SQL Server 2005 but at this time, these are supported only on a HEAP. To make the matter further complicated, not all objects in TempDB are subject to logging. I will explain this in more detail later. Let us consider one example to illustrate the point that I just made


use tempdb






drop table ##t_tempdblog



— create a global temp table. Notice, each row is

— greater than > 1000. I did this so that we can easily

— identify if the row is indeed logged.

create table ##t_tempdblog (c1 int, c2 char(1000))



— load 10 rows into this table

declare @i int

select @i = 0

while (@i < 10)


    insert into ##t_tempdblog values

          (@i, replicate (cast (‘a’ as varchar), 1000))

     select @i = @i+1



–Now run the following command to see the log records

select top 10 operation,context,

      [log record fixed length],

      [log record length]

from fn_dblog(null, null)

where allocunitname=‘dbo.##t_tempdblog’

order by [Log Record Length] Desc

Here is the output of the above query. You will notice that all log records are < 100 bytes so clearly the row to be inserted was not logged.

operation       context    log record fixed length    log record length


LOP_MODIFY_ROW  LCX_IAM            62                      88

LOP_MODIFY_ROW  LCX_IAM            62                      88

LOP_FORMAT_PAGE LCX_HEAP           80                      84

LOP_FORMAT_PAGE LCX_IAM            80                      84

LOP_FORMAT_PAGE LCX_HEAP           80                      84

LOP_MODIFY_ROW  LCX_PFS            62                      80

LOP_MODIFY_ROW  LCX_PFS            62                      80

LOP_INSERT_ROWS LCX_HEAP           62                      72

LOP_INSERT_ROWS LCX_HEAP           62                      72

LOP_INSERT_ROWS LCX_HEAP           62                      72


Now, just to prove that logging is different in user database, I will use the same example as above but with user table, here the log records that I get

operation             context   log record fixed length log record length


LOP_INSERT_ROWS      LCX_HEAP          62                      1104

LOP_INSERT_ROWS      LCX_HEAP          62                      1104

LOP_INSERT_ROWS      LCX_HEAP          62                      1104

LOP_INSERT_ROWS      LCX_HEAP          62                      1104

LOP_INSERT_ROWS      LCX_HEAP          62                      1104

LOP_INSERT_ROWS      LCX_HEAP          62                      1104

LOP_INSERT_ROWS      LCX_HEAP          62                      1104

LOP_INSERT_ROWS      LCX_HEAP          62                      1104

LOP_INSERT_ROWS      LCX_HEAP          62                      1101

LOP_INSERT_ROWS      LCX_HEAP          62                      1104

Now to illustrate, that UNDO log information is indeed logged. I did the following operation

  update ##t_tempdblog set c2 =

            replicate (cast (‘b’ as varchar), 1000)


  And, now I run the following query

select top 10 operation,context,

       [log record fixed length], [log record length]

from fn_dblog