CANNOT START SQL SERVER Service - 18052 NEED HELP here !

Hi ,
This is refering to my previous mail " moving master db to another location"
and really need advise as i messed up the SQL Server !!
below is the series of steps & checks i have made from my earlier postings
1) +++++++++++++++++
Hi ,
I was trying to move both msdb & model using the following steps :
1. addede the parameter -T3608
2a. use master
go
sp_detach_db 'msdb'
go
2b. use master
go
sp_detach_db 'model'
go
3. stop & restart SQL SERVER
4. i could not restart the SQL SERVER Service from the SQL Server Service
Manager as it keeps restarting & stopping by itself
any way i can get it to work now ?
tks & rdgs
2) ++++++++++++++++=
Hi ,
I got the 18502 error (looked into past posting seems no solution)
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 5/29/2006
Time: 6:11:53 PM
User: N/A
Computer: ALTIRISSERVER
Description:
18052 :
Error: 823, Severity: 24, State: 2.
and
from the SQL Error Log i got the followings :
2006-05-29 18:11:51.41 server Microsoft SQL Server 2000 - 8.00.760 (Intel
X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2006-05-29 18:11:51.42 server Copyright (C) 1988-2002 Microsoft
Corporation.
2006-05-29 18:11:51.42 server All rights reserved.
2006-05-29 18:11:51.42 server Server Process ID is 2268.
2006-05-29 18:11:51.42 server Logging SQL Server messages in file 'd:\ms
sqlserver\MSSQL\log\ERRORLOG'.
2006-05-29 18:11:51.42 server SQL Server is starting at priority class
'high'(2 CPUs detected).
2006-05-29 18:11:51.44 server Performance monitor shared memory setup
failed: -1
2006-05-29 18:11:51.53 server SQL Server configured for thread mode
processing.
2006-05-29 18:11:51.53 server Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks.
2006-05-29 18:11:51.57 server Attempting to initialize Distributed
Transaction Coordinator.
2006-05-29 18:11:52.60 spid4 Starting up database 'master'.
2006-05-29 18:11:53.85 spid4 Error: 823, Severity: 24, State: 2.
2006-05-29 18:11:53.85 spid4 Cannot recover the master database. Exiting.
Any solutions besidess re-install ?
tks & rdgs
3 +++++++++++++++
Hi ,
further info :
I have checked the registry for the Master db the path is as per the
startup parameters before i moved the 'model' and 'msdb' database
really appreciate all advise
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1> 3 +++++++++++++++
> Hi ,
> further info :
> I have checked the registry for the Master db the path is as per the
> startup parameters before i moved the 'model' and 'msdb' database
The startup parameters must be changed to reflect the new master file
locations in order for SQL Server to start.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60f93fb49965d@.uwe...
> Hi ,
> This is refering to my previous mail " moving master db to another
> location"
> and really need advise as i messed up the SQL Server !!
> below is the series of steps & checks i have made from my earlier postings
> 1) +++++++++++++++++
> Hi ,
> I was trying to move both msdb & model using the following steps :
> 1. addede the parameter -T3608
> 2a. use master
> go
> sp_detach_db 'msdb'
> go
> 2b. use master
> go
> sp_detach_db 'model'
> go
> 3. stop & restart SQL SERVER
> 4. i could not restart the SQL SERVER Service from the SQL Server Service
> Manager as it keeps restarting & stopping by itself
> any way i can get it to work now ?
> tks & rdgs
>
>
> 2) ++++++++++++++++=> Hi ,
> I got the 18502 error (looked into past posting seems no solution)
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 5/29/2006
> Time: 6:11:53 PM
> User: N/A
> Computer: ALTIRISSERVER
> Description:
> 18052 :
> Error: 823, Severity: 24, State: 2.
> and
> from the SQL Error Log i got the followings :
> 2006-05-29 18:11:51.41 server Microsoft SQL Server 2000 - 8.00.760
> (Intel
> X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2006-05-29 18:11:51.42 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2006-05-29 18:11:51.42 server All rights reserved.
> 2006-05-29 18:11:51.42 server Server Process ID is 2268.
> 2006-05-29 18:11:51.42 server Logging SQL Server messages in file
> 'd:\ms
> sqlserver\MSSQL\log\ERRORLOG'.
> 2006-05-29 18:11:51.42 server SQL Server is starting at priority class
> 'high'(2 CPUs detected).
> 2006-05-29 18:11:51.44 server Performance monitor shared memory setup
> failed: -1
> 2006-05-29 18:11:51.53 server SQL Server configured for thread mode
> processing.
> 2006-05-29 18:11:51.53 server Using dynamic lock allocation. [2500]
> Lock
> Blocks, [5000] Lock Owner Blocks.
> 2006-05-29 18:11:51.57 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2006-05-29 18:11:52.60 spid4 Starting up database 'master'.
> 2006-05-29 18:11:53.85 spid4 Error: 823, Severity: 24, State: 2.
> 2006-05-29 18:11:53.85 spid4 Cannot recover the master database.
> Exiting.
> Any solutions besidess re-install ?
> tks & rdgs
>
>
> 3 +++++++++++++++
> Hi ,
> further info :
> I have checked the registry for the Master db the path is as per the
> startup parameters before i moved the 'model' and 'msdb' database
> really appreciate all advise
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hi ,
But i did not move the Master file i moved only 'model' and 'msdb' . will
this affect the master db's parameter as well ?
tks & rdgs
Dan Guzman wrote:
>> 3 +++++++++++++++
>[quoted text clipped - 4 lines]
>> I have checked the registry for the Master db the path is as per the
>> startup parameters before i moved the 'model' and 'msdb' database
>The startup parameters must be changed to reflect the new master file
>locations in order for SQL Server to start.
>> Hi ,
>[quoted text clipped - 95 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Moving model and msdb files will not affect the master database file
locations but you mentioned moving master in your other thread and it looks
to me like this problem may be related.
Check to ensure the master database files are at the locations specified by
the registry startup parameters.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60f9d688bc36d@.uwe...
> Hi ,
> But i did not move the Master file i moved only 'model' and 'msdb' . will
> this affect the master db's parameter as well ?
>
> tks & rdgs
> Dan Guzman wrote:
>> 3 +++++++++++++++
>>[quoted text clipped - 4 lines]
>> I have checked the registry for the Master db the path is as per the
>> startup parameters before i moved the 'model' and 'msdb' database
>>The startup parameters must be changed to reflect the new master file
>>locations in order for SQL Server to start.
>> Hi ,
>>[quoted text clipped - 95 lines]
>> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hi,
That was my intention to move master to another location but before i could
do that i wasn't able to start SQL server service after moving both the model
& msdb db to a new location at the same time
tks & rdgs
Dan Guzman wrote:
>Moving model and msdb files will not affect the master database file
>locations but you mentioned moving master in your other thread and it looks
>to me like this problem may be related.
>Check to ensure the master database files are at the locations specified by
>the registry startup parameters.
>> Hi ,
>[quoted text clipped - 17 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hi ,
This is the latest details from the Error Log :
2006-05-29 22:18:11.46 server Microsoft SQL Server 2000 - 8.00.760 (Intel
X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2006-05-29 22:18:11.46 server Copyright (C) 1988-2002 Microsoft
Corporation.
2006-05-29 22:18:11.46 server All rights reserved.
2006-05-29 22:18:11.46 server Server Process ID is 1672.
2006-05-29 22:18:11.46 server Logging SQL Server messages in file 'd:\ms
sqlserver\MSSQL\log\ERRORLOG'.
2006-05-29 22:18:11.47 server SQL Server is starting at priority class
'high'(2 CPUs detected).
2006-05-29 22:18:11.61 server SQL Server configured for thread mode
processing.
2006-05-29 22:18:11.61 server Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks.
2006-05-29 22:18:11.64 server Attempting to initialize Distributed
Transaction Coordinator.
2006-05-29 22:18:11.67 server Failed to obtain
TransactionDispenserInterface: Result Code = 0x8004d01b
2006-05-29 22:18:11.67 spid3 Starting up database 'master'.
2006-05-29 22:18:11.83 server Using 'SSNETLIB.DLL' version '8.0.766'.
2006-05-29 22:18:11.85 server SQL server listening on 10.224.1.9: 1433.
2006-05-29 22:18:11.85 server SQL server listening on 127.0.0.1: 1433.
2006-05-29 22:18:11.85 spid3 Server name is 'ALTIRISSERVER'.
2006-05-29 22:18:11.85 spid8 Starting up database 'Altiris'.
2006-05-29 22:18:11.86 spid10 Starting up database 'Altiris_Incidents'.
2006-05-29 22:18:11.86 spid9 Starting up database 'SidataSQL_DC'.
2006-05-29 22:18:11.86 spid11 Starting up database 'ScalaDB_SGO_Restore'.
2006-05-29 22:18:11.86 spid12 Starting up database 'ScalaDB_SGO_TEST'.
2006-05-29 22:18:11.86 spid5 Could not find database ID 3. Database may
not be activated yet or may be in transition.
which database does ID 3 refers to ?
tks & rdgs
maxzsim wrote:
>Hi,
> That was my intention to move master to another location but before i could
>do that i wasn't able to start SQL server service after moving both the model
>& msdb db to a new location at the same time
>tks & rdgs
>>Moving model and msdb files will not affect the master database file
>>locations but you mentioned moving master in your other thread and it looks
>[quoted text clipped - 8 lines]
>> tks & rdgs
--
Message posted via http://www.sqlmonster.com|||Error 823 is usually preceded by a detailed message about the cause of the
I/O error. Do you have any other related messages in the event log? Are
you moving these database files because a disk is going bad?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60fa3a34e6e1e@.uwe...
> Hi,
> That was my intention to move master to another location but before i
> could
> do that i wasn't able to start SQL server service after moving both the
> model
> & msdb db to a new location at the same time
> tks & rdgs
> Dan Guzman wrote:
>>Moving model and msdb files will not affect the master database file
>>locations but you mentioned moving master in your other thread and it
>>looks
>>to me like this problem may be related.
>>Check to ensure the master database files are at the locations specified
>>by
>>the registry startup parameters.
>> Hi ,
>>[quoted text clipped - 17 lines]
>> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||The model database must be assigned dbid 3. Perhaps dbid 3 was not assigned
correctly either because of the other problems or because you forgot to
remove the 3608 trace flag.
Start SQL Server in minimal configuration mode (-f parameter) and run the
following query
SELECT dbid
FROM master..sysdatabases
WHERE name = 'model'
If model reports a dbid other than 3, repeat the model database move steps
in minimal configuration mode.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60fa4c757d28d@.uwe...
> Hi ,
> This is the latest details from the Error Log :
> 2006-05-29 22:18:11.46 server Microsoft SQL Server 2000 - 8.00.760
> (Intel
> X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2006-05-29 22:18:11.46 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2006-05-29 22:18:11.46 server All rights reserved.
> 2006-05-29 22:18:11.46 server Server Process ID is 1672.
> 2006-05-29 22:18:11.46 server Logging SQL Server messages in file
> 'd:\ms
> sqlserver\MSSQL\log\ERRORLOG'.
> 2006-05-29 22:18:11.47 server SQL Server is starting at priority class
> 'high'(2 CPUs detected).
> 2006-05-29 22:18:11.61 server SQL Server configured for thread mode
> processing.
> 2006-05-29 22:18:11.61 server Using dynamic lock allocation. [2500]
> Lock
> Blocks, [5000] Lock Owner Blocks.
> 2006-05-29 22:18:11.64 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2006-05-29 22:18:11.67 server Failed to obtain
> TransactionDispenserInterface: Result Code = 0x8004d01b
> 2006-05-29 22:18:11.67 spid3 Starting up database 'master'.
> 2006-05-29 22:18:11.83 server Using 'SSNETLIB.DLL' version '8.0.766'.
> 2006-05-29 22:18:11.85 server SQL server listening on 10.224.1.9: 1433.
> 2006-05-29 22:18:11.85 server SQL server listening on 127.0.0.1: 1433.
> 2006-05-29 22:18:11.85 spid3 Server name is 'ALTIRISSERVER'.
> 2006-05-29 22:18:11.85 spid8 Starting up database 'Altiris'.
> 2006-05-29 22:18:11.86 spid10 Starting up database 'Altiris_Incidents'.
> 2006-05-29 22:18:11.86 spid9 Starting up database 'SidataSQL_DC'.
> 2006-05-29 22:18:11.86 spid11 Starting up database
> 'ScalaDB_SGO_Restore'.
> 2006-05-29 22:18:11.86 spid12 Starting up database 'ScalaDB_SGO_TEST'.
> 2006-05-29 22:18:11.86 spid5 Could not find database ID 3. Database
> may
> not be activated yet or may be in transition.
>
> which database does ID 3 refers to ?
> tks & rdgs
> maxzsim wrote:
>>Hi,
>> That was my intention to move master to another location but before i
>> could
>>do that i wasn't able to start SQL server service after moving both the
>>model
>>& msdb db to a new location at the same time
>>tks & rdgs
>>Moving model and msdb files will not affect the master database file
>>locations but you mentioned moving master in your other thread and it
>>looks
>>[quoted text clipped - 8 lines]
>> tks & rdgs
> --
> Message posted via http://www.sqlmonster.com|||Hi ,
Yes i am moving the databases because the harddisk is failing and i did the
following steps below
1. >sqlservr.exe /T3608
2. Then from SQL Query Analyzer I tried to attach both MODEL and MSDB
databases, and they were attached.
step 1 & 2 is from a posting by VM
3. but i cannot "see" model and when i tried to attached from EM it says
successfully but i still cannot see "model" db or when i tried to force
restore or WITH REPLACE it says RESTORE failed abnormally
what shall i do now ?
tks & rdgs
Dan Guzman wrote:
>Error 823 is usually preceded by a detailed message about the cause of the
>I/O error. Do you have any other related messages in the event log? Are
>you moving these database files because a disk is going bad?
>> Hi,
>[quoted text clipped - 20 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hello ,
Have you taken a copy of Master/Model/MSDB Database files before Performing
the above Steps?
Vishal|||I suggest you repeat the model database attach steps as described in
http://support.microsoft.com/kb/224071/en-us. Don't attach the model
database from EM - run the SQL scripts from Query Analyzer or OSQL.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60fae5663e141@.uwe...
> Hi ,
> Yes i am moving the databases because the harddisk is failing and i did
> the
> following steps below
> 1. >sqlservr.exe /T3608
> 2. Then from SQL Query Analyzer I tried to attach both MODEL and MSDB
> databases, and they were attached.
> step 1 & 2 is from a posting by VM
> 3. but i cannot "see" model and when i tried to attached from EM it says
> successfully but i still cannot see "model" db or when i tried to force
> restore or WITH REPLACE it says RESTORE failed abnormally
>
> what shall i do now ?
> tks & rdgs
>
> Dan Guzman wrote:
>>Error 823 is usually preceded by a detailed message about the cause of the
>>I/O error. Do you have any other related messages in the event log? Are
>>you moving these database files because a disk is going bad?
>> Hi,
>>[quoted text clipped - 20 lines]
>>>
>>> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hi ,
I have made a backup copies of all the system db but i am not able to
restore from the model.bak
tks & rdgs
Vishal Gandhi wrote:
>Hello ,
>Have you taken a copy of Master/Model/MSDB Database files before Performing
>the above Steps?
>Vishal
--
Message posted via http://www.sqlmonster.com|||Hi ,
using QA to attach the msdb & model db was done after i ran sqlserv.exe
/T3608
it simply says attached successfully but i could not see the "model" db and
btw ID 3 refers to the "msdb" db
in the worst case scenario if i need to re-install SQL Server can i restore
the master , model & msdb over the existing ones ?
tks & rdgs
Dan Guzman wrote:
>I suggest you repeat the model database attach steps as described in
>http://support.microsoft.com/kb/224071/en-us. Don't attach the model
>database from EM - run the SQL scripts from Query Analyzer or OSQL.
>> Hi ,
>[quoted text clipped - 26 lines]
>>>
>>> tks & rdgs
--
Message posted via http://www.sqlmonster.com|||> it simply says attached successfully but i could not see the "model" db
> and
> btw ID 3 refers to the "msdb" db
It looks like msdb got assigned dbid 3 instead of model.
Start sql server in minimal configuration mode and detaching both model and
msdb. Attach model first and verify all is expected:
USE model
EXEC sp_helpfile
EXEC sp_helpdb 'model' --make sure dbid is 3
GO
Then attach msdb and verify all is good:
USE msdb
EXEC sp_helpfile
EXEC sp_helpdb 'msdb' --make sure dbid is > 3
GO
Then restart SQL Server normally.
> in the worst case scenario if i need to re-install SQL Server can i
> restore
> the master , model & msdb over the existing ones ?
Yes, you could restore following a reinstall (or REBUILDM) but I'd try the
reattach first. You are almost there.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60fb4a3f43fa3@.uwe...
> Hi ,
> using QA to attach the msdb & model db was done after i ran sqlserv.exe
> /T3608
> it simply says attached successfully but i could not see the "model" db
> and
> btw ID 3 refers to the "msdb" db
> in the worst case scenario if i need to re-install SQL Server can i
> restore
> the master , model & msdb over the existing ones ?
> tks & rdgs
> Dan Guzman wrote:
>>I suggest you repeat the model database attach steps as described in
>>http://support.microsoft.com/kb/224071/en-us. Don't attach the model
>>database from EM - run the SQL scripts from Query Analyzer or OSQL.
>> Hi ,
>>[quoted text clipped - 26 lines]
>>>
>>> tks & rdgs
> --
> Message posted via http://www.sqlmonster.com|||tks for all the help provided but unfortunately the hdd failed on me before i
could do anything further
now i'll need to rebuild or re-install but luckily i got all the system &
user DBs backed up
tks & rdgs
Dan Guzman wrote:
>> it simply says attached successfully but i could not see the "model" db
>> and
>> btw ID 3 refers to the "msdb" db
>It looks like msdb got assigned dbid 3 instead of model.
>Start sql server in minimal configuration mode and detaching both model and
>msdb. Attach model first and verify all is expected:
>USE model
>EXEC sp_helpfile
>EXEC sp_helpdb 'model' --make sure dbid is 3
>GO
>Then attach msdb and verify all is good:
>USE msdb
>EXEC sp_helpfile
>EXEC sp_helpdb 'msdb' --make sure dbid is > 3
>GO
>Then restart SQL Server normally.
>> in the worst case scenario if i need to re-install SQL Server can i
>> restore
>> the master , model & msdb over the existing ones ?
>Yes, you could restore following a reinstall (or REBUILDM) but I'd try the
>reattach first. You are almost there.
>> Hi ,
>[quoted text clipped - 19 lines]
>>>>
>>>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1