I’ve recently had to work with our IT Department in changing the layout of the SQL Server instance created when they installed Service Manager – at the time of the install the SAN was offline so everything ended up being dumped on the C:\ drive. After some very poor performance from Service Manager they asked me to look at why it was running so slowly. Documented below are the steps necessary to move database and logfile locations in case you are presented with the same issues.
- Get the current physical and logical locations
USE master
GO
SELECT name AS LogicalFileName, physical_name AS FileLocation , state_desc AS Status
FROM sys.master_files
WHERE database_id = DB_ID(‘ServiceManager’);
which gave me:
LogicalFileName FileLocation Status
SM_DATA C:\Program Files\Microsoft SQL Server\MSSQL11.SCSMR2\MSSQL\DATA\ServiceManager.mdf ONLINE
SM_LOG C:\Program Files\Microsoft SQL Server\MSSQL11.SCSMR2\MSSQL\DATA\ServiceManagerlog.ldf ONLINE
2. Take the database offline:
USE master
GO
ALTER DATABASE ServiceManager SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
3. Move the data and log files physically to their new locations using the Windows OS
4. Use ALTER DATABASE to MODIFY the filename for each file that has been moved – anly one file can be done at a time
USE master
GO
ALTER DATABASE ServiceManager
MODIFY FILE
( NAME = SM_DATA,
FILENAME = ‘E:\Microsoft SQL Server\MSSQL11.SCSMR2\MSSQL\DATA\ServiceManager.mdf’); — New file path
USE master
GO
ALTER DATABASE ServiceManager
MODIFY FILE
( NAME = SM_LOG,
FILENAME = ‘F:\MSSQL11.SCSMR2\MSSQL\DATA\ServiceManagerlog.ldf’); — New file path
5. Set the DATABASE ONLINE
USE master
GO
ALTER DATABASE ServiceManager SET ONLINE;
6.Verify the new physical locations
USE master
GO
SELECT name AS FileName, physical_name AS CurrentFileLocation, state_desc AS Status
FROM sys.master_files
WHERE database_id = DB_ID(‘ServiceManager’);
