Determining Unused Log Space

Use dbcc checktable(syslogs) for an accurate check of free space in Sybase Adaptive Server Enterprise. -

_{A bit old though}

Contents

When you need to check free space in the server logs, users typically use the stored procedure sp_helpdb. While sp_helpdb is useful for a general estimation of free space, for a precise figure use one of the following methods:

* dbcc checktable (syslogs)
* Determine the number of data pages in the transaction log via isql script, for example:

select data_pgs (8, doampg)
from sysindexes where id=8
go

Each method has advantages.

Sybase recommends sp_helpdb for most situations because it reports quickly. sp_helpdb uses the unreserved page count in sysusages. However, unreserved page count is updated intermittently and therefore may not accurately reflect the actual state of the database. Thus, when sp_helpdb reports free space, when you perform an insert you may run out of space, resulting in error message 1105, which reads in part:

Can't allocate space for object ... because log segment full

If this error occurs, follow the instructions in Runtime 1105 Errors: State 3 in the "Error Message Writeups" chapter of the Adaptive Server Enterprise Troubleshooting and Error Messages Guide.

The dbcc checktable (syslogs) command also checks for possible corruption as well as the size of the log. However, it can take a long time to run, depending on the size of the log. For more information about dbcc checktable, see the chapter, "Checking Database Consistency" in the Adaptive Server Enterprise System Administration Guide.

The isql script is more accurate than sp_helpdb. It is described in the Error 1105 section in "Error Message Writeups" chapter of the Adaptive Server Enterprise Troubleshooting and Error Messages Guide.