June 6, 2003 at 4:00 am
we currently run our DBCC checkdb,checkcatalog, and newalloc jobs during a dedicated slot against a 6.5 service. We are migrating to 2000 and will be running 24/7. Is it O.K. to run these DBCC jobs while the databases are up and running ?
thanks
June 6, 2003 at 9:49 am
We have around 50+ 24/7 servers. We run DBCC 3 times a week. Yes I agree DBCC blocks the user process, especially when you rebuild the indexes. You need to run during the minimal activity on the server. We usually run early morning and some times midnight.
Shas3
June 6, 2003 at 10:01 am
From BOL.
"In SQL Server 2000, DBCC CHECKDB holds a schema lock on the table to prevent meta data changes while the table is being checked, thus allowing DML statements but not any data definition language (DDL) statements on the tables being checked. This change provides greater flexibility as to when you can run DBCC CHECKDB because DBCC CHECKDB does not deny system usage completely to the users.
DBCC CHECKDB is a CPU- and disk-intensive operation. Each data page that requires checking must first be read from disk into memory. In addition, DBCC CHECKDB uses tempdb to do sorting.
If actively performing transactions while DBCC CHECKDB is running, the transaction log continues to grow because the DBCC command blocks log truncation until it has finished reading the log.
It is recommended that DBCC CHECKDB be run during hours when the load is light on the server. If DBCC CHECKDB is run during heavy peak usage time, expect a performance hit on the transaction throughput as well as DBCC CHECKDB completion time."
"DBCC CHECKDB validates the integrity of everything in a database. There is no need to run DBCC CHECKALLOC or DBCC CHECKTABLE if DBCC CHECKDB either is currently or has been recently executed."
" DBCC NEWALLOC is identical to DBCC CHECKALLOC and is included in Microsoft® SQL Server™ 2000 for backward compatibility only. It is recommended that DBCC CHECKALLOC be used to check the allocation and use of all pages in the specified database."
Consider run DBCC INDEXDEFRAG if you are really unable to run DBCC DBREINDEX that could block users activities during its running.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply