May 8, 2023 at 10:50 am
Hello.
I posted previously about DBCC CHECKDB and Backups and changes are under review - thanks for feedback on here.
This is related - but a very specific question about DBCC CHECKDB - here is what I am seeing.
DBCC CHECKDB runs against Database A in SQL Instance A.
On certain days of the month a job runs (last night it ran for 3.5 hours) and while it is running it seems to 'lock out' DBCC CHECKDB until it completes.
There are multiple issue with the job itself which I will be raising with the Developers tomorrow, including another request to move the start time of the job - but this may just move the problem and not resolve it.
The thing is that whilst the job executes in SQL Instance A, and references multiple databases in that instance (and another), it does not reference Database A.
There is no 'Blocking' as such, the WAITS I am seeing for DBCC CHECKDB are LATCH_EX.
I see no Memory Pressure recorded (nothing in the last 7 days).
So DBCC CHECKDB is trying the get an Exclusive Latch on the DB and can't even though it is a different DB?
Or is it the fact that the SSIS Package (?) containing the DBCC CHECKDB is set to check all DBs in the SQL Instance - including the ones being referenced in the job - despite that fact that the CHECKDBs have already completed for those DBS?
Or is it something to do with TEMPDB - since I understand that the batch job outputs to TEMPDB and I believe from some posts that DBCC CHECKDB also uses TEMPDB? I have to say that we have 8 TEMPDB files, with no indication of contention elsewhere, and tracking pages used does not indicate anything excessive in terms of pages used. No indication of IO issues - although the write activity to TEMPDB seems to be concentrated between the first (1/3) and last (2/3) hours of the 4 - with very little in between.
I am probably going to have find out when the job runs next and query in real time (just seen this is 15th - so a weeks time - just looking for any wisdom on what is likely to be causing this if anybody knows?
Thanks.
Steve O.
May 8, 2023 at 12:42 pm
Based on the information you provided, it's not too clear. Could you please elaborate as it can be because of different factors such as blocking issues, job execution using more into temporary database TempDB, maybe the use of lots of resources running on that instance,
May 9, 2023 at 8:10 am
Hello Priyanka.
Thanks for the response.
The system is fairly quiet ( 1:00AM-4:30AM) - little resource being used by other work.
I have checked IO, CPU and Memory and the lack of Blocking (we use Solarwinds DPA repository to record).
As to contention for TEMPDB, we are not seeing the allocated pages being fully utilised, and TEMPDB has not extended since the last restart.
Reagrds
Steve O.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply