How To Clear Temp Files In Sql Server

SQL Server – How to Best Remove Extra TempDB Data Files and Log Files

May 6, 2013 by Suresh Raavi

One of the Microsoft's recommendation for optimizing the tempDB performance is to make each tempdb data file the same size.

Today, on one of our servers, I noticed there are 13 data files with different sizes as shown in the below screenshot:

tempdb1

My target here is to configure tempdb with 8 equi sized data files and one log file. So, in this case I have to delete those 5 extra data files and re-size the remaining 8 files equally.

To achieve this I followed the below simple three step process (The same procedure can be applied to any user databases as well)

            --              Step1: First empty the data file                        USE                        tempdb                        GO DBCC                        SHRINKFILE                        (            tempdev12            ,                        EMPTYFILE            );                        -- to empty "tempdev12" data file                        GO

Data file won't gets deleted unless it is empty. So before going to Step2 to delete the data file, we should first empty the data file which can be done from the Step1 above.

Note: If encountered this errorMsg 2555, Level 16, State 1, Line 1while performing the above operation, please refer to this post –> Msg 2555, Level 16, State 1, Line 1 – Error DBCC SHRINKFILE with EMPTYFILE option

                          --Step2: Remove that extra data file from the database                        ALTER DATABASE                        tempdb                        REMOVE FILE                        tempdev12            ;                        --to delete "tempdev12" data file                        GO

I repeated the above two steps to delete the other files as well. Below is the screenshot after deleting the extra files

tempdb2

                          --Step3: Re-size the data files to target file size                            -- Use ALTER DATABASE if the target file size is greater than the current file size                        USE                        [master]                        GO ALTER DATABASE                        [tempdb]                        MODIFY FILE                        (                        NAME                        =                        N'tempdev'            ,                        SIZE                        =            3072000KB                        )                        --grow to 3000 MB                        GO                        --Use DBCC SHRINKFILE if the target file size is less than the current file size                        USE                        [tempdb]                        GO DBCC                        SHRINKFILE                        (            N'tempdev'                        ,            3000)                        --shrink to 3000 MB                        GO

In my case, the target file size is 3000 MB which is greater than the current file size, so I ran the ALTER DATABASE command for all the 8 files. And below is the screenshot after re-sizing the files

tempdb3

Note: The same procedure can be used for removing extra log files as well. Make sure the log file you are trying to empty do not have any active portion of the log. Run DBCC LOGINFO on your specified database, taking note of the FileId and the Status indicator (2 showing that it is an active VLF)

Posted in Files and FileGroups, TempDB | Tagged SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | 13 Comments