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.