PRB: Incorrect Log Size Reported in SEM or Performance Monitor (183100)
The information in this article applies to:
- Microsoft SQL Server 6.0
- Microsoft SQL Server 6.5
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 (<db_name>)
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.
| Modification Type: | Minor | Last Reviewed: | 3/14/2005 |
|---|
| Keywords: | kbprb KB183100 |
|---|
|