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