SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 SQL Server Service won't start
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jpotucek
Constraint Violating Yak Guru

USA
273 Posts

Posted - 03/17/2008 :  16:33:50  Show Profile  Send jpotucek an AOL message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 03/17/2008 :  16:36:59  Show Profile  Reply with Quote
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.
Go to Top of Page

jpotucek
Constraint Violating Yak Guru

USA
273 Posts

Posted - 03/17/2008 :  16:47:36  Show Profile  Send jpotucek an AOL message  Reply with Quote
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?
Go to Top of Page

jpotucek
Constraint Violating Yak Guru

USA
273 Posts

Posted - 03/17/2008 :  17:23:13  Show Profile  Send jpotucek an AOL message  Reply with Quote
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' )
Go to Top of Page

jpotucek
Constraint Violating Yak Guru

USA
273 Posts

Posted - 03/17/2008 :  17:52:02  Show Profile  Send jpotucek an AOL message  Reply with Quote
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
Go to Top of Page

jpotucek
Constraint Violating Yak Guru

USA
273 Posts

Posted - 03/17/2008 :  21:39:39  Show Profile  Send jpotucek an AOL message  Reply with Quote
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??
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 03/17/2008 :  22:55:09  Show Profile  Reply with Quote
Try alter database again if master db is up and you can open query window.
Go to Top of Page

mdubey
Posting Yak Master

USA
133 Posts

Posted - 05/27/2008 :  10:28:23  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.33 seconds. Powered By: Snitz Forums 2000