Log space

Question

Log space

karthik babu

Hall of Fame

Points: 3335
More actions
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: 🙂

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

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

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

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)

karthik babu Hall of Fame Points: 3335 More actions · Answer 2

Those DBs aren't offline.

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

kevaburg SSCoach Points: 18131 More actions · Answer 3

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

karthik babu Hall of Fame Points: 3335 More actions · Answer 4

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: 🙂

kevaburg SSCoach Points: 18131 More actions · Answer 5

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

karthik babu Hall of Fame Points: 3335 More actions · Answer 7

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: 🙂