Default trace - high read activity on disk

Question

Default trace - high read activity on disk

Paul Cresham

Hall of Fame

Points: 3623
More actions
April 18, 2018 at 2:01 pm

#334394

I've noticed on one of our servers running SQL Server 2014 Enterprise in Resource Monitor the default trace file (log_298.trc in this instance) is being constantly read by sqlservr.exe at 3-400,000 B/sec. On other similar servers there is a constant low-rate write to this file which one might expect but I can not figure out what is reading this file constantly at such a rate.
Does anyone have any idea what it might be or how I can trace what is reading it?
I have tried disabling the default trace and then re-enabling it. This causes the new trace file to initially start being written at a low rate, but after a short while starts getting read again at the high rate. If I could just figure out what is causing it then I can probably track it down, but there don't appear to be any SPIDs involved in this so I'm at a loss where to look.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Paul Cresham - Wednesday, April 18, 2018 2:01 PM
I've noticed on one of our servers running SQL Server 2014 Enterprise in Resource Monitor the default trace file (log_298.trc in this instance) is being constantly read by sqlservr.exe at 3-400,000 B/sec. On other similar servers there is a constant low-rate write to this file which one might expect but I can not figure out what is reading this file constantly at such a rate.
Does anyone have any idea what it might be or how I can trace what is reading it?
I have tried disabling the default trace and then re-enabling it. This causes the new trace file to initially start being written at a low rate, but after a short while starts getting read again at the high rate. If I could just figure out what is causing it then I can probably track it down, but there don't appear to be any SPIDs involved in this so I'm at a loss where to look.

Do you have some form of monitoring software running on the server like "SpotLight" or some such?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 2

We are running Idera's SQL Diagnostic Manager and we also have some Red Gate software, but I was under the impression that Idera used extended events and if not it would just read the trace file itself (so wouldn't show as SQL Server being the process reading the file) and Red Gate's DLM stuff runs off database triggers. But we run these against various servers too and only one of them is showing this behaviour so on those bases I ruled them out - but could I have been wrong in doing so?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Paul Cresham - Thursday, April 19, 2018 5:38 AM
We are running Idera's SQL Diagnostic Manager and we also have some Red Gate software, but I was under the impression that Idera used extended events and if not it would just read the trace file itself (so wouldn't show as SQL Server being the process reading the file) and Red Gate's DLM stuff runs off database triggers. But we run these against various servers too and only one of them is showing this behaviour so on those bases I ruled them out - but could I have been wrong in doing so?

Possibly. I agree that the Red Gate DLM stuff probably isn't the problem but I usually don't rule anything out until I know the actual cause. I'd also be looking for some form of audit software running but wouldn't limit it to that.

I've also not experienced this issue and, hopefully, someone that has run into this before will chime in.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 4

Thanks, Jeff. I will have another scout about for any other software and will try pausing/disabling Idera (or stopping its services on the server it runs on) to see if that helps and as you say hopefully someone who has seen this before might see this in the meantime!