Create tempdb partitions and move to new location location.
Know existing location:
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');
Move to new location:
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = "C:\SQLDB\tempdb.mdf")
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = "C:\SQLDB\templog.ldf")
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev1, FILENAME = "C:\SQLDB\tempdb1.mdf")
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog1, FILENAME = "C:\SQLDB\templog1.ldf")
Note:
Also verify that SQL service account has full access to new location.
To move msdb, need to follow steps below after executing above queries.
1. Go to SQL Server configuration manager.
Know existing location:
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');
Move to new location:
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = "C:\SQLDB\tempdb.mdf")
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = "C:\SQLDB\templog.ldf")
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev1, FILENAME = "C:\SQLDB\tempdb1.mdf")
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog1, FILENAME = "C:\SQLDB\templog1.ldf")
Note:
Also verify that SQL service account has full access to new location.
To move msdb, need to follow steps below after executing above queries.
1. Go to SQL Server configuration manager.
2. Update path on parameters.
No comments:
Post a Comment