Tuesday 2 June 2015

How to move TempDB files from one drive to another

1.  Get Current TempDB Files Location

Use following code to get the current TempDB files location:  

USE TempDB
GO
sp_HelpFile
GO



2. Verify New File Location

Before moving files to new location, verify that SQL Server has access to the new location. I will use "T:\MSSQL\Data" as new file location.

3. Use T-SQL command to specify new location:

ALTER DATABASE TempDB
MODIFY FILE (NAME = tempdev, FILENAME = 'T:\MSSQL\Data\tempdb.mdf');
GO 

ALTER DATABASE TempDB
MODIFY FILE (NAME = templog, FILENAME = 'T:\MSSQL\Data\templog.ldf'); 
GO

4. Restart SQL Server Services

SQL Serve will not use new file location unless you restart SQL Server Services. Once you restart the SQL Services, you can delete the old files

No comments:

Post a Comment

How to find table row count?

--Use below query to find table row count select so.name,sp.rows from sys.objects so inner join sys.partitions sp on so.object_id = sp.obj...