Need for TempDB Log File

While working on some issue, it struck to me on “why we need log File for TempDB”. Generally, we need log file during crash recovery to rollback or roll-forward the transactions based on whether they are committed or not at that time. This helps the database to be transactionally consistent. Of course, we need log file so we can take log backups and this helps us to make  point in time restoration. PS: Log file keeps track of many other events that happen within the user database.

But neither of these points apply for TempDB as it always gets recreated every time the instance restarts. I did some research and found out that we need TempDB log file for ‘ROLLBACKS’.Apparently, I was not thinking clearly.(It was always makes sense, when you know the answer). Also, I learnt that Logging in TempDB is different than User databases and logging is lighter and faster in TempDB.

So, if we have to create a temp table, it lot better to create on TempDB than USer DB as it is much faster because of less logging

Advertisement

Moving system database files to new location

Sometimes, we need to move system databases to a different location for various reasons. Moving system databases is little different compared to user databases. TempDB is the usual suspect as it can fill up the disk space and if it is not on a dedicated disk, it can cause more problems. In that case, we can either add a new file or move the TempDB to different drive.

To add new file to TempDB, you can do as below.
USE [MASTER]
GO
Alter database TempDB
Add File (Name= 'TempDev2', FileName = 'E:\TempDBData\TempDev2.ndf',SIZE = 4096MB , FILEGROW =500MB)
Note: TempDB cannot have user defined filegroups. Also, Sql Server uses Proportional Fill algorithm to split the data across multiple files. So, use caution on how you allocate the disk space.

If you could afford some downtime and prefer to move the TempDB files to different location, you can do as below and RESTART the sql server.
USE [MASTER]
GO
Alter database TempDB
MODIFY File (Name= 'TempDev', FileName = 'E:\TempDBData\TempDev.mdf',SIZE = 4096MB , FILEGROW =500MB)
GO
Alter database TempDB
MODIFY File (Name= 'TempLog', FileName = 'E:\TempDBLog\TempLog.ldf',SIZE = 1024MB , FILEGROW =250MB)

NOTE: one of the best practice suggestion is to assign one tempdb file for every 4 logical processors, i.e, if you have 16 logical processors, it is best to have maximum of 4 TempDB files.

MSDB: I recently had to move MSDB to different drive and below are steps I followed to accomplish the same :
1. Use Alter Database .. Modify File .. command to make the sys.database_files point to the new location.
2. We cannot set the MSDB database to offline. So, we need to stop the SQL Server.
3. Copy over the files to new location.
4. Restart the SQL Server.

TempDB Actual Current Size

Sometimes, the actual physical size of TempDB is not always shown correctly when you run the below query.

Select Database_id,((size*8)/1024) as SIZE_IN_MB, Name,Type_desc,Physical_Name from sys.master_files where database_id=2

Or when you right click on the TempDB database and look in the properties.

It only shows the last configured\start up size of the TempDB. This happens when the TempDB grows beyond the initial configured size.So, to get the actual size of the TempDB, we need to run

sp_helpdb 'TempDB'

You can also use below query to find the actual current size of the TempDB.
select Name,((size*8)/1024) as SIZE_IN_MB from Tempdb.sys.database_files