Friday, January 30, 2015

Moving the tempdb database

You don't actually have to move the tempdb files.  You just need to change the path to a new location and then restart SQL Server.  The files will automatically be rebuilt in th new location and you can delete the old files.

1. Verify logical name and physical path of the tempdb files
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

2. Change the tempdb paths
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'D:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\templog.ldf')
GO

3. Verify the paths
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

4. Check for any open connections before we bounce SQL
SELECT DB_NAME(dbid) as DBName,
       COUNT(dbid) as NumberOfConnections,
       loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

5. Bounce SQL

6. Use windows explorer to delete the old tempdb files


No comments:

Post a Comment

SQL 2022 TSQL snapshot backups!

  SQL 2022 now actually supports snapshot backups!  More specifically, T-SQL snapshot backups. Of course this is hardware-dependent. Here ...