sp_helpdb and sys.databases inconsistent for user_access

  • **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

  • 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"

  • 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

  • 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


    Kind regards,

    Vegard Hagen
    Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
    @vegard_hagen on Twitter
    Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply