DBCC's

3.1    How do I set TS Role in order to run certain DBCCs...?
3.2    What are some of the hidden/trick DBCC commands?
3.3    Other sites with DBCC information.
3.4    Fixing a Munged Log

Performing any of the above may corrupt your ASE installation. Please do not call Sybase Technical Support after screwing up ASE. Remember, always take a dump of the master database and any other databases that are to be affected.

3.1: How to set TS Role

Some DBCC commands require that you set TS Role in order to run them. Here's how to set it:

Login to Server as sa and perform the following:

sp_role "grant", sybase_ts_role, sa
go

set role "sybase_ts_role" on
go

Back to top

3.2: DBCC Command Reference

Here is the list of DBCC commands that have been sent into the FAQ.  If you know of any more or have more information, then please send it in to dowen@midsomer.org, this is, after all, a resource for us all.

As ASE develops, so some of the dbcc's change.  I have pointed out major changes from one release to another that I know about.  However, a couple of changes are so common that it will save a lot of space if I say it once.  Where there is an option to specify dbid or dbname, in previous releases only dbid would be accepted.

Back to top

3.3: Other Sites with DBCC information

• http://user.icx.net/~huntley/dbccinfo.htm, Al Huntley's site contains a comprehensive including discussion on some and example output.
• http://www.kaleidatech.com/dbcc1.htm, From KaleidaTech Associates, Inc. has another fairly complete list.
• http://www.sypron.nl, as you would expect, Rob Verschoor has a list of DBCC's in his ASE Quick Reference Supplement.

Back to top

3.4: Fixing a Munged Log

Sybase Technical Support states that this is extremely dangerous as it "jacks up the value of the timestamp" which is used for recovery purposes. This may cause potential database corruption if the system fails while the timestamp rolls over.

In 4.9.2, you could only run the dbcc rebuild_log command once and after that you would have to use bcp to rebuild the database

In System 10, you can run this command about 10 times.

In System 11 I (Pablo, previous editor) tried it about 20 times and no problem.

1> use master
2> go
1> select count(*) from your_database..syslogs
2> go

 -----------
 some number

1> sp_configure "allow updates",1
2> go
1> reconfigure with override  /* for system 10 and below only*/
2> go

1> begin tran
2> go

/* Save the following status to be used later... */
1> select saved_status=status from sysdatabases where name = "your_database"
2> go
1> update sysdatabases set status = -32768 where name = "your_database"
2> go
1> commit tran
2> go
1> shutdown
2> go

1> dbcc rebuild_log (your_database, 0, 0)
2> go
DB-LIBRARY error (severity 9):
   Unexpected EOF from SQL Server.

1> dbcc rebuild_log (your_database, 1, 1)
2> go
DBCC execution completed. If DBCC printed error messages, see your System
Administrator.


1> use your_database
2> go
1> select count(*) from syslogs
2> go

 -----------
           1

1> begin tran
2> go
1> update sysdatabases set status = saved_status where name = "your_database"
2> go
(1 row affected)
1> commit tran
2> go
1> shutdown
2> go

Back to top