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.
Monday, March 5, 2007
Subscribe to:
Post Comments (Atom)
1 comment:
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.
Post a Comment