Post reply

How to tell what sp_dboptions are set on a database

  • April 3, 2007 at 3:49 pm

    #194999

    HI. How can I tell what sp_dboptions are set on a database? I was trying to find out if the 'truncate log on checkpoint' was set to true for a database.

    Thanks,

    Juanita

      

  • April 3, 2007 at 4:30 pm

    #698419

    Just run sp_dboptions 'databasename'. Since you aren't setting anything, it will return all the current settings.

    If you add the second parameter, it will return the setting for just that one.

    Refer to the BOL (sp_dboption) for more information.

    -SQLBill

  • April 4, 2007 at 6:45 am

    #698513

    i have this handy query stored in my snippets collection.

    select name as DBNAME,STATUS,

    CASE WHEN (STATUS &         1) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [AUTOCLOSE_(ALTER_DATABASE)],

    CASE WHEN (STATUS &         4) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SELECT_INTO/BULKCOPY_(ALTER_DATABASE_USING_SET_RECOVERY)],

    CASE WHEN (STATUS &         8) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TRUNC._LOG_ON_CHKPT_(ALTER_DATABASE_USING_SET_RECOVERY)],

    CASE WHEN (STATUS &        16) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TORN_PAGE_DETECTION_(ALTER_DATABASE)],

    CASE WHEN (STATUS &        32) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [LOADING],

    CASE WHEN (STATUS &        64) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [PRE_RECOVERY],

    CASE WHEN (STATUS &       128) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [RECOVERING],

    CASE WHEN (STATUS &       256) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [NOT_RECOVERED],

    CASE WHEN (STATUS &       512) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [OFFLINE_(ALTER_DATABASE)],

    CASE WHEN (STATUS &      1024) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [READ_ONLY_(ALTER_DATABASE)],

    CASE WHEN (STATUS &      2048) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [DBO_USE_ONLY_(ALTER_DATABASE_USING_SET_RESTRICTED_USER)],

    CASE WHEN (STATUS &      4096) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SINGLE_USER_(ALTER_DATABASE)],

    CASE WHEN (STATUS &     32768) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [EMERGENCY_MODE],

    CASE WHEN (STATUS &   4194304) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [AUTOSHRINK_(ALTER_DATABASE)],

    CASE WHEN (STATUS &1073741824) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [CLEANLY_SHUTDOWN]

    from master.dbo.sysdatabases

    SELECT name as DBNAME,STATUS2,

    CASE WHEN (STATUS2 &     16384) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [ANSI_null_default_(ALTER_DATABASE)],

    CASE WHEN (STATUS2 &     65536) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [concat_null_yields_null_(ALTER_DATABASE)],

    CASE WHEN (STATUS2 &    131072) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [recursive_triggers_(ALTER_DATABASE)],

    CASE WHEN (STATUS2 &   1048576) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [default_to_local_cursor_(ALTER_DATABASE)],

    CASE WHEN (STATUS2 &   8388608) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [quoted_identifier_(ALTER_DATABASE)],

    CASE WHEN (STATUS2 &  33554432) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [cursor_close_on_commit_(ALTER_DATABASE)],

    CASE WHEN (STATUS2 &  67108864) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [ANSI_nulls_(ALTER_DATABASE)],

    CASE WHEN (STATUS2 & 268435456) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [ANSI_warnings_(ALTER_DATABASE)],

    CASE WHEN (STATUS2 & 536870912) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [full_text_enabled_(set_by_using_sp_fulltext_database)]

    from master.dbo.sysdatabases

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 4, 2007 at 9:48 am

    #698579

    Thanks so much!

    Juanita

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

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