Post reply

Configuration

  • November 27, 2007 at 2:32 pm

    #184117

    I have 100 Database what is the easiest way to check about each database weather it is in Auto-grow mode or not?

    Also, How can I see the configuration of my SQl server

    Nita

  • November 27, 2007 at 6:58 pm

    #754798

    Got this script from one of the topics here in sqlservercentral

    select

    name as [DB_NAME]

    , isnull(case when status & 1 = 1 then 'autoclose' else null end + ',', '')

    + isnull(case when status & 4 = 4 then 'select into/bulkcopy' else null end + ',', '')

    + isnull(case when status & 8 = 8 then 'trunc. log on chkpt' else null end + ',', '')

    + isnull(case when status & 16 = 16 then 'torn page detection' else null end + ',', '')

    + isnull(case when status & 32 = 32 then 'loading' else null end + ',', '')

    + isnull(case when status & 64 = 64 then 'pre recovering' else null end + ',', '')

    + isnull(case when status & 128 = 128 then 'recovering' else null end + ',', '')

    + isnull(case when status & 256 = 256 then 'not recovered' else null end + ',', '')

    + isnull(case when status & 512 = 512 then 'offline' else null end + ',', '')

    + isnull(case when status & 1024 = 1024 then 'read only' else null end + ',', '')

    + isnull(case when status & 2048 = 2048 then 'dbo use only' else null end + ',', '')

    + isnull(case when status & 4096 = 4096 then 'single user' else null end + ',', '')

    + isnull(case when status & 32768 = 32768 then 'emergency mode' else null end + ',', '')

    + isnull(case when status & 4194304 = 4194304 then 'autoshrink' else null end + ',', '')

    + isnull(case when status & 1073741824 = 1073741824 then 'cleanly shutdown' else null end + ',', '') AS 'DB_PROPERTIES'

    from master..sysdatabases

    "-=Still Learning=-"

    Lester Policarpio

  • November 29, 2007 at 7:55 am

    #755453

    Thanks

    Nita

  • November 29, 2007 at 8:15 am

    #755474

    This Script don't tell me weather auto-grow is selected or not?

    Can you or anyone please help me to find the script which generate to find the auto-grow is selected or not?

    I can do manually but its all 100 databases

    Nita

  • November 29, 2007 at 8:30 am

    #755487

    Try this:

    Exec sp_MSForeachDB @command1 = 'Use ?;SELECT DB_NAME(),FileName,

    CASE

    WHEN Growth = 0 THEN ''NO''

    ELSE ''YES''

    END as AutoGrow

    FROM sysfiles'

    [font="Verdana"]Markus Bohse[/font]

  • November 29, 2007 at 8:56 am

    #755509

    Yup that's work

    thanks

    🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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