Monday, March 5, 2007

Add Data File or Move Data File for TempDB

TempDB works slightly differently than other user databases when it comes to adding or moving data files to it. There are occasions when a DBA may need to add new data files to TempDB or move the data files around.
Some reasons to add data files = running out of space in current data files, added new processor to database server or moved to a server with more number of physical processors, etc.
General Rule = Number of data files for TempDB = Number of Physical processors on server.
This rule is true for servers with upto and including 8 physical servers.
All files must be of the same size for SQL server to intelligently use the files.
Here are the steps needed to achieve that.

----- Move Data File for TempDB
USE master
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'X:\tempdb.mdf') -- New Location
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'X:\templog.ldf') -- New Location
GO
-- After this stop and restart sql server.

----- Add New Data File to TempDB
ALTER DATABASE tempdb
ADD FILE (NAME = TempDB3
, FILENAME = 'X:\tempdb3.mdf'
, SIZE = 4000) ----- Size should be same as other Data Files in MB.
GO
-- After this stop and restart sql server.

1 comment:

Anonymous said...

Aw, this was a really quality post. In theory I' d like to write like this too - taking time and real effort to make a good article... but what can I say... I procrastinate alot and never seem to get something done.