Instance will not come up after moving resource db

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)

Instance will not come up after moving resource db

Author

Topic

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2008-05-20 : 12:42:34

after moving the resource database files with this command:

ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');
GO

I am getting this error trying to do anything in the instance while I have it started in minimal mode (/f /t3608)

(the instance WILL NOT COME UP UNLESS I USE THE /F /T3608 from a command promt.. please .. any suggestions??????????????????????
Also.. I checked and the primary file is NOT READ-ONLY.

File activation failure. The physical file name "E:\MSSQL\KOCSQLDEV01\Datafiles\mssqlsystemresource.ldf" may be incorrect.

The log cannot be rebuilt when the primary file is read-only.

File activation failure. The physical file name "E:\MSSQL\KOCSQLDEV01\Datafiles\mssqlsystemresource.ldf" may be incorrect.

The log cannot be rebuilt when the primary file is read-only.

Msg 945, Level 14, State 2, Line 1

Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2008-05-20 : 15:02:25

any suggestions? I'm a little leary to just uninstall the instance..???

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-20 : 15:15:46

Check this out:

http://www.sqlservercentral.com/Forums/Topic370246-146-2.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-20 : 15:15:50

To properly move the resource database, see this article:
http://msdn.microsoft.com/en-us/library/ms345408.aspx

You'll notice that it is a bit more involved than just ALTER DATABASE.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2008-05-21 : 08:41:06

I followed the document tara listed to the 'T'

What i FINALLY found was that when i updated to SP2, it create two new resource database files (mdf and ldf) in their original locations!!! I stopped all the services and moved the resource DB file to their correct location (where I had moved them to) and everything came up fine. Thanks for your support

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources