Microsoft KB Archive/46423

From BetaArchive Wiki

INF: Monitoring SYSLOGS Space Usage ID Number: Q46423

1.00 1.10 1.11 4.20 OS/2


The “SQL Server System Administrator’s Guide” recommends using sp_spaceused to determine how close SYSLOGS is to filling up. This article provides more information on this topic.

More Information:

The “data” value returned by sp_spaceused is an approximate indicator of how many 2K pages are currently being used by SYSLOGS. The “reserved” value is the number of pages that have been allocated to SYSLOGS and are unavailable for use by other tables. Reserved pages may or may not be actually used. Data pages are those pages that are reserved and are also actually used.

For tables other than SYSLOGS, “reserved” should always be greater than “data”; however, SYSLOGS changes so often that this statistic is not maintained in real time for performance reasons. Run DBCC CHECKTABLE(SYSLOGS) to get the up-to-date value. The sp_spaceused value should be close enough for the purpose of estimating the amount of space consumed by the log. The index size is always 0 because SYSLOGS has no index. The unused space may be negative because it is computed by subtracting data pages from reserved pages, and those values may not be up-to-date for reasons described above.

When SYSLOGS cannot get any more space, further updates will be prohibited. If SYSLOGS is on a separate database fragment (as it should be), the total available space for SYSLOGS is the total size of the log fragment(s). When the number of 2K log pages consumes all of the usable pages in the database fragment(s), further updates will be prohibited. There are 510 usable 2K pages per megabyte of database space. This is because there are 512 2K pages per megabyte, but 2 of these are used to keep track of page usage.

If SYSLOGS is mixed in with table data (as in master), SYSLOGS competes with other tables for unreserved space. The sp_spaceused will show the total amount of reserved space in a database.