September 4, 2012 at 4:40 am
Hi,
Just noticed my Master DB is in single user mode on a test machine. Tried to switch it back to multi_user both in T-SQL and GUI, i get the same error:
Msg 5058, Level 16, State 5, Line 1
Option 'MULTI_USER' cannot be set in database 'master'.
I cant restart the services or machine till tonight, Should i be concerned!?
Start up params and log below
2012-06-27 19:58:31.49 Server (c) Microsoft Corporation.
2012-06-27 19:58:31.49 Server All rights reserved.
2012-06-27 19:58:31.49 Server Server process ID is 1564.
2012-06-27 19:58:31.50 Server System Manufacturer: 'HP', System Model: 'ProLiant ML370 G4'.
2012-06-27 19:58:31.51 Server Authentication mode is MIXED.
2012-06-27 19:58:31.51 Server Logging SQL Server messages in file 'D:\Log\ERRORLOG'.
2012-06-27 19:58:31.58 Server This instance of SQL Server last reported using a process ID of 1580 at 27/06/2012 19:49:50 (local) 27/06/2012 18:49:50 (UTC). This is an informational message only; no user action is required.
2012-06-27 19:58:31.58 Server Registry startup parameters:
-d D:\Mdf\master.mdf
-e D:\Log\ERRORLOG
-l D:\Ldf\mastlog.ldf
2012-06-27 19:58:31.69 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2012-06-27 19:58:31.69 Server Detected 4 CPUs. This is an informational message; no user action is required.
2012-06-27 19:58:32.82 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.
2012-06-27 19:58:35.65 Server Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2012-06-27 19:58:37.08 spid6s Starting up database 'master'.
2012-06-27 19:58:37.68 spid6s 4 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
2012-06-27 19:58:37.83 spid6s 0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
2012-06-27 19:58:37.83 spid6s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
September 4, 2012 at 5:32 am
Just some updates..
sp_help 'master' returns:
namedb_sizeownerdbidcreatedstatuscompatibility_level
master 6.75 MBsa1Apr 8 2003Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=661, Collation=Latin1_General_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics100
SELECT name, user_access_desc
FROM sys.databases
where name = 'master'
nameuser_access_desc
masterSINGLE_USER
I have managed to restart the server. Didn't bring up any errors in event logs or anything new in SQL error log.
🙁
September 4, 2012 at 5:35 am
Are you sure that it really is in single user? Sp_help says multi user and that setting can't be set in the master database.
Is SQL not starting? Are users getting errors?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 4, 2012 at 5:56 am
Hi Gail,
No users are complaining and i can get multiple connections to Master, so it seems ok. Just weird and wasn't sure if it would lead to problems.
Is this why people don't like the GUI :D?
September 4, 2012 at 6:29 am
What's the GUI got to do with it?
Looks like something odd in the metadata. Run a checkDB to be sure there's nothing actually wrong.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 4, 2012 at 6:49 am
Log from running the Ola H checkdb
DateTime: 2012-09-04 13:36:46
Database: [master]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: SIMPLE
DateTime: 2012-09-04 13:36:46
Command: DBCC CHECKDB ([master]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
Outcome: Succeeded
Duration: 00:00:39
DateTime: 2012-09-04 13:37:25
Seems good.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply