Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_helpdb and sys.databases inconsistent for user_access Expand / Collapse
Author
Message
Posted Monday, October 29, 2012 9:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 10, 2013 8:54 AM
Points: 87, Visits: 253
**moved from data corruption forum**

The Master database on one of our servers is showing in SSMS as Single-user mode. I have tried to change it back to multi-user, but am getting error "Option 'MULTI_USER' cannot be set in database 'master'". Weirdly, I am able to connect to the database and make changes to it.

Sys.databases is reporting user_access as 'SINGLE_USER'
However, sp_helpdb 'master' reports "user_access=MULTI_USER"
DBCC CHECKDB returns nothing of interest
The error logs all look fine, too.

I understand that another instance of SQL Server was installed on the same server recently, though that is used by another department, and I don't have access to that instance. Could this have caused this confusion?

Our infrastructure providers are saying that this inconsistency is only a GUI issue, so we shouldn't be concerned. However, I am concerned that this could be indicative of a wider problem with the database and that they are trying to sweep it under the carpet.

Can anyone shed any light on these inconsistencies?

Thanks
Post #1378316
Posted Monday, October 29, 2012 12:03 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:02 AM
Points: 709, Visits: 1,422
Look at the SQL logs. It should show what parameters were used when the instance started. Can you list those?

Alternatively it should also be listed in the startup parameters if you look at the SQL Server service for the instance you are troubleshooting in SQL Server Configuration Manager.


Joie Andrew
"Since 1982"
Post #1378398
Posted Tuesday, October 30, 2012 2:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 10, 2013 8:54 AM
Points: 87, Visits: 253
Hi

Thanks for your reply. Is this what you meant, or would you need to see more than this?

10/29/2012 10:18:12,Server,Unknown,Registry startup parameters: <nl/> -d F:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf<nl/> -e F:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG<nl/> -l F:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

The only information that I can see that is specifically about the Master database is

10/29/2012 10:18:12,spid7s,Unknown,Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.<nl/><{39B75A24-0837-4CEC-AFDF-B960027AE07E}>RsFxNso initialized. InstanceId = 00000002<nl/><{50080099-5EC4-4EAF-A2A2-63C3DA97F8EB}>FsAgent is initialized<nl/><{09C4480B-DBA4-49B7-956F-68A8B8B2445D}>FsAgent is up and running
10/29/2012 10:18:12,spid7s,Unknown,Starting up database 'master'.

Thanks,
Alun
Post #1378610
Posted Tuesday, October 30, 2012 3:17 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 11, 2014 6:50 AM
Points: 517, Visits: 346
I believe the question was meant to discover whether your instance was started in Single user mode, also known as maintenance mode. Startup parameter: -m



Vegard Hagen
Norwegian DBA, blogger and generally a nice guy who believes the world is big enough for all of us.

@vegard_hagen on Twitter
Blog: Vegard's corner (No actual SQL stuff here - haven't found my niche yet. Maybe some day...)

"It is better to light a candle than to curse the darkness."
(Chinese proverb)

Post #1378628
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse