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




Related Articles :

  • No Related Articles Found

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 |