SQL Server Service won't start

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL Server Service won't start

Author

Topic

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2008-03-17 : 16:33:50

I successfully installed SQL Server 2005 and then used the ALTER DATATBASE statement to move around my data and log files for Model, MSDB and TEMPDB. I stopped SQL SERVER Services and physically moved the files to their new locations.. now the service won't start? I get this in the SQL Server ERROLOG:

Starting up database 'model'.
2008-03-17 15:57:01.29 spid9s Error: 17207, Severity: 16, State: 1.
2008-03-17 15:57:01.29 spid9s FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'E:\MSSQL\AML\DataFiles'. Diagnose and correct the operating system error, and retry the operation.
2008-03-17 15:57:01.29 spid9s Error: 17204, Severity: 16, State: 1.
2008-03-17 15:57:01.29 spid9s FCB::Open failed: Could not open file E:\MSSQL\AML\DataFiles for file number 1. OS error: 5(Access is denied.).
2008-03-17 15:57:01.29 spid4s Server name is 'KOCSQLDEV01\AML'. This is an informational message only. No user action is required.
2008-03-17 15:57:01.29 spid9s Error: 5120, Severity: 16, State: 101.
2008-03-17 15:57:01.29 spid9s Unable to open the physical file "E:\MSSQL\AML\DataFiles". Operating system error 5: "5(Access is denied.)".
2008-03-17 15:57:01.29 spid9s Error: 17207, Severity: 16, State: 1.
2008-03-17 15:57:01.29 spid9s FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'F:\MSSQL\AML\LogFiles'. Diagnose and correct the operating system error, and retry the operation.
2008-03-17 15:57:01.29 spid9s Error: 17204, Severity: 16, State: 1.

I can log on to the Server with the account that starts the services and open the .mdf and .ldf files in Notepad.. not sure what's going on.. I then gave the Domain Account that starts the Services Full Control on each of the volumes that I moved the files to.. I checked down to the file level to make sure that the permissions propogated.. the service still won't start.. can someone help?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-17 : 16:36:59

Why did you use Alter database to move log and data files? Didn't you have an option to locate data files and log files in whichever drives you specify while installing ? Should be really careful while system databases.

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2008-03-17 : 16:47:36

When installing.. You can specify where to locate your Data and log files.. but it still puts the system DBs in the install folder. I have a requirement to seperate all of the data and log file and put ther tempdb on it's own seperate drive. I followed BOL to the letter (or so I thought)
http://msdn2.microsoft.com/en-us/library/ms345408

anyone ever run into this before?

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2008-03-17 : 17:23:13

this is the SQL I used to specify the new file locations:

ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBData , FILENAME = 'E:\MSSQL\AML\DataFiles' )
ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBLog , FILENAME = 'F:\MSSQL\AML\LogFiles' )
ALTER DATABASE Model MODIFY FILE ( NAME = ModelDEV , FILENAME = 'E:\MSSQL\AML\DataFiles' )
ALTER DATABASE Model MODIFY FILE ( NAME = ModelLog , FILENAME = 'F:\MSSQL\AML\LogFiles' )
ALTER DATABASE TempDB MODIFY FILE ( NAME = TempDEV , FILENAME = 'G:\MSSQL\AML\TEMPDB' )
ALTER DATABASE TempDB MODIFY FILE ( NAME = TempLOG , FILENAME = 'G:\MSSQL\AML\TEMPDB' )

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2008-03-17 : 17:52:02

now I'm really confused. I was able to start in recovery mode from a command prompt

net start mssql$instancename /f /t3608

it did not create the tempdb files

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2008-03-17 : 21:39:39

I see what I did now..

in my code.. I gave it the location but NOT the actual filenames !!!!
how dumb!!!

ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBData , FILENAME = 'E:\MSSQL\AML\DataFiles' )
ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBLog , FILENAME = 'F:\MSSQL\AML\LogFiles' )
ALTER DATABASE Model MODIFY FILE ( NAME = ModelDEV , FILENAME = 'E:\MSSQL\AML\DataFiles' )
ALTER DATABASE Model MODIFY FILE ( NAME = ModelLog , FILENAME = 'F:\MSSQL\AML\LogFiles' )
ALTER DATABASE TempDB MODIFY FILE ( NAME = TempDEV , FILENAME = 'G:\MSSQL\AML\TEMPDB' )
ALTER DATABASE TempDB MODIFY FILE ( NAME = TempLOG , FILENAME = 'G:\MSSQL\AML\TEMPDB' )

Does ANYONE know how I can fix this??

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-17 : 22:55:09

Try alter database again if master db is up and you can open query window.

mdubey
Posting Yak Master

133 Posts

Posted - 2008-05-27 : 10:28:23

Please give the full path name.

for ex.

ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBData , FILENAME = 'E:\MSSQL\AML\DataFiles\<filename.mdf>' )
ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBLog , FILENAME = 'F:\MSSQL\AML\LogFiles\<Filename.ldf>' )

Manoj
MCP, MCTS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources