SQL Server Performance

Percentage of Log space used

Discussion in 'General DBA Questions' started by dmaddhali, Mar 6, 2006.

  1. dmaddhali New Member

    Hello All,
    I am trying to get the log space used by all the databases in sql server 2000/2005.

    I used "DBCC SQLPerf(LogSpace)" command to know the percentage of log space(.ldf) used in all the databases.

    The problem with the above command is ; If you have more than one log file for a database then it returns an average usage percentage .For example if you had a log file (.ldf) of 2MB and one of 4 MB and utilisation is 3 MB then the command shows 50% of usage. i.e. it calculates the usage percentage in this way.

    ((2+4)/3)*100


    Please tell me a command which will give me the usage percentage of all the log files associated with a database.

    Thanks in advance.

    Regards,
    Satish

  2. FrankKalis Moderator

    Actually just guessing, but since SQL Server treats more than one log files internally as one single virtual file, I'm not aware of a command that gives you the information you want. <br /><br />...but I also wouldn't be suprised when there is such an undocumented one. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterst�tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  3. shekhar157 New Member

  4. satya Moderator

    I would stick to that statement to ascertain the log space used with the databases.
    The process you have mentioned is correct and in order to get accurate you need third party tools to see the log usage, as I don't see any undocumented way to achieve.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. dmaddhali New Member

    Dear Shekar ,
    I went through the link which you provided. Its doesnot deal with multiple logs problem.Thanks for your reply.


    Hello All,
    So, Can we come to a conclusion that there is no documented way to get multiple log files usage percentage ?


    Regards,
    Satish
  6. FrankKalis Moderator

  7. satya Moderator

    ... or rather say Microsoft internal use only [<img src='/community/emoticons/emotion-5.gif' alt=';)' />] try emailing sqlwish@microsoft.com and you may get response for the question.<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  8. dmaddhali New Member

    Thanks Satya . I have sent a mail to sqlwish@microsoft.com .

  9. satya Moderator

    So am I, let us wait for the update.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. dtipton New Member

    Just to be curious, why do you have multiple log files?
  11. satya Moderator

    A common mistake by the users to create another log file when the first (primary) log file is filling/filled up with any transactions. I have seen this problem in many places and they never keen on setting the size and managing with frequent backups of Tlog.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  12. dtipton New Member

    It was my understanding that creating multiple data files for the same tlog did not buy you anything performance wise. Is that correct?

  13. satya Moderator

    Absolutely, rather after some time it will create confusion.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page