Understanding Logging in Tempdb. Is Tempdb Recreated or Rebuilt after SQL Server Starts?

 

 

↑ Back to top

---

Introduction

Have you come across posts in forums where people say Tempdb is rebuilt from copy of model database after SQL Server re-starts or starts? Books Online (BOL) says that Tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database but does not give enough information what re-created means. So what is the difference between re-created and rebuilt? There must be some reason why Microsoft specifically used the word re-created not rebuilt. Actually it is correct that Tempdb is re-created not rebuilt when SQL Server starts and I will try to show in my post what does it mean.

So to show what re-created actually means we did a little test on my SQL Server 2012 machine as below.

NOTE: There is a debate going on Linkedin where folks say Tempdb is even not recreated. Its is actually cleared. Without going into this debate we would stick to lingo used by Microsoft instead of reinventing the wheel.

 

↑ Back to top

---

Performing the test

This is a fresh installation of SQL Server 2012 evaluation edition on my machine. And below is snapshot of Tempdb

use Tempdb
go
select  name, 
physical_name,
size
from sys. database_files
go

Now let's take a look at the model database with the same query

use model
go
select  name, 
physical_name,
size
from sys. database_files
go

Below is a screenshot of it

Now I will increase the size of Model database to 50 MB. After this I will restart SQL Server instance to check whether Tempdb is really re-created or rebuilt from copy of Model database

USE model;
GO
 
ALTER DATABASE Model
MODIFY FILE
(NAME = modeldev,
SIZE = 50MB);
 
GO
 
Select  name, physical_name,  size from sys.database_files 

Below is image after the change

Now let's restart SQL Server Service from SQL Server configuration manager. After restarting I run below command to check size

  use Tempdb

go

select name``, physical_name, size from sys. database_files

go

 

use Model

go

select name``, physical_name, size from sys. database_files

go

Below is the screenshot of the results from these queries. Now you can see it is correct to say that Tempdb is re-created /rebuilt (please allow me to use both words as still it is not clear) from the **copy **of model database. I also want to point out that copy here does not mean every property of Model database is copied to Tempdb.  In my case recovery model of Model database is full but Tempdb always works in simple recovery. What does copy means I will just point out further in my post.

Now to prove that, is Tempdb rebuilt or recreated with copy of model database I will add a data file to Tempdb but not to Model database. If Tempdb were to create from copy of Model database, after SQL Server restart the data file that I have added to Tempdb must be gone and it must have exactly one data file and log file as Model database. Let's check

Below query will add a data file to Tempdb

use Tempdb
go
Alter  Database Tempdb
Add File
(
 NAME = tempdev1,
 FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL11. MSSQLSERVER\MSSQL\DATA\tempdev1. mdf', 
 SIZE = 5MB,
 MAXSIZE = 100MB,
 FILEGROWTH = 5MB
)

Now I restarted SQL Server from configuration manager. And run query to check data file statistics of Tempdb and result is below

 

↑ Back to top

---

Conclusion about re-created/rebuild

From the result above it is clear that Tempdb is not rebuilt but re-created from the model database. There does not happen any process of dropping Tempdb and rebuilding it. The metadata and the information about its last known structure, here specifically new data file which were added, is preserved and is used while recreating it. Tempdb is not deleted when SQL Server restarts its objects like tables, stored proc which resides in it are deleted and metadata, allocation pages and bitmaps are copied from Model to create fresh copy of Tempdb.

To check whether metadata and objects are copied from Model when Tempdb is re-created you can create a table in Model database and restart SQL Server instance. Now check Tempdb this table would exist in Tempdb after restart. I leave this task for readers to test.

If you refer to SQL server errorlog using below query you can see that it is written 'Clearing Tempdb database', it is nether written recreating nor rebuilding.

 Exec  sp_readerrorlog 0,1,'tempdb'

 

↑ Back to top

---

Understanding Recovery Model in Tempdb

Recovery model of Tempdb is always simple. So one can guess logging and recovery would also be same as in case of simple recovery. I would like to shed some light on simple recovery model. It is quite same as full recovery model just in simple recovery model automatic checkpoint happens when log file grows to 70 % of its size, unless some transaction is holding it, and log is truncated. You can run two transactions one in full recovery and one in simple  and can use sys. dm_tran_database_transactions DMV to see logging please refer to suggested reading section for more details regarding this DMV . There would not be much difference in size. Now BOL says that Operations within Tempdb are minimally logged.

Note: Tempdb works in simple recovery mode but operations are minimally logged. Isn't that intriguing?

 

↑ Back to top

---

Performing test to understand Recovery model

There has always been debate what is meant by minimally logged. Books Online by Microsoft says that Minimal logging involves logging only the information that is required to recover the transaction without supporting point-in-time recovery but does not give much information about what information is actually logged . Paul Randal wrote in his logging and recovery article when he was with Microsoft that only allocation changes are logged( see suggested reading  section article logging and recovery ). Kalen Delaney says enough information is logged so that transaction can be recovered (I believe her) . I will try to show what is logged using undocumented command fn_dblog. I started using this undocumented command after reading various articles by Paul Randal. So a big thanks to him. I will put his articles in reference section.

use Tempdb ---my habit to use database name specifically
GO
CREATE  TABLE #temptest
(
 Id   int  NOTNULL  IDENTITY (1, 1),
 c1  char(50)  NOT NULL  DEFAULT('temptest')
)
GO
 
SET NOCOUNT ON
GO
 
INSERT  INTO #temptest DEFAULT VALUES
GO 10000
 
select [current lsn],
[operation],
[transaction ID],
[log record length],
[AllocunitName],
[AllocUnitId],
[Transaction name], 
[Description]
from
fn_dblog(null, null)

Below is screenshot of result obtained from the above query

LOP_BEGIN_EXACT: Signifies beginning of a implicit transaction

LOP_INSERT_ROWS: Signifies rows being inserted

LOP_COMMIT_EXACT: Signifies commit of implicit transaction

Transaction ID: Internal ID given by SQL Server to a transaction

Log Record length: Length of log records being generated for transaction

AllocUnit Name : Refers to allocation type.

Now let's analyze the output. If you see in figure above all marked in rectangle have the same Transaction ID. This transaction ID corresponds to insertion of two rows in a table . One Identity column and one 'temptest' value. If you see at description column it shows Insert: 0x01 this hexadecimal value corresponds to decimal value 1 which is what we are inserting. This has repeated itself and that is what I had marked . See transaction ID 7a30 . One row for begin one row for insert and one row for commit. Same for transaction ID 7a32 and same for 7a33. 7a34 and so on. Note that current LSN for these transactions I have pointed out they are in sequence and log record size is equal.

There is one more thing I need to talk about please refer below figure it has been taken figure above. I took it out separately so that we can dig little deeper

 

LOP_COUNT_DELTA: Signifies metadata being updated when rows are inserted. Notice transactionID for count_delta is zero.

Sixth column is above screenshot is AllocationUnitID . We can query sys.allocation_unit catalog in SQL Server to see what these ID's corresponds to.

use master
go
select *  from sys. allocation_units
where allocation_unit_idin (458752, 327680, 196608)

Below is the result

So IN_ROW_DATA is allocation Unit which corresponds to insertions of new data into SQL Server. In SQL Server there are 3 standard allocation units( there are many but I am talking about standard one) one of which is in_row_data please refer to suggested reading section for more information about allocation unit. IN_ROW_DATA allocation unit comes in picture when data is inserted as this allocation unit holds majority of data in SQL Server. For all data that can be stored in 8KB char, int, datetime all comes under this allocation unit. So above result points to fact that during insertion of data in Tempdb information about being and commit transaction was there and information about allocation units is there. There is no information about pages or rows being inserted,  no information about locks being taken. In my opinion this is what meant by Allocation changes are logged.

I also quoted from Books online that 'Minimal logging involves logging only the information that is required to recover the transaction without supporting point-in-time recovery'. To explain this I performed below operation on SQL Server. After a restart I ran below code. Code is same as previous one this time I will just add five records and then update a record in the table. I have kept record count simple for more understanding.

  use tempdb

go

CREATE TABLE #temptest

(

 ``Id  ``int  NOT NULL  IDENTITY (1, 1),

 ``c1  ``char``(50)  ``NOT NULL  DEFAULT (``'temptest'``)

 ``)

GO

 

SET NOCOUNT ON

GO

 

INSERT INTO #temptest DEFAULT VALUES

GO 5

 

 

UPDATE #temptest set  c1=``'Temp' where ID=1

SELECT  [``current lsn],[operation],[``transaction ID],[log record length],[AllocunitName],[Page ID][``Transaction name``],[Description]

FROM

fn_dblog(``null``,``null``)

After running above code I got below output. I would like you to move down to last row returned by query as this has interesting thing in it.

If you see the figure above in description column TransactionID 0000:0000338 is for a insert operation whose data being inserted is below

INSERT;0x010500000000000515000000df131d06ee01c203416ab3b2e8030000

If you see decimal value of 0x01 it will be equivalent to 1 which we are inserting.

Now look at the update operation having transactionID 0000:0000033a below is value

UPDATE;0x010500000000000515000000df131d06ee01c203416ab3b2e8030000

What is important to note is both values are the same.

Now what can be derived from here is during an update operation information about which value changed is present what was the value after the change is not present.So information needed for rollback is there but for roll-forward no information is there as we can see no information about value after running update is present.So we can say that enough information to rollback a transaction is logged but no information which can help in redo is logged.

With all discussed about minimal logging It would be worth showing here how logging works in full recovery to figure out the actual difference. So to show this I created a database with full recovery model. Took full backup to remove it from Pseudo simple recovery and then ran same fn_dblog command to see the behavior.

USE master;
GO
CREATE DATABASE  TempDemo
ON PRIMARY
( NAME  = TempDemo_dat,
 FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL11. MSSQLSERVER\MSSQL\DATA\TempDemo. mdf',
 SIZE = 50,
 MAXSIZE = 50,
 FILEGROWTH = 15% )
  
LOG ON
  
( NAME  = TempDemo_log,
 FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL11. MSSQLSERVER\MSSQL\DATA\TempDemo_log. ldf',
 SIZE = 5MB,
 MAXSIZE = 25MB,
 FILEGROWTH = 5MB ) ;
GO
  
use tempdemo
go
  
alter database  tempdemo set  recovery full
go
backup database  tempdemo to  disk='D:\Tempdemo. bak'--to remove database from Pseudo simple recovery
go
  
CREATE TABLE  temptest
(
 Id  int   NOT NULL   IDENTITY (1, 1),
 c1  char(50)  NOT  NULL  DEFAULT  ('temptest')
 )
GO
  
SET NOCOUNT ON
GO
  
INSERT INTO  temptest DEFAULT VALUES
GO 10000
  
select [current lsn],
[operation],
[transaction ID],
[log record length],
[AllocunitName],
[AllocUnitId],
[Transaction name],
[Description]
from
fn_dblog(null, null)

The result set was big now you can see lot of operation mentioned here like LOP_FORMAT_PAGE, LOP_MODIFY_ROW, LOP_HOBT_DELTA, LOP_LOCK_XACT. If you remember well these were missing for same operation done in Tempdb. Now below are some significance each has

LOP_FORMAT_PAGE: This indicates that page allocation has been done

LOP_MODIFY_ROW: Indicated that row was modified as result of Insert command

LOP_LOCK_EXACT: Indicates various locks which were taken during modification.

Now we can see starting from page to row to locks all information has been logged . Now I refined a query little bit like below to focus on one TransactionID. This transaction ID corresponds to beginning of insert operation .

select [current lsn],
[Page ID],
[operation],
[transaction ID],
[log record length],
[AllocunitName],
[AllocUnitId],
[Transaction name],
[Description]
from
fn_dblog(null, null)
where [Transaction ID]='0000:000002f6'

 

When I run this query below is the output

Now I marked pageID =0001:00000050 and Operation as LOP_FORMAT_PAGE . When a new page is allocated to store data this operation corresponds to that. Now I need to check whether pageID 0001:00000050 is first page for table Temptest( this is only table in the database Tempdemo). I took help of below article by Paul Randal to prove it

Using DBCC Page to find first page of table

I ran below query. As per above article to hexadecimal value of first page of a table which could be either heap or clustered index below query will required output

      SELECT first  FROM sysindexes
      WHERE id = object_id ('temptest')
      AND indid in (0, 1)

In my case output was as below

 Now 0x500000000100 is your first page for table temptest. Now if refer to article it will show you how to convert this into file an page format.

1. Separate the output in group of two  0x 50 00 00 00 01 00 because in hexadecimal notion each set of two characters represent a byte.
2.  Leaving 0x as it it swap the output like00 0100 00 00 50
3. Now first two (00 01) represents two byte file number and rest represent page number ( 00 00 00 50)
4.  So now file is 0x0001 and page is 0x00000050
5.  Decimal value of  0x00000050 is 80. so page with ID 80 will be first page .

To prove that it is first page I ran DBCC IND command as below

 DBCC IND (TEMPDEMO, TEMPTEST, 1)  

Below is the output

80 is the PagePID of first page with pagetype as 1 which corresponds to data page. Page type 10 corresponds to IAM page. More details about DBCC IND in below link

Fun with DBCC IND and DBCC PAGE 

↑ Back to top

---

Conclusion about Recovery Model

From this result we can see that in case of Tempdb allocation units corresponds to IN_ROW_DATA. In SQL Server there are 3 standard allocation units one of which is in_row_data. This allocation unit comes in picture when data is inserted as this allocation unit  holds majority of data in SQL Server. For all data that can be stored in 8KB char, int, datetime all comes under this allocation unit. So above result points to fact that during insertion of data in Tempdb information about being and commit transaction  was there and information about allocation units is there. This is what Paul Randal meant when he said allocation changes are  logged in Minimal logging. Please refer to below article about recovery models and Minimal logging as said by Paul.  

With full recovery model each and every thing about a transaction is logged. Right from allocation of page to locks being taken. The mere fact that there were lots of more operations present in fn_dblog output for full recovery points to fact that how through an inclusive logging happens in full recovery. My motive was to point on fact that in minimal logging what gets logged as Paul said allocation changes are locked and we saw it was there. I specifically pointed out pages as I wanted to show information about page changes is also present in full recovery

Logging and recovery in SQL Server 

↑ Back to top

---

Owner of Tempdb

If you refer to Books Online (BOL) it says that owner of Tempdb is DBO but I disagree to this fact. Owner of Tempdb is always SA. In my humble opinion this information should be corrected. I cannot understand what DBO means here. You cannot change owner of Tempdb, as a fact you cannot change owner of any system databases.

Tempdb owner definition as per BOL 

↑ Back to top

---

Summary

 Recovery model of Tempdb is always simple but operations are logged minimally. Only *enough* information is logged so that query running in Tempdb can be rolled back. Crash recovery does not happens in Tempdb and so there is no information logged to roll forward a transaction just enough information so that it can be rolled back. How it does exactly  is only known within Microsoft. Owner of Tempdb is always SA. Checkpoint also works differently in Tempdb since crash recovery does not happen in Tempdb when checkpoint happens (automatically) dirty pages are not flushed to disk. For checkpoint in Tempdb please see See Also section

↑ Back to top

---

Suggested Readings

• Understanding Logging and recovery in SQL server
• Tempdb
• sys.sysindexes
• Sys.allocation_units
• sys.database_files
• Understanding pages an extents
• Sys.dm_tran_database_transactions  

↑ Back to top

---

See Also

Below articles will help you in understanding behavior of fn_dblog command

• SQL Server General & Database Engine Resources on the TechNet Wiki
• List of articles by Shanky

This Article participated in Technet Guru Competition March 2014 and won Silver Medal. 

↑ Back to top

---

Credits

I would like to thank Alberto Morillo MVP and Olaf Helper MVP for reviewing this article.