This section provides some strategies for detecting allocation errors 2521, 2540, 2546, 7939, 7940, and 7949 as early as possible:
Refer to “Single-User Mode Method (Spurious and Non-Spurious Errors)” if the database can be placed in single-user mode to perform maintenance tasks.
Refer to “Multiuser Mode Method (Spurious Errors Only)” if you cannot invoke single-user mode on the database in question (for example a 24-hour production site).
Consider running your dbcc checks on groups of tables in successive off-peak periods. For example, if you have 210 tables, run checks on 70 tables nightly until you cycle through all the tables. If you adopt this approach, placing the database in single-user mode is only necessary when running dbcc tablealloc on system tables. The same approach can be taken for dbcc checktable but there is no need to place the database in single-user mode.
Without single-user mode, you cannot prevent non-spurious error messages from occurring.
If you can run dbcc checkalloc in single-user mode, replace each occurrence of dbcc checkalloc in scripts and procedures with dbcc checkalloc with the fix option, as follows:
1> use master 2> go
1> sp_dboption database_name, single, true 2> go
1> use database_name 2> go
1> checkpoint 2> go
1> use master 2> go
1> dbcc checkalloc(database_name, fix) 2> go
1> sp_dboption database_name, single, false 2> go
1> use database_name 2> go
1> checkpoint 2> go
Use dbcc checkalloc with the fix option
while in a database other than the one that is being repaired.
Before you implement this strategy, consider these facts:
dbcc checkalloc with the fix option must be run in single-user mode.
Because dbcc checkalloc with the fix option may report other errors, Sybase recommends that you save the output from the dbcc checkalloc command and examine it.
dbcc checkalloc with the fix option is the same program as dbcc checkalloc, except that dbcc checkalloc with the fix option requires single-user mode and fixes errors instead of just reporting them. dbcc checkalloc with the fix option is not slower than dbcc checkalloc.
Because the master database is usually updated less frequently, allocation errors occur much less often. Therefore, you may not need to use this strategy on master. If you do use it on master, refer to “How to Start Adaptive Server in Single-User Mode” of this guide for instructions on how to activate single-user mode (it cannot be invoked via sp_dboption on master).
You do not ever need to run dbcc checkalloc after dbcc checkalloc with the fix option to ensure that the errors were corrected.
Although no actual users are logged on, you may not be able to enable single-user mode if there are processes still active.
If you have databases on which you cannot run allocation checks in single-user mode, use the following procedure to eliminate the spurious allocation errors that can occur when dbcc checkalloc is run in multiuser mode.
If your site does not allow single-user operation (such as a 24-hour production Adaptive Server), you cannot completely prevent spurious allocation errors, but you can prevent spurious errors on the transaction log-where most occur. Use both of the strategies described in this section to stop occurrences of spurious allocation errors.
Do not run dbcc check commands when performing operations like create index, truncate table, or bcp; or when doing large numbers of inserts into the database.
Before you implement this strategy, consider these facts:
This strategy is unnecessary if you can run the database in single-user mode. If you can run the database in single-user mode, use the strategy described in “Single-User Mode Method (Spurious and Non-Spurious Errors)”.
Because the master database is usually updated less frequently than user databases, allocation errors occur much less frequently. Therefore, this strategy may be unnecessary on master.
For this strategy, replace each occurrence of dbcc checkalloc in scripts and procedures with the following:
1> dbcc traceon (2512) 2> go
1> dbcc checkalloc (database_name) 2> go
1> dbcc traceoff (2512) 2> go
1> use database_name 2> go
1> dbcc tablealloc (syslogs) 2> go
This procedure prevents dbcc checkalloc from examining the syslogs table, where most spurious errors originate (dbcc tablealloc checks syslogs instead). If you get genuine allocation errors, refer to Chapter 3, “Error Message Writeups” for instructions.
