Microsoft KB Archive/183100

= PRB: Incorrect Log Size Reported in SEM or Performance Monitor =

Article ID: 183100

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q183100



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 ()

NOTE: 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: kbprb KB183100

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.