DBCC SQLPERF(logspace)

Question

DBCC SQLPERF(logspace)

Sachin Nandanwar

SSCertifiable

Points: 7699
More actions
January 16, 2011 at 9:25 am

#245364

Hi All,
I had a test database which I shrunk the log file.But when I used the DBCC SQLPERF(logspace) to check the status of the log file it shows the file size did shrunk but the %Log space used has jumped about 3 times than before.
Can anyone explain what does it mean?
Please refer to the image attached with this post.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

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

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

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

Shrink won't reduce the amount of space used within the log, only the size of the file.

So if the log was 200 MB and 20MB of it was in use, that would be 10% utilised. If you then shrunk the log to 40MB, the used space wouldn't change and the % used would now be 50%.

Please read through this - Managing Transaction Logs[/url]

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

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 2

Thanks for the reply Gail.

I understand what you are saying.But I just used the below DBCC command without specifying any size

dbcc shrinkfile('logfile')

So how was it that after shrinking the file the "Log space used %" suddenly jumped from 11% before shrinking to 48% after shrinking ?

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

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

Because the amount of used space didn't chance, the amount of free space did. Hence the percentage of the file that was in use increased.

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

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 4

GilaMonster (1/16/2011)
Because the amount of used space didn't chance, the amount of free space did. Hence the percentage of the file that was in use increased.

Yes now that makes sense.Thank you very much.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server