February 7, 2006 at 9:31 am
We have an ETL tool that runs a nightly job, performing only SELECT statements on the source SQL Server 2000 system. There has never been a table locking problem until recently and nothing within the job or the maintenance on the source system has changed, so I've been told. However, from the block process file that gets generated, the blocker has been shown to be related to a SQL Agent job, showing a DBCC CHECKDB happening. Also in the logs a DBCC CHECKDB is happening on several databases around the time our nightly job runs.
In order to stop the table locking, the ETL services on the ETL server have to be restarted to drop any connections to the source system. This can be several hours later when the table locking is noticed.
The lock always occurs on the same source table. The ETL process that pulls from that table only takes 4 seconds to run and pulls 1,600 records out of ~46k records.
Could the DBCC CHECKDB running before or after the ETL process begins cause the table locking? Why is it always the same table? Any explanation or further analysis advice would be appreciated.
February 7, 2006 at 3:22 pm
Lets clarify something here!
Are you syaing that the DBCC CHECKDB and the ETL is running at the same time each night?
If so, DBCC CHECKDB will always lock the tables!
Also, is the Database marked for Replication? If so is it possible that there may be a Snapshot taking place at the time of your ETL?
February 8, 2006 at 8:15 am
The DBCC CHECKDB is not happening every night, nor at the same time of day but the times that the table lock occurred, yes, the DBCC CHECKDB ran around those times. But why would the same table be getting locked when the ETL job hits many tables, could it just be timing?
Why would the ETL lock the table if the DBCC CHECKDB locks the database? We have to restart the ETL services to unlock the table. Wouldn't the DBCC CHECKDB prevent the ETL from proceeding and return an error message to the ETL Job Server if the database is being locked? The ETL logs show no indication of a lock.
February 8, 2006 at 3:23 pm
February 8, 2006 at 5:59 pm
Have you scheduled a wizard-generated DB Maintenance Plan that backs up the database or log at the same time? I believe these plans default to running the "DB Integrity Check" (DBCC CHECKDB). If so, disable it (uncheck the checkbox).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply