Cluster based TEMPDB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Cluster based TEMPDB

Author

Topic

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2010-02-23 : 23:10:28

It is Cluster Server and We have to move the TEMPDB to another New Drive, however, what are the implications if moving the MDF and LDF Data files to the new locations?

The Process involves here is Physically moving the datafiles to new location,then restart the services so that the new location TEMPDB files will be operable after restart.

My question is whether this operation of Moving the files will cause any Fail-over on the Cluster server?

The Failover is located on the server where the TEMPDB is going to get moved out.

Can anyone clarify this, thanks all.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-02-25 : 15:52:43

It is not necessary to move either the TempDB data or log files since they get recreate whenever the server restarts; in fact moving them would not accomplish what you want. This script will walk you through the steps necessary to move the temp database to a new location. You can safely ignore step #3 since we are dealing with TempDB specifically.

One thought: Are you sure that your SQL Server can access files at the new location? You could verify this by creating a new database and putting its files on the desired directory.

Moving the database should not inherently cause the cluster to failover.

And now, on to the script...

READ THE INSTRUCTIONS!!! DON'T JUST RUN THE ENTIRE SCRIPT AT ONCE!!!

-----------------------------------------------------------------------------
--- Move system database files (except Master or Resources)
---
--- This involves multiple SEPARATE steps:
--- 1) ALTER DATABASE to define the new file location(s)
--- 2) Stop the SQL Service
--- 3) Physically move the database files to the new location
--- 4) Restart the SQL Service
--- 5) Verify the desired result
--- 6) Physically delete the ORIGINAL data file(s)
--- 7) Make a backup of MASTER database
---
-----------------------------------------------------------------------------

use MASTER
set NOCOUNT ON

---**************************************************************************
--- 1) Define new file location(s)
---**************************************************************************

ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'E:\Data\TempDB.mdf')
go
ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TempLog.ldf')
go

---**************************************************************************
-- Run from here and above first
---**************************************************************************

---**************************************************************************
--- 2) Stop the SQL Service
---**************************************************************************

---**************************************************************************
--- 3) Physically move the data file(s) to the new location (OS level)
--- (Optional for TempDB)
---**************************************************************************

---**************************************************************************
--- 4) Restart the SQL Service and any dependent services
--- (e.g., SQL Agent)
---**************************************************************************

---**************************************************************************
--- 5) Validate the change (Sanity Check)
---**************************************************************************

SELECT
name,
physical_name AS CurrentLocation,
state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'TempDB');

---**************************************************************************
--- 6) Physically delete the original data file(s) (OS level)
--- (Optional but a good idea!)
---**************************************************************************

---**************************************************************************
--- 6) Make a backup of the MASTER database
--- (Optional but a very good idea!)
---**************************************************************************

exec dbadmin..litespeed_full_backup 'Master', 'D:\SQLBACKUPS\DBImport1\'

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2010-02-25 : 20:05:03

Thanks Mr. Warrior and I got the requisite answer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources