DBCCs should be run on a regular basis to check for allocation errors, which occur due
to hardware issues (in most cases). For 24x7 needs, DBCCs can be run on a separate server
that is loaded from a current database dump.
Here is a script which will perform the basic DBCC functions
use master
go
sp_dboption invoice_db,'single user', true
go
use invoice_db
go
checkpoint
go
use invoice_db
go
select db_name()
go
checkpoint
go
dbcc checkdb
go
dbcc checkalloc
go
dbcc checkcatalog
go
use master
go
sp_dboption invoice_db,'single user',false
go
use invoice_db
go
checkpoint
go
Table or index allocation errors can be fixed by simply dropping the object and recreating
it (using BCP as needed). See below for other repair methods.
Here is a script which will fix many table allocation errors
use invoice_db
go
dbcc tablealloc(tablename, full, fix)
go
Here is a script which will fix most page allocation errors
use master
go
sp_dboption invoice_db,'single user', true
go
use invoice_db
go
checkpoint
go
use invoice_db
go
select db_name()
go
checkpoint
go
dbcc checkalloc(invoice_db,fix)
go
use master
go
sp_dboption invoice_db,'single user',false
go
use invoice_db
go
checkpoint
go
|
|
