This article discusses how to improve SQL server performance by moving database files to separate drives.

Summary

On occasions customers can experience performance issues with the database as a result of increased I/O operations. I/O operations with database files include the data file and the log file. In this case for Analyst databases most customers have two files, skfuser.mdf and skfuser_log.ldf.

Details

The example and steps below describe moving a log file (the skfuser log file) from a location on “C” drive to one on “D” drive. The example can be easily adapted for a data (mdf) file.

Prerequisites

• Stop all services connecting to skfuser database or Analyst. Services include Transaction Server, IMX service, etc.
• Display the location and status of the skfuser files before processing any changes, using:

SELECT name, physical_name AS NewLocation, state_desc AS OnlineStatus
FROM sys.master_files
WHERE database_id = DB_ID(N'skfuser')

Note:
Please execute each statement in the steps below separately, before proceeding with the next step.

Step 1

• Alter the logical file location within SQL Server to match the path of the physical location where the log file is going to be placed. To do this use:

ALTER DATABASE skfuser
MODIFY FILE ( NAME = skfuser_log, FILENAME = 'D:\DatabaseLogFiles\skfuser_log.ldf')

Step 2

• Disconnect the skfuser database from all external activities by taking it offline, using:

ALTER DATABASE skfuser SET OFFLINE

Step 3

• With the database offline, move the skfuser_log.ldf file to the new location:

Drag and drop (or cut and paste) skfuser_log.ldf to the location specified in step 1.

Step 4

• Bring database back online for the changes to take effect and so users can resume normal activities:

ALTER DATABASE skfuser SET ONLINE

• Verify that the file move was successful by displaying current location and status in SQL Server:

SELECT name, physical_name AS NewLocation, state_desc AS OnlineStatus
FROM sys.master_files
WHERE database_id = DB_ID(N'skfuser')

For further assistance, please contact the Technical Support Group by phone at 1-800-523-7514 option 8, or by e-mail at [e-mail].