PRB: Incorrect Log Size Reported in SEM or Performance Monitor

ID: Q183100

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5

SYMPTOMS

After truncating a very full transaction log for a database, log space reporting may be incorrect. This is true whether the log space is checked by means of SQL Enterprise Manager (SEM), Windows NT Performance Monitor, or the sp_spaceused stored procedure.

CAUSE

All space reporting is taken from information in the sysindexes table within the database. In most cases, this information is up to date; however, it is not guaranteed to be. Due to performance reasons, the sysindexes table is not continually updated. Updating this information is the same as updating any table in the database, and must be logged first in the transaction log. When the transaction log becomes full, updates to the sysindexes table are stopped, causing the information reported to be inaccurate.

WORKAROUND

To work around this problem, issue the following statement in the database after truncating the log:

DBCC CHECKTABLE (syslogs)

This will display output similar to the following:

Checking syslogs
The total number of data pages in this table is 1.
The number of data pages in Sysindexes for this table was 4. It has
been corrected to 1.
The number of rows in Sysindexes for this table was 128. It has been
corrected to 12.
*** NOTICE: Space used on the log segment is 0.00 Mbytes, 0.10.
*** NOTICE: Space free on the log segment is 2.05 Mbytes, 99.90.
Table has 12 data rows.
DBCC execution completed. If DBCC printed error messages, see your
System Administrator.

The space used and free on the log segment is the correct information, because DBCC CHECKTABLE follows the actual page chain of the log. Also notice the line:

The number of rows in Sysindexes for this table was 128. It has been corrected to 12.

This is where DBCC CHECKTABLE is updating the sysindexes values. This alone should correct the space reporting information in SQL Enterprise Manager or Performance Monitor. However, sometimes you may also need to do one of the following:

Issue the following statement:

DBCC UPDATEUSAGE (<db_name>)
NOTE: Running DBCC UPDATEUSAGE may take a great deal of time, and requires the database to be in single-user mode. DBCC UPDATEUSAGE may take a long time to run because this command updates the dpages value in sysindexes for all the tables in the database, not just the dpages value for syslogs.

After issuing this statement, space reporting should be accurate. If it is not, try issuing the CHECKPOINT command to flush changes in the cache to disk so that they can be read in and calculated correctly.

-or-

In SQL Enterprise Manager, use the Recalculate button on the Edit Database screen to refresh the information. Note that this option also runs DBCC UPDATEUSAGE, which may take a long time.

-or-

Use DBCC SQLPERF(logspace) to obtain the log space information.

Additional query words: winnt dump no_log perfmon entman ent man 1105

Keywords : SSrvAdmin SSrvEntMan
Version : WINNT:6.0,6.5
Platform : winnt
Issue type : kbprb