SQL Server 2005 Instance will not start

[jpotucek]

SQL Server 2005 Instance will not start after moving MSDB and MODEL data and log files.

SQL Server 2005 Standard edition SP3 and CU4 (x64) on Windows 2008R2

I installed 2 instances on this Server. I then proceeded to move the Master, Model and MSDB data and log files from their original location of E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data to the following locations:
F:\MSSQL\KOCSQL06\Default\DataFiles for the data files
G:\MSSQL\KOCSQL06\Default\LogFiles for the log files.

I moved master using the configuration manager and moved model and msdb using this SQL:

**MOve MSDB**
USE master;
GO
ALTER DATABASE MSDB
MODIFY FILE (NAME = msdbdata, FILENAME = 'F:\MSSQL\KOCSQL06\Default\Datafiles\msdbdata.mdf');
GO
ALTER DATABASE  MSDB
MODIFY FILE (NAME = msdblog, FILENAME = 'G:\MSSQL\KOCSQL06\Default\Logfiles\msdblog.ldf');
GO


**Move Model**
USE master;
GO
ALTER DATABASE MODEL
MODIFY FILE (NAME = modeldev, FILENAME = 'F:\MSSQL\KOCSQL06\Default\Datafiles\Model.mdf');
GO
ALTER DATABASE  MODEL
MODIFY FILE (NAME = modellog, FILENAME = 'G:\MSSQL\KOCSQL06\Default\Logfiles\modellog.ldf');
GO

I did not touch the mssqlsystemresrouce database data and log files - they remained in their original install location of E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data

The instance started fine and I went on my way.

This is where I got into trouble... I read that that it's not a good to move the system databases in SQL Server 2005 - mainly because of the mssqlsystemresource database - and I proceeded to move them back.

I tackled MODEL and MSDB first.

I used this SQL to try and move them back

**Move MSDB**
USE master;
GO
ALTER DATABASE MSDB
MODIFY FILE (NAME = msdbdata, FILENAME = E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdbdata.mdf');
GO
ALTER DATABASE  MSDB
MODIFY FILE (NAME = msdblog, FILENAME = E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdblog.ldf');
GO


**Move Model**
USE master;
GO
ALTER DATABASE MODEL
MODIFY FILE (NAME = modeldev, FILENAME = 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data');
GO
ALTER DATABASE  MODEL
MODIFY FILE (NAME = modellog, FILENAME = 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data');
GO

Stopped the instance, moved the files and attempted to start the instance. It will not start.

getting this in the error log:

2011-04-12 20:49:54.73 Server      Microsoft SQL Server 2005 - 9.00.4226.00 (X64) 
	May 26 2009 14:58:11 
	Copyright (c) 1988-2005 Microsoft Corporation
	Standard Edition (64-bit) on Windows NT 6.1 (Build 7600: )

2011-04-12 20:49:54.73 Server      (c) 2005 Microsoft Corporation.
2011-04-12 20:49:54.73 Server      All rights reserved.
2011-04-12 20:49:54.73 Server      Server process ID is 10480.
2011-04-12 20:49:54.73 Server      Authentication mode is MIXED.
2011-04-12 20:49:54.73 Server      Logging SQL Server messages in file 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2011-04-12 20:49:54.73 Server      This instance of SQL Server last reported using a process ID of 11112 at 4/12/2011 8:12:36 PM (local) 4/13/2011 12:12:36 AM (UTC). This is an informational message only; no user action is required.
2011-04-12 20:49:54.73 Server      Registry startup parameters:
2011-04-12 20:49:54.73 Server      	 -d F:\MSSQL\KOCSQL06\Default\DataFiles\master.mdf
2011-04-12 20:49:54.73 Server      	 -e E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2011-04-12 20:49:54.73 Server      	 -l G:\MSSQL\KOCSQL06\Default\Logfiles\mastlog.ldf
2011-04-12 20:49:54.74 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2011-04-12 20:49:54.74 Server      Detected 24 CPUs. This is an informational message; no user action is required.
2011-04-12 20:49:54.84 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2011-04-12 20:49:54.84 Server      Lock partitioning is enabled.  This is an informational message only. No user action is required.
2011-04-12 20:49:54.86 Server      Multinode configuration: node 0: CPU mask: 0x0000000000fff000 Active CPU mask: 0x0000000000fff000. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2011-04-12 20:49:54.86 Server      Multinode configuration: node 1: CPU mask: 0x0000000000000fff Active CPU mask: 0x0000000000000fff. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2011-04-12 20:49:54.86 Server      Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2011-04-12 20:49:55.87 Server      Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2011-04-12 20:49:55.88 Server      Database mirroring has been enabled on this instance of SQL Server.
2011-04-12 20:49:55.88 spid7s      Starting up database 'master'.
2011-04-12 20:49:56.01 spid7s      SQL Trace ID 1 was started by login "sa".
2011-04-12 20:49:56.02 spid7s      Starting up database 'mssqlsystemresource'.
2011-04-12 20:49:56.03 spid7s      The resource database build version is 9.00.4226. This is an informational message only. No user action is required.
2011-04-12 20:49:56.31 spid11s     Starting up database 'model'.
2011-04-12 20:49:56.31 spid7s      Server name is 'KOCSQL06'. This is an informational message only. No user action is required.
2011-04-12 20:49:56.31 spid11s     Error: 17207, Severity: 16, State: 1.
2011-04-12 20:49:56.31 spid11s     FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'. Diagnose and correct the operating system error, and retry the operation.
2011-04-12 20:49:56.31 spid11s     Error: 17204, Severity: 16, State: 1.
2011-04-12 20:49:56.31 spid11s     FCB::Open failed: Could not open file E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data for file number 1.  OS error: 5(Access is denied.).
2011-04-12 20:49:56.31 spid11s     Error: 5120, Severity: 16, State: 101.
2011-04-12 20:49:56.31 spid11s     Unable to open the physical file "E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data". Operating system error 5: "5(Access is denied.)".
2011-04-12 20:49:56.32 spid11s     Error: 17207, Severity: 16, State: 1.
2011-04-12 20:49:56.32 spid11s     FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'. Diagnose and correct the operating system error, and retry the operation.
2011-04-12 20:49:56.32 spid11s     Error: 17204, Severity: 16, State: 1.
2011-04-12 20:49:56.32 spid11s     FCB::Open failed: Could not open file E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data for file number 2.  OS error: 5(Access is denied.).
2011-04-12 20:49:56.32 spid11s     Error: 5120, Severity: 16, State: 101.
2011-04-12 20:49:56.32 spid11s     Unable to open the physical file "E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data". Operating system error 5: "5(Access is denied.)".
2011-04-12 20:49:56.32 spid11s     File activation failure. The physical file name "E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data" may be incorrect.
2011-04-12 20:49:56.32 spid11s     Error: 945, Severity: 14, State: 2.
2011-04-12 20:49:56.32 spid11s     Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
2011-04-12 20:49:56.32 spid11s     Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2011-04-12 20:49:56.33 spid11s     SQL Trace was stopped due to server shutdown. Trace ID = '1'.

It looks like it's choking with a permissions issue on the model log file?
Here's what I have done:
Checked the permissions on the Model and MSDB data and log files so make sure they weren't read only.
Made sure that the Domain account that starts the services has Adminstrative rights to the Server.
I added the following account to have FULL CONTROL at the root of all drives on the Server (except c KOCSQL06\SQLServer2005MSSQLUser$KOCSQL06$OVOPS

the instance will still not start! I can start it in single user mode NET START MSSQLSERVER /c /m /T3608 and i was thinking of trying to restore Model and MSDB but I don't want to make things worse than they already are.

Does anyone have any ideas on how to fix this?

 

[jpotucek]

Looks like I had a typo in my Alter Database commands.


I started up in single user mode and reissued my commands correctly like this and the instance started up just fine:

USE master;
GO
ALTER DATABASE MSDB
MODIFY FILE (NAME = msdbdata, FILENAME = 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdbdata.mdf');
GO
ALTER DATABASE MSDB
MODIFY FILE (NAME = msdblog, FILENAME = 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdblog.ldf');
GO



USE master;
GO
ALTER DATABASE MODEL
MODIFY FILE (NAME = modeldev, FILENAME = 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Model.mdf');
GO
ALTER DATABASE MODEL
MODIFY FILE (NAME = modellog, FILENAME = 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\modellog.ldf');
GO

 

[jpotucek]

Looks like I had a typo in my Alter Database commands.


I started up in single user mode and reissued my commands correctly like this and the instance started up just fine:

USE master;
GO
ALTER DATABASE MSDB
MODIFY FILE (NAME = msdbdata, FILENAME = 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdbdata.mdf');
GO
ALTER DATABASE MSDB
MODIFY FILE (NAME = msdblog, FILENAME = 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdblog.ldf');
GO



USE master;
GO
ALTER DATABASE MODEL
MODIFY FILE (NAME = modeldev, FILENAME = 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Model.mdf');
GO
ALTER DATABASE MODEL
MODIFY FILE (NAME = modellog, FILENAME = 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\modellog.ldf');
GO