↑ Back to top
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.
I would be using fn_dblog and DBCC IND command which are undocumented commands. Undocumented means Microsoft does not support these commands, changes can be made to these commands without giving any information to user so unexpected result might occur. So
I advise readers not to use these commands on production database. You can play around with these commands on Test databases.
This is a fresh installation of SQL Server 2012 evaluation edition on my machine. And below is snapshot of Tempdb
Now let's take a look at the model database with the same query
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
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
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
'D:\Program Files\Microsoft SQL Server\MSSQL11. MSSQLSERVER\MSSQL\DATA\tempdev1. mdf'
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
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
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.
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?
use Tempdb ---my habit to use database name specifically
IDENTITY (1, 1),
[log record length],
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.
(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.
ID],[log record length],[AllocunitName],[Page ID][
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
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
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.
'D:\Program Files\Microsoft SQL Server\MSSQL11. MSSQLSERVER\MSSQL\DATA\TempDemo. mdf'
MAXSIZE = 50,
FILEGROWTH = 15% )
'D:\Program Files\Microsoft SQL Server\MSSQL11. MSSQLSERVER\MSSQL\DATA\TempDemo_log. ldf'
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
--to remove database from Pseudo simple recovery
IDENTITY (1, 1),
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 .
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
id = object_id (
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.
To prove that it is first page I ran DBCC IND command as below
DBCC IND (TEMPDEMO, TEMPTEST, 1)
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
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
Tempdb owner definition as per BOL
Below articles will help you in understanding behavior of fn_dblog command
This Article participated in Technet Guru Competition March 2014 and won Silver Medal.
I would like to thank
Alberto Morillo MVP and
Olaf Helper MVP for reviewing this article.