SMO appears to provide inaccurate log file data

We've found a situation where SMO tells us that one of our log files is more than 100% full.

This appears to require that more than one log file is available for the database, and that the total log space used is greater than the total size of at least one of these log files.

In our situation, we have one log file with 15.1GB total space, and a second with 125MB total space. SMO reports 529MB used in the first file, and 529MB used in the second file.

Upon further inspection, the problem appears to be located in the section of code found at the end of the document ("SMO Internal Code".) The main problem I see, is the join from sysfiles to #tmplogspc. Sysfiles has two rows for this database, one for each file. #tmplogspc has one row for this database. So, SMO applies the total log space used to each of the files that constitute the log. Where the log file is less than the total log space used, the log appears to be more than 100% full.

Please let me know if there is an accurate way to measure the log space used by file.

Thanks!Joshua (Monterey, CA)

SMO Client Code (for problem rows, "used space" will be > 100%, and "available space" will be negative):

foreach (LogFile lfin db.LogFiles)

{

DataRow logfile_row = logfile_table.NewRow();

logfile_row["Database"] = db.Name;

logfile_row["FileName"] = lf.FileName;

logfile_row["Max Size (MB)"] = lf.MaxSize;

logfile_row["Used Space (%)"] =Math.Round((lf.UsedSpace / lf.Size) * 100, 2);

logfile_row["Available Space (MB)"] =Math.Round(lf.Size - lf.UsedSpace, 2);

logfile_row["Total Size (MB)"] = lf.Size;

logfile_row["Growth"] = lf.Growth;

logfile_row["GrowthType"] = lf.GrowthType.ToString();

logfile_table.Rows.Add(logfile_row);

}

SMO Internal Code:

DECLARE @PageSize float
SELECT @PageSize=v.low/1024.0 FROM master..spt_values v WHERE v.number=1 AND v.type='E'

create table #tmplogspc (DatabaseName sysname, LogSize float, SpaceUsedPerc float, Status bit)
insert #tmplogspc EXEC ('dbcc sqlperf(logspace)')

SELECT
rtrim(s.name) AS [Name],
rtrim(s.filename) AS [FileName],
case when s.maxsize=-1 then -1 else s.maxsize * @PageSize end AS [MaxSize],
tspclog.LogSize * tspclog.SpaceUsedPerc * 10.24 AS [UsedSpace],
(s.size * @PageSize) AS [Size],
CASE WHEN s.growth=0 THEN 99 WHEN (0 <>(s.status & 0x100000)) THEN 1 ELSE 0 END AS [GrowthType],
CAST(CASE WHEN (0 <>(s.status & 0x100000)) THEN s.growth ELSE s.growth * @PageSize END AS float) AS [Growth]
FROM
dbo.sysfiles AS s
INNER JOIN #tmplogspc tspclog ON
tspclog.DatabaseName = db_name()

WHERE
(s.groupid = 0)
ORDER BY
[Name] ASC

drop table #tmplogspc

[5422 byte] By [JWLucero] at [2007-12-24]