How to move TempDB files from one drive to another
Recently I had a request to move TempDB files from one drive to another because of space issues. I would want to share the knowledge throgh this article.
As far as Production environment is concern, this task should be taken care by DBA. Albeit, you can do it in Development environment. Below are the steps to transfer TempDB files:
1. Get Current TempDB Files Location
Use following code to get the current TempDB files location:
USE TempDB
GO
sp_HelpFile
GO
You might need these files if something goes wrong.
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 (mentioned in Step 1).
Recently I had a request to move TempDB files from one drive to another because of space issues. I would want to share the knowledge throgh this article.
As far as Production environment is concern, this task should be taken care by DBA. Albeit, you can do it in Development environment. Below are the steps to transfer TempDB files:
1. Get Current TempDB Files Location
Use following code to get the current TempDB files location:
USE TempDB
GO
sp_HelpFile
GO
You might need these files if something goes wrong.
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 (mentioned in Step 1).