Skip to end of metadata
Go to start of metadata

dbrepair : Update or repair various information for a database

Syntax:
dbcc dbrepair(dbname, option [, tablename, indid])
dbcc dbrepair(dbname, remap [, fixalloc])
dbcc dbrepair(dbname, fixlogholeslocation [, lstart])

OptionDescription
dbnameDatabase name
Option/actionVarious actions that can be done against this database. See table option/action table
Table NameName of table in the current database that repairindex will be run against.
indidThe Index ID for the table in the current database that repairindex will be run against.

Option Actions table . . . Note items with asterisk * are documented in the ASE 15 manuals

Option/Action for dbrepairinfo
dropdb *Delete a database that has been marked suspect (DBT_SUSPECT 0x100) and not recovered (DBT_NOTREC 0x40). You may trick the database into being marked suspect and not recovered by setting Master..sysdatabases.status to 320 ( Hex 140 = Decima1 320.). Prior to ASE 15.0.2, this option was needed because the DROP DATABASE command would not drop suspect databases. DROP DATABASE has since been enhanced so it will drop suspect databases, so this option is now generally obsolete but retained for backward compatibility.
findstrandedFinds the number of non-syslogs extents that reside on a logsegment and toggles the status bit DBT2_STRANDED (0x8000) in the dbtable structure field dbt_stat2. This command works silently but is otherwise the same as dbcc findstranded, which adds output indicating things such as how many stranded extents were found.
flushthresholdFlushes in-memory last-chance threshold value to systhresholds without setting any status information in the column sysdatabases.status2. This is used by sp_helpthreshold to make sure that up-to-date information is available to users.
ltmignoreThis function performs the same function as the command " dbcc settrunc(ltm, ignore)", but does not presume that the requestor is already in the database to be affected. This function supports customers who are attempting to bring replicated databases online, where the database log must be upgraded to a newer version, which means that we must replicate the remaining portion of the log before onlining the database when there is no replication server available. There is no good alternative for these customers except to cancel replication on the target database.
newthreshold *Refresh the threshold cache for a dbtable from the systhresholds table for the db.
remapUpdate the dbtable structure sysusages information by reading the current sysusages information for dbname.

If the fixalloc option is given, then all of the allocation pages have their segmap value updated

repairindexRebuilds an index for a system table. This option cannot be used on the sysindexes table and can only be used for the non-clustered index for sysobjects. Prior to proceeding, the O_RAMBOIX bit (0x1000) must be set in the sysstat column for the object in sysobjects table. The database must be in single user mode in order to use this option.
updownerUpdates the uid in the dbinfo->dbi_suid and the dbtable->dbt_dbaid fields. This is called by sp_changedbowner
readpageUsed to read a page in a db and is only called by apt/workbench, dblib and jConnect. The SybEdit tool read function is recommended instead of this.
writepageUsed to write a page in a db and is only called by apt/workbench, dblib and jConnect. The SybEdit tool write function is recommended instead of this.
flushthresholdFlushes the LCT value to systhresholds table.
dropproxydb *Drop an HA-created proxy database.
fixlogfreespacedbcc dbrepair (dbname, fixlogfreespace [ ,scanlogchain] )

Recalculate the log free space count for a given database. This should be done in single user mode. (C code indicates it is "possible to do the work in multi user mode even if we know the count might be inaccurate, but this needs to be done as a last resort".) Applicable for dedicated log databases only.  The default method scans all the allocation pages to guarantee an accurate result assuming single user mode.  The optional "scanlogchain" method just scans the syslogs page chain to determine the number of allocated pages and subtracts that from the total size of the segment to reset free space.  This can be much faster, particularly if the log is truncated as small as possible, but only accurate if there are no orphaned allocations on the log segment.
upd_usgUpdate the usage anchors from the current contents of sysusages.
 fixholeslocation
Used to fix sysusages.location when the holes (fragments removed by ALTER DATABASE SHRINK) have a location 0, to set it to either 9 (log hole) or 10 (data hole). It will also set to 0 when a hole is marked by recovery as 'to become a hole (location == 11) at online time', if for whatever reason the 11 has been left behind. For this, you can actually pass an lstart, and it will set the location to the one requested.
[Introduced in 16.0 SP03 PL07 under CR 818137, see KBA 2882962 for more details. ]

Some Sample output:

findstranded option does not report any info.

  1> dbcc dbrepair(batman,findstranded)
  2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.

newthreshold option does not report any info

  1> dbcc dbrepair(batman, newthreshold)
  2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.

Rebuild an index for a system table.
Note that the O_RAMBOIX bit must be enable in sysstat column in sysobjects for the table in question.

  1> use x
  2> go
  1> update sysobjects set sysstat= sysstat|4096 where name='syscomments'
  2> go
(1 row affected)
  1> checkpoint
  2> go
  1> dbcc dbrepair(x,repairindex, syscomments,1)
  2> go
There are 0 rows in 1 pages for object 'syscomments'.
Clustered index successfully restored for object 'syscomments' in 'x' database.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
  1> update sysobjects set sysstat = sysstat & ~4096 where name='syscomments'
  2> go
(1 row affected)
  1> checkpoint
  2> go

Drop a database that has been marked suspect and marked not recovered
If database is not marked suspect and not recovered, you can force the status by setting it to 320

  1> dbcc dbrepair(pubs100, dropdb)
  2> go
Msg 2573, Level 16, State 1:
Server 'hub_150', Line 1:
Database 'pubs100' is not marked suspect. You cannot drop it with DBCC.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
  1> update master..sysdatabases set status=320 where name='pubs100'
  2> go
(1 row affected)
  1> dbcc dbrepair(pubs100, dropdb)
  2> go
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

Remap the sysusages entries for a database

  1> dbcc dbrepair(batman, remap)
  2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.

Recalculate the log free space count for a given database

  1> sp_dboption testdb, "single", true
  2> go
Database option 'single user' turned ON for database 'testdb'.
Running CHECKPOINT on database 'testdb' for option 'single user' to take effect.
(return status = 0)
  1> dbcc dbrepair (testdb, fixlogfreespace)
  2> go
Syslogs free space count has been successfully recalculated. It has been corrected to 2549 pages.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
  • No labels