Moving SQL Server System Databases

I was recently examining the configuration of
one of our production database servers. This server was involved in Merge
Replication. All the database files on this server were dumped on one small SAN drive. The replication cleanup job for some reason was
unable to delete the historical records from the system tables present in the
distribution database due to which all of a sudden the drive which contained
the data files as well as the log files got filled up hardly leaving any space
for the SQL Server to start. After a detailed examination of the system, I came
up with the following best practise recommendations:

  • Allocate a separate drive to hold the distribution.mdf file.
  • Allocate
    a separate drive to hold the distribution.ldf file.
  • Allocate
    a separate drive to hold the tempdb database. Since tempdb is a very high write
    intensive database therefore RAID 10 is the best option.
  • Allocate a separate drive to hold the system databases master,model and msdb

First we will look at moving the
system database in detail. For demonstration purposes, I am using SQL Server
2008 R2 and the instance name is INTEL/abc.

Moving the tempdb database

At present, the tempdb is  at the path F:\TempDB and I want to move the mdf and ldf
file to the paths named E:\Microsoft SQL Server\MSQL\Data and E:\Microsoft
SQL Server\MSQL\Log
respectively.

Connect to SSMS, open a new query window and select the tempb database as shown below:

Execute the below T-SQL query against the tempdb database to
get the logical file name of the data and log files of the tempdb database.

Select * from sysfiles

Please refer to the screen capture below:

As seen from the screen capture above, the logical name of
the data file of the tempdb database is tempdev whereas the logical name of the
log file of the tempdb database is templog.

Execute the below set of T-SQL statements against the tempdb
database.

ALTER DATABASE tempdb 
MODIFY FILE ( NAME ='tempdev', 
FILENAME = 'E:\Microsoft SQL Server\MSQL\Data\tempdev.mdf')
ALTER DATABASE tempdb 
MODIFY FILE ( NAME ='templog', 
FILENAME = 'E:\Microsoft SQL Server\MSQL\Log\templog.ldf')

In the first query, tempdev is the logical name of the data
file of tempdb database and E:\Microsoft SQL Server\MSQL\Data is the new path
to hold its data file. Also, templog is the logical name of the log
file of tempdb database and E:\Microsoft SQL Server\MSQL\log is the new path to
the log file.

Please refer to the screen capture below:

Re-start  SQL Server for changes to take effect. I always have a habit of
doing this using command prompt as shown below.

1. Stop
the SQL Server Services.
On the command prompt, type the following
command

NET STOP MSSQLSERVER

After executing this command,  SQL Server
Services are stopped.

Pages: 1 2




Array

One Response to “Moving SQL Server System Databases”

  1. Very well documented. Thanks for sharing.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |