Post reply

Log space

  • January 8, 2014 at 7:00 am

    #281772

    The below query gives results omitting information of few databases. Upon going through the results I found that the databases with is_cleanly_shutdown is 1 are missing.

    DBCC sqlperf (logspace)

    Please throw some light on this..

    ______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

  • January 8, 2014 at 8:24 am

    #1678436

    karthik babu (1/8/2014)

    The below query gives results omitting information of few databases. Upon going through the results I found that the databases with is_cleanly_shutdown is 1 are missing.

    DBCC sqlperf (logspace)

    Please throw some light on this..

    SQL Server can't report on databases that aren't online.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 8, 2014 at 10:35 pm

    #1678671

    Those DBs aren't offline.

    ______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

  • January 9, 2014 at 12:10 am

    #1678681

    Is it possible your user isn't allowed to view information on the missing databases?

  • January 9, 2014 at 12:47 am

    #1678686

    I am logging as sa

    Also I found if I am running "use database", that particular database, the value of is_cleanly_shutdown becomes 1

    And those databases are not showing up when I run DBCC sqlperf (logspace)

    the mystery continues....

    ______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

  • January 9, 2014 at 12:59 am

    #1678688

    If you have databases with a is_cleanly_shutdown status of 1 then the database is effectively invisible to DBCC SQLPERF(LOGSPACE). That is the reaason you cannot see them.

    This also happens if the database is in recovery.

  • January 9, 2014 at 2:00 am

    #1678696

    karthik babu (1/8/2014)

    Those DBs aren't offline.

    No, but they're shut down. That's what 'is_cleanly_shutdown' means, the database is closed, not in use. Probably they have auto_close enabled.

    It's not a mystery, databases that are not online (closed, offline, recovering, recovery_pending) don't show up in that DBCC because they're not in use.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 9, 2014 at 2:26 am

    #1678707

    Oh Yes.. the auto close on option was enabled and hence those doubts.. I have made it false now and my doubt is cleared.. somebody created with those options ON.

    Thanks a lot Gail!!

    ______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

Viewing 8 posts - 1 through 7 (of 7 total)

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