Server side tracing - where is the data stored?

Question

Server side tracing - where is the data stored?

derek.colley

SSCrazy Eights

Points: 8040
More actions
June 18, 2012 at 4:31 am

#258186

Maybe a silly question from me today but ... I've set up a server-side trace with a max size of 5GB and a target path to a .trc file.
The trace is running, everything seems fine, except the .trc isn't being written to - it's created, but 0 bytes.
I surmise that the event data is being kept within, possibly, tempdb? And it will be flushed to disk once the trace is stopped? Does anyone know if this is the case?
I ask because now I will have to keep an eye on tempdb, since the trace is going to be quite large. I've put in a stop time as a safety net (in case I get hit by a bus on my lunch break, or something).
Can't find much on BOL about it except for http://technet.microsoft.com/en-us/library/cc293613.aspx which doesn't provide the answer.
Cheers.
---
Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:
Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It goes into the file. The explorer properties may not be up to date until the trace is stopped. TempDB is not involved at all.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 2

Righto. Thanks for the reply. Trace is going to stop in about half an hour so I'll probably find the file populated with data after that. I put a monitoring script in place for the trace in the meantime so I can keep an eye on event_count and file_position from sys.traces, if I start seeing silly numbers I'll stop the trace.

Thanks again.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Yes, I noticed that only last week. The size of the trace file in Explorer is 0KB until the trace has stopped.

John

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

SQL Server will buffer the trace results for performance reasons. Depending on what you set your trace file size to, and presumably what else is going on with the server at the time, SQL Server will sometimes write to the file multiple times in smaller increments.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato