SQL Server 2005 cluster post installation – move log file

By default, when you are installing a new SQL Server 2005 cluster, the installer will install data file and log file at the same drive. So one of the key post-installation steps, is to move those system database log files to log drive, and make sure all user database will also save their log to log drive later.

Bind Log Drive to Resource Group

Before you move the log files, first you have to bind the log drive with your SQL Server cluster instance. Because SQL Server installer only bind one drive with SQL Server instance. Follow steps below:

  1. You need go to “Cluster Administrator” MMC, find the resource group of new installed SQL Server cluster instance.
  2. Right click the resource group, click “Take offline”.
  3. Double click the “SQL Server (instance name)” service resource. On the “Dependencies” tab, add your log drive. If you cannot find the log drive, then you may need to add it into resource group first.
  4. Then right click the resource group, and take it back online.

Modify Default Path of Data File and Log File

After we bind the log drive (we call it as Y drive here), we need modify SQL Server instance settings to move default log. You can use SSMS, or you can also use extendend procedure “xp_instance_regweite” if you prefer scripting.

To use SSMS, follow steps below:

  1. Right click the instance in object explorer and click “Properties”.
  2. Select “Database Settings” tab.
  3. Modify the “Data” and “Log” path in “Database default locations” section. (We use “Z:\Microsoft SQL Server\MSSQL.1\MSSQL\Data” as data path, and “Y:\Microsoft SQL Server\MSSQL.1\MSSQL\Log” as log path here.)
  4. Click “OK” to save your settings.

To use extend procedure “xp_instance_regwrite”, use the script here:

USE [master]
EXEC xp_instance_regwrite
N’Y:\Microsoft SQL Server\MSSQL.1\MSSQL\Log’

After you perform steps above, your data file and log file will go to the path you set by default. Which means, when you use “CREATE DATABASE XXX” without specify the file path, one data file and one log file will be created in the directory path you set.

Move Log File of System Databases

So now we need to move log file of system database. Maybe you will say “OK. System database is not so heavily used and not so big. So leave them.” Actually you shouldn’t. Because tempdb can not huge and very busy.

So first we need to move system databases. By default, there are only 4 system databases after fresh installation, master, msdb, tempdb, model. And one hidden resource database mssqlsystemresource.

First we need to run following script to move log files of msdb, tempdb, model database.

FILE (NAME = modellog , FILENAME = ‘Y:\Microsoft SQL Server\MSSQL.1\MSSQL\Log\modellog.ldf’)

FILE (NAME = msdblog , FILENAME = ‘Y:\Microsoft SQL Server\MSSQL.1\MSSQL\Log\msdblog.ldf’)

FILE (NAME = templog , FILENAME = ‘Y:\Microsoft SQL Server\MSSQL.1\MSSQL\Log\templog.ldf’)

Now the log file are still remains at orginal place, which at the same directory with data file. We need restart to make it effect. So we use MMC “Cluster Administrator” to take resouce group offline. Move log files of all system databases to log drive, include master database. Then use the MMC to take resource group online again. Leave the log file of resource database with data file. Don’t move it.

Modify Instance Startup Parameter

Now we need to modify SQL Server instance startup parameters, point the lgo file of master database to the new place. Follow steps below:

  1. Keep resource group online, then open “SQL Server Configuration Manager”.
  2. Double click the SQL Server service and select “Advanced” tab.
  3. Modify the -l parameter in “Startup Parameters” value. One expample here:
    Before modify
    -dZ:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eZ:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lZ:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
    Modify it to
    -dZ:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eZ:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lY:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\mastlog.ldf
  4. Click “OK” to save your settings.
  5. Then use MMC “Cluster Administrator” to take the resource group back online.

Move Resource Database Log File

Now we need to move the resource database log file. Follow steps below:

  1. Open a command prompt.
  2. Execute command “NET START MSSQL$ISNT1 /f -T3608”. This is a master database only recovery mode. In this mode, you can access the resource database.
  3. In SSMS or use sqlcmd, execute command

ALTER DATABASE mssqlsystemresource MODIFY
FILE (NAME=log, FILENAME= ‘Y:\Microsoft SQL Server\MSSQL.1\MSSQL\Log\mssqlsystemresource.ldf’)

ALTER DATABASE mssqlsystemresource SET READ_ONLY

After this we need stop SQL Server instance, with command “NET STOP MSSQL$INST1”. Then move resource database log file to log  drive. And use command “NET START MSSQL$INST1” to start instance again. Once confirmed instance can be start up, then stop it again. And use MMC “Cluster Administrator” to take the resource group back online. You will see all log files are in log drive now.

Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: