SQL Server 2005 Instance will not 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 2005 Instance will not start

Author

Topic

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2011-04-13 : 07:26:18

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-13 : 08:07:15

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

Isn't data the folder name rather than the filename?
If there is a file named data then this is giving the same name to the data and log file so whichever gets opened first will be locked when the other tries to open it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2011-04-13 : 08:42:46

I started the instance in single user mode and ran this:

SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id < 5
here are the results.
master F:\MSSQL\\Default\DataFiles\master.mdf
mastlog G:\MSSQL\\Default\Logfiles\mastlog.ldf
tempdev H:\MSSQL\\Default\TEMPDB\tempdb.mdf
templog H:\MSSQL\Default\TEMPDB\templog.ldf
modeldev E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data
modellog E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data
MSDBData E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data
MSDBLog E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data

you're right.. when i tried to move them back i did not specify filenames for the model and msdb.

I started up in single user mode and reissued my commands:

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

The instance started with no problem!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources