|
|
Posted by Mike on 12/03/07 10:53
I'm having trouble moving msdb/model databases on SQL2005.
We don't have any problems doing this on SQL2000.
Our SQL instance is a named one (TEST) and has the following
SP2
File version 2005.090.3042.00
SQL States 2053
Version 9.2.3042.00
We are running SQL with trace flag T3608 as recommended by MS in order
to move the msdb and model log files. That was tricky enough - SQL2005
seems to be much pickier than 2000 when applying options:
-dD:\MSSQL\DATA\master.mdf;-eD:\MSSQL\LOG\ERRORLOG;-lC:\MSSQL\LOGS
\mastlog.ldf; -m -c -T3608
I've successfully restarted SQL. Extract from ErrLog:
2007-12-03 10:37:25.76 Server (c) 2005 Microsoft Corporation.
2007-12-03 10:37:25.76 Server All rights reserved.
2007-12-03 10:37:25.76 Server Server process ID is 4228.
2007-12-03 10:37:25.76 Server Authentication mode is MIXED.
2007-12-03 10:37:25.76 Server Logging SQL Server messages in file
'D:\MSSQL\LOG\ERRORLOG'.
2007-12-03 10:37:25.78 Server This instance of SQL Server last
reported using a process ID of 6500 at 12/3/2007 10:37:10 AM (local)
12/3/2007 10:37:10 AM (UTC). This is an informational message only; no
user action is required.
2007-12-03 10:37:25.78 Server Registry startup parameters:
2007-12-03 10:37:25.78 Server -d D:\MSSQL\DATA\master.mdf
2007-12-03 10:37:25.78 Server -e D:\MSSQL\LOG\ERRORLOG
2007-12-03 10:37:25.78 Server -l C:\MSSQL\LOGS\mastlog.ldf
2007-12-03 10:37:25.78 Server -m -c -T3608
2007-12-03 10:37:25.78 Server SQL Server is starting at normal
priority base (=7). This is an informational message only. No user
action is required.
2007-12-03 10:37:25.78 Server Detected 8 CPUs. This is an
informational message; no user action is required.
2007-12-03 10:37:25.92 Server Set AWE Enabled to 1 in the
configuration parameters to allow use of more memory.
2007-12-03 10:37:26.05 Server Using dynamic lock allocation.
Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per
node. This is an informational message only. No user action is
required.
2007-12-03 10:37:26.06 Server Attempting to initialize Microsoft
Distributed Transaction Coordinator (MS DTC). This is an informational
message only. No user action is required.
2007-12-03 10:37:28.08 Server Attempting to recover in-doubt
distributed transactions involving Microsoft Distributed Transaction
Coordinator (MS DTC). This is an informational message only. No user
action is required.
2007-12-03 10:37:28.08 Server Database mirroring has been enabled
on this instance of SQL Server.
2007-12-03 10:37:28.08 spid5s Starting up database 'master'.
2007-12-03 10:37:28.14 spid5s Recovery is writing a checkpoint in
database 'master' (1). This is an informational message only. No user
action is required.
2007-12-03 10:37:28.23 spid5s SQL Trace ID 1 was started by login
"sa".
2007-12-03 10:37:28.28 spid5s Starting up database
'mssqlsystemresource'.
2007-12-03 10:37:28.28 spid5s The resource database build version
is 9.00.3042. This is an informational message only. No user action is
required.
2007-12-03 10:37:28.39 spid5s Server name is 'MYSERVER\TEST'.
This is an informational message only. No user action is required.
2007-12-03 10:37:28.39 spid9s Starting up database 'model'.
2007-12-03 10:37:28.45 spid9s Clearing tempdb database.
2007-12-03 10:37:28.50 spid9s Starting up database 'tempdb'.
2007-12-03 10:37:28.51 spid12s The Service Broker protocol
transport is disabled or not configured.
2007-12-03 10:37:28.51 spid12s The Database Mirroring protocol
transport is disabled or not configured.
2007-12-03 10:37:28.53 spid12s Service Broker manager has started.
2007-12-03 10:37:35.08 Server The server could not load the
certificate it needs to initiate an SSL connection. It returned the
following error: 0x8009030d. Check certificates to make sure they are
valid.
2007-12-03 10:37:35.28 Server A self-generated certificate was
successfully loaded for encryption.
2007-12-03 10:37:35.28 Server Server is listening on [ 'any'
<ipv4> 2490].
2007-12-03 10:37:35.28 Server Server local connection provider is
ready to accept connection on [ \\.\pipe\SQLLocal\TEST ].
2007-12-03 10:37:35.28 Server Server local connection provider is
ready to accept connection on [ \\.\pipe\MSSQL\sql\query ].
2007-12-03 10:37:35.29 Server Server is listening on [ 127.0.0.1
<ipv4> 4601].
2007-12-03 10:37:35.29 Server Dedicated admin connection support
was established for listening locally on port 4601.
2007-12-03 10:37:35.36 Server SQL Server is now ready for client
connections. This is an informational message; no user action is
required.
This shows I've restarted SQL successfully.
I then tried the following (once again as recommended):
exec sp_detach_db msdb
I get the error
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
Surely this shouldn't happen? Does anyone know what I'm doing wrong?
Many thanks for any help you can give,
Mike.
[Back to original message]
|