Managing TEMPDB Issues

When a TEMPDB disk space alert occurs its typically due to only a one thing, a long running open transaction or process. Currently, due to how our build process creates TEMPDB, both the TEMPDB datafiles and transaction log itself are located on the same dedicated drive (X:\). Due to this, space issues can occur for the following reasons:

1. One or more TEMPDB data-files auto-grow

   1. This is only an issue when the auto-growth size is very large. Some datafiles will have enough space on disk to grow while others wont be able to. These large autogrowth sizes cause drive to run out of space before all files can grow equally.

      • Nothing can be done in this scenario outside of fixing the TEMPDB metadata during a service cycle

      • This can be checked by reviewing the standard reports

        1. Right Click TEMPDB → Reports → Standard Reports → Disk Usage

        2. Look for auto-growths for both Datafiles and LogFiles

2. The TEMPDB transaction log required more space to grow due to a long running open transaction within TEMPDB itself

   1. The below scripts will help determine if this is true

Due to TEMPDB drive (H:\) being a located on a local drive which cannot be grown, we have to treat TEMPDB uniquely. Please follow the below procedure when TEMPDB space alerts occur.

1. Run the below scripts on the server having TEMPDB issues

USE tempdb;

GO

IF OBJECT_ID('tempdb..#DiskSpaceMetrics') IS NOT NULL DROP TABLE #DiskSpaceMetrics

SELECT @@ServerName AS ServerName,

DBName,

LogicalFileName,

CASE FileType WHEN 0 THEN 'DATA' ELSE 'LOG' END AS FileType,

FileGroupName,

PhyscialFileName,

CEILING(FileSize) AS FileSizeMB,

SpaceUsed AS SpaceUsedMB,

ROUND((FileSize - SpaceUsed),2) AS SpaceFreeMB,

ROUND(100*((FileSize-SpaceUsed)/FileSize),2) AS PercentFree,

MaxSize AS MaxSizeMB,

FileGrowth AS FileGrowthMB,

IsPercentGrowth,

MountPoint,

DiskSize AS DiskSizeMB,

DiskFreeSpace AS DiskFreeSpaceMB,

ROUND(DiskPercentFree,2) AS DiskPercentFree

INTO #DiskSpaceMetrics

FROM

(

SELECT

db_name() as DBName,

df.name AS [LogicalFileName],

df.type AS FileType,

df.physical_name AS PhyscialFileName,

CAST(df.size/128.0 AS FLOAT) AS [FileSize],

(CAST(CASE df.type WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS float)* CONVERT(float,8) END AS float))/1024 AS [SpaceUsed],

CASE

WHEN df.type_desc = 'FILESTREAM' THEN NULL

WHEN df.is_percent_growth = 0

THEN ceiling((df.growth * 8192.0)/(1024*1024))

ELSE df.growth

END AS FileGrowth,

df.is_percent_growth AS IsPercentGrowth,

g.name as [FileGroupName],

CASE df.max_size WHEN -1 then df.max_size else df.max_size/128 end as MaxSize,

v.volume_mount_point AS MountPoint,

CAST((v.total_bytes/1024)/1024 AS FLOAT) AS DiskSize,

CAST((v.available_bytes/1024)/1024 AS FLOAT) AS DiskFreeSpace,

CAST(CAST(v.available_bytes AS FLOAT)/ CAST(v.total_bytes AS FLOAT) AS FLOAT) * 100 AS DiskPercentFree

FROM sys.database_files AS df

INNER JOIN sys.master_files m on df.file_id = m.file_id

LEFT JOIN sys.data_spaces AS g ON df.data_space_id=g.data_space_id

CROSS APPLY sys.dm_os_volume_stats(database_id, m.file_id) v

WHERE m.database_id = DB_ID()

) DBFileSizeInfo

ORDER BY FileType

SELECT Name AS DBName, log_reuse_wait_desc FROM sys.databases WHERE name = DB_NAME(DB_ID())

SELECT ServerName,DBName AS ObjectName,SUM(FileSizeMB) AS TotalSizeMB, SUM(SpaceFreeMB) AS TotalFreeSpaceMB,ROUND(100*((SUM(FileSizeMB)-SUM(SpaceUsedMB))/SUM(FileSizeMB)),2) AS TotalPrecentFree

FROM #DiskSpaceMetrics

WHERE FileType ='DATA'

GROUP BY ServerName,DBName

UNION

SELECT ServerName,DBName+' Log' AS ObjectName,SUM(FileSizeMB) AS TotalSizeMB, SUM(SpaceFreeMB) AS TotalFreeSpaceMB,ROUND(100*((SUM(FileSizeMB)-SUM(SpaceUsedMB))/SUM(FileSizeMB)),2) AS TotalPrecentFree

FROM #DiskSpaceMetrics

WHERE FileType ='LOG'

GROUP BY ServerName,DBName

SELECT DISTINCT MountPoint, DiskSizeMB AS TotalDiskSpace, DiskFreeSpaceMB, DiskPercentFree

FROM #DiskSpaceMetrics;

SELECT ServerName, DBName, LogicalFileName, FileType, PhyscialFileName, FileSizeMB AS TotalSizeMB, PercentFree, MaxSizeMB, FileGrowthMB, IsPercentGrowth

FROM #DiskSpaceMetrics;

The above script will produce 4 distinct outputs

1. The first will show you whether the transaction log is waiting on anything to truncate

   • If log_reuse_wait_desc is ACTIVE_TRANSACTION, please look for a long running open transaction

   • If log_reuse_wait_desc is NOTHING, the transaction log may be shrunk to free up space (ONLY if its determined that log growth is determined to be the culprit)

2. The second will provide details on total/free space for both TEMPDB and its transaction log

   • If you see a low TotalPercentFree space on the transaction log but high TotalPrecentFree space for the databases itself, this may mean we have a log running transaction open causing the TEMPDB log to grow. In this situation, you should proceed to determine if the log running open transaction can be killed by reaching out to the code owner

   • If you see low TotalPercentFree for TEMPDB itself, please run below script to determine what SPID is causing high TEMPDB usage. If this is due to a user query, please reach out to user and determine if it can be stopped or killed

sp_whoisactive @filter_type ='Database', @filter = 'tempdb', @show_sleeping_spids =2

3. 
   The Third output will show you all LUNs associated with TEMPDB

   • This will show you how much space is left and if any other LUNs are associated with TEMPDB besides H:\

4. The last output shows all data file details for TEMPDB

   • Pay close attention to MaxSizeMB as this will help you understand if an auto-growth could of been the culprit. If the MaxSizeMB is more then the totalSizeMB, then an auto-growth can occur. This may be a problem and should be fixed to avoid future space issues

2. If its determined that transaction log auto-growth is the cause of the TEMPDB drive running out of space, follow below:

1. Review standard reports for log autogrowths

2. Review above output for MaxSizeMB of the LOG file. If value is 2097152, this means auto-growth is unlimited

3. Review the lg_common LUN for freespace

If there is ample free space on the lg_common LUN, we will want to added a log file to that LUN with below script. File has been capped at 200 GB (Make sure this is enough!)

ALTER DATABASE [tempdb] ADD LOG FILE ( NAME = N'templog_01', FILENAME = N'W:\SQL\Logs\<log mountpoint>\templog_01.ldf' , SIZE = 4194304KB , MAXSIZE = 209715200KB , FILEGROWTH = 4194304KB )

4. Lastly, we will want to attempt to shrink the original TEMPDB log file by some amount. We can shrink the TLOG by 1 of two ways, TRUNCATEONLY, or shrinking it to a determined size. Depending on how much free space the log file has, start with TRUNCATE ONLY.

   1. 1. Make sure to look for blocking when running below script!

USE [tempdb]

GO

DBCC SHRINKFILE (N'templog' , TRUNCATEONLY)

GO

5. Lastly, we need to cap log file to its current size. This will stop it from growing further and causing another space issue. This can be done using below script:

USE [tempdb]

GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', MAXSIZE = <SIZE OF LOG FILE IN KB>KB )

GO