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.
Contents
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