Adding tempdb Files

We know after last week’s tempdb post that multiple tempdb data files are typically better than one. We know how to estimate the number of data files to configure. If more data files are needed, how do we get them added? What factors should we consider?

Some More

Let’s say we have 1 tempdb data file and we want to add 3 more for now. 3 is probably going to be low for most systems these days but you’ll get the idea.

Remember, we want to make sure the files that are added have matching settings so that we can then expect them to grow evenly. If you only have one tempdb data file, make sure you size it up appropriately and are considering the new files to be added. If you already have multiple tempdb files but are adding more, it’s also a good time to make sure the existing files are even and adjust if necessary before making the additions.

To add our new tempdb data files, we could run the following:

USE [master];
GO

ALTER DATABASE [tempdb] ADD FILE (
	NAME = N'tempdev2'
	,FILENAME = N'D:\tempdb_Example\tempdev2.ndf'
	,SIZE = 1 GB
	,FILEGROWTH = 100
	);

ALTER DATABASE [tempdb] ADD FILE (
	NAME = N'tempdev3'
	,FILENAME = N'D:\tempdb_Example\tempdev3.ndf'
	,SIZE = 1 GB
	,FILEGROWTH = 100
	);

ALTER DATABASE [tempdb] ADD FILE (
	NAME = N'tempdev4'
	,FILENAME = N'D:\tempdb_Example\tempdev4.ndf'
	,SIZE = 1 GB
	,FILEGROWTH = 100
	);
GO

Don’t copy/paste the whole script and execute. Make sure you have the settings configured for your environment. You’ll want to do a bit of Find/Replace to get the numbers right and possibly add more or less files.

SQL Server Trivia

Why is the default tempdb file name called tempdev (and then tempdev2, tempdev3, etc.)? Temp “development” is what I think of first but I know that isn’t right.

The “dev” in tempdev is actually short for “device.” This is based on database sections of a drive needing defined as devices back in the early days of SQL Server. You’re now ready for the next episode of Jeopardy!

Thanks for reading!