Corruption of syslogs (dbcc rebuild log)

A corrupt syslogs table (the transaction log of a database) can sometimes be fixed. Best approach is to load a clean database dump and it's transaction log. However, that's not always possible. For instance, when the corruption has been unnoticed for some time you need to take other steps.

Some suggestions:

• dump the database and reload the dump. This can solve some issues.
• extract all contect from the database with tools like defncopy and bcp. Then drop the database, recreate and load in the extracted information.

A more complex situation is shown here.

Solving Msg 2578 and Msg 2503

Run dbcc checktable(syslogs) to check the syslogs table.

use <database>
go
dbcc checktable(syslogs)
go
Checking syslogs: Logical pagesize is 2048 bytes
Msg 2578, Level 16, State 1:
Server 'ASE1', Line 1:
The first page 67664 in sysindexes for table 'syslogs' has previous page # 67663 in its page header. The previous page # should be NULL. Please check sysindexes.
Msg 2503, Level 16, State 1:
Server 'ASE1', Line 1:
Table Corrupt: Page linkage is not consistent; check the following pages: (current page#=67664;  page# pointing to this page=0; previous page# indicated in this page=67663)
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

You might want to run a "dbcc pglinkage" to see if you can fix the page number yourself. When that is not possible you may need to follow the rebuild_log method. WARNING you must use great caution with dbcc rebuild_log. This command will increase the current database timestamp value significantly. Once the database timestamp reaches it's limit (for example if you overuse this command) you will have to rebuild the database.

Bypass recovery mode for the database

use master
go
select status from sysdatabases where name = "<database>"
go

Make a note of the status

sp_configure "allow updates to system tables",1
go
begin tran
go
update sysdatabases set status = -32768 where name = "<database>"
go
commit tran
go
use <database>
go
checkpoint
go
shutdown
go

Rebuild the transaction log

Boot the ASE server. You will see a message in the errorlog that it skips recovery for the database.

server  *** Bypassing recovery of database id 7

Run the following commands:

use <database>
go
dbcc rebuild_log(<database>,1,1)
go
use master
go
begin tran
go
update sysdatabases set status = <original status> where name = "<database>"
go
commit tran
go
shutdown
go

Check if it worked

Boot the ASE server and check if the syslogs is now OK.

use <database>
go
dbcc checktable(syslogs)
go
sp_configure "allow updates to system tables",0
go