Unanswered: Problem with "dbcc checkdb" locks table
I have a database in one ASE server 15.0.2. This databases is inserting thousands of rows every 15m. Nevertheless, I need to use "dbcc checkdb" before dump the database, but the process is trying inserting data is locked and hanged. The checkdb takes about 10 hours, so there are not new data during this time because there are only one big table and dbcc locks this completly table for checking.
I have read some solutions:
- Make a dump, load the database in other server and check it. The problem is that I need to create a new server to do it..but I am not sure. If I have database "DB_A" in server "SERVER_A", can I dump DB_A and load it in SERVER_A with another name to check it with checkdb?
- Make partitioning. If checkdb locks only tables, maybe if I make a partition for this big table, then checkdb doesn't lock all. Nevertheless, this solution is very complicated for this moment...
I would rather suggest that you set up a dbccdb for database checks.
You can then use 'dbcc checkstorage' which is far more efficient in checking for problems than the normal dbcc commands - checkdb, checkalloc.
You will retain fault history, it will run much faster than the normal dbccs, it uses parallel processing in checks and loads most of the required pages with allocation info into the configured data cache before the checks start.
It's relatively easy to set up, and may just need some tweaks after the initial setup / configuration. It's setup is documented in the Sybase ASE Administrator's guide, and there's also a very informative short section in Rob Verschoor's "The Complete Sybase ASE Quick Reference Guide" on how to set it up.
Faults indicated from the dbccdb reports might still require fixing, and here you will need to use the "traditional" dbcc commands.
BTW, if you are still without Rob's Quick Reference Guide, you need to :
1. Get with the program
2. Get your head read
3. Get one of them
( Pick one or more of the above that applies. OK, #3 and any of the other 2 that applies )
Edit : Sorry,
I forgot to mention that you can create another db on the same server, assuming you have the space, and do the dump & load to that db. You can then still run the DBCCs against the secondary db on the same server. No costs/need for extra hardware this way.
I assume you have some quiet time in the daily cycle for a maintenance window - or does the updates continue for a 24 hour cycle ?
If they continue, you may end up with a lot of "false positives", or data integrity errors that are falsely reported due to checking the database allocation and integrity while data changes are taking place. In this case, the secondary database is a better alternative as this would remain static for the duration of the checks.
If you do have a maintenance window, rather run the dbcc checkstorage against the original database - it will NOT take 10 hours.
I had also thought in this possibility but I thought that I had the same problem: the big table would be locked. But now I have read that it doesn't have the same behaviour, so it doesn't lock the table (at least completly).
So, I have started to created dbccdb! Thank you again for the response!