Need best practices to maintain TempDB which is growing to 100GB now, is moving Tempdb to 35GB drive have any limitations?

sql-server-2008tempdb

Can moving TempDB to smaller drive cause failures?
How can TempDB be configure to use 35GB drive without any issues?
Please suggest if something needs to be taken care for this smooth transition of TempDB to a 35GB drive?

This small drive was dedicatedly designed to have TempDB but when some of the process started giving TempDB space issues we moved it to drive where it can grow as much as required, it is now 130GB. so was thinking was it really required to keep it grow as much as possible? need to know how can i limit it to considerable size without facing any issues and also how can i maintain it in long run?

It shows 135GB total space and available free space is 95%..so was thinking would regular shrinking is advisable or the better way is to move it 35GB drive?

Best Answer

If you move tempdb to a drive with insufficient space, then you will encounter errors and performance problems arising from this. If, however, your tempdb growth was a one-off event, then you might not have any issues moving back to the 35 GB drive. The only way to know for sure is to track your current tempdb usage and determine if you require additional space.

Use the following query to create a table in a suitable database to store tempdb space information

CREATE TABLE TempDBSpace (
    [LogDate]   DATETIME
    , [FileType]    NVARCHAR(120)
    , [FileName]    NVARCHAR(255)
    , [FilegroupName]   NVARCHAR(255)
    , [Filepath]    NVARCHAR(500)
    , [FileSize_MB] DECIMAL(10,2)
    , [UsedSpace_MB]    DECIMAL(10,2)
    , [FreeSpace_MB]    DECIMAL(10,2)
    , [FreeSpace_%] DECIMAL(5,2)
)

Then create a SQL Agent job to log the tempdb space consumption using this query:

INSERT INTO [<db where you created the table above>].[dbo].[TempDBSpace]
SELECT GETDATE() AS [LogDate]
    , [FileType]        = f.[type_desc]
    , [FileName]        = f.[name]
    , [FilegroupName]   = fg.[name]
    , [Filepath]        = f.[physical_name]
    , [FileSize_MB] = CONVERT(DECIMAL(10,2),f.[size]/128.0)
    , [UsedSpace_MB]    = CONVERT(DECIMAL(10,2),f.[size]/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(f.[name], 'SPACEUSED') AS INT)/128.0))
    , [FreeSpace_MB]    = CONVERT(DECIMAL(10,2),f.[size]/128.0 - CAST(FILEPROPERTY(f.[name], 'SPACEUSED') AS INT)/128.0)
    , [FreeSpace_%] = CONVERT(DECIMAL(10,2),((f.[size]/128.0 - CAST(FILEPROPERTY(f.[name], 'SPACEUSED') AS INT)/128.0)/(f.[size]/128.0))*100)
FROM sys.database_files f
LEFT JOIN sys.filegroups fg ON f.[data_space_id] = fg.[data_space_id]
ORDER BY f.[type], f.[name]
;

Note that this job must target tempdb as the database for the T-SQL job step, otherwise it will not return data.

Schedule this to run frequently, say every 5 minutes, and leave the job enabled for a suitable period, at least 1 month, but ideally, 3-6 months, to collect enough data to make an informed decision.

When the suitable time has elapsed, check the max total consumption of your data and log files for tempdb using this query:

SELECT [FileType]
    , MAX(UsedSpaceTotal_MB) AS [MaxSpaceUsed]
FROM
(
    SELECT [LogDate]
        , [FileType]
        , SUM([UsedSpace_MB]) AS UsedSpaceTotal_MB
    FROM ##Temp
    GROUP BY [LogDate]
        , [FileType]
) s
GROUP BY [FileType]

If the max consumed space doesn't exceed the available space on your intended drive, then you should be okay to proceed with the move. If the max consumption is greater, you will likely encounter errors and performance problems if you move tempdb to your intended drive.

If tempdb's growth was the result of an event such as a major database change that may occur occasionally, you're better placed to use an overflow file in tempdb rather than moving the whole thing.

For example, you can configure tempdb to use the 35GB drive normally, grow your files to fill this drive and set their maximum size as per best practise. Then when you're planning for one of these events that require additional tempdb space, simply add an additional file to tempdb on a different drive.

Once your process is finished, you can flush the data out of your overflow file and remove it from tempdb, and your existing configuration on the 35GB drive remains unchanged.

Related Question