Adding and removing TempDB files

Add/Remove data file to avoid TempDB Running out of space

Today I ran into a situation where there was heavy use of TempDB which caused data files to grow beyond the capacity of the drive.

So as an immediate solution I decided to add one more file to TempDB on a separate drive where there was space available. As you know this is going to be a NDF file.

To avoid new drive running out of space this new file needs to be restricted in maximum growth.

Use following script to add the file

USE [master]

GO

ALTER DATABASE [tempdb]

ADD FILE ( NAME = N'tempdev01',

FILENAME = N'D:\DATA\tempdev01.ndf' ,

SIZE = 10240000KB ,

MAXSIZE = 76800000KB ,

FILEGROWTH = 102400KB )

GO

Now once you know use of TempDB has receded and it is safe to remove the NDF file following script should be used. This is done in 2 parts.

1)      File can only be removed if it is empty so first we empty the file

use tempdb

go

DBCC SHRINKFILE ('tempdev01' , EMPTYFILE) ;

go

2)      After the file is empty then file can be removed

USE Master

GO

alter database tempdb REMOVE file [tempdev01] ;

So in short it is possible to temporarily allocate disk space to TempDB to avoid stoppage.

PS: No need to restart SQL Service for removing TempDB files