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
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>)
Hi, http://www.databasejournal.com/features/mssql/article.php/1467771 above link can help U regards Shekhar Mishra "No passion in the world is equal to the passion to alter someone else's draft."
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.
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
I'd say, yes. And again, I'm even not sure if there is an undocumented one. -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de Heute schon gebloggt?http://www.insidesql.de/blogs Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
... 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>
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.
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.
It was my understanding that creating multiple data files for the same tlog did not buy you anything performance wise. Is that correct?
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.
