ID Number: Q46423
1.00 1.10 1.11 4.20
OS/2
Summary:
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.