|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Profiler trace grows very fastHi,
I have a profiler trace running every 30 minutes in a production database, The trace include SQL:STMTCompleted, SQL:BatchCompleted, RPC:Completed, SP:Recompile then 16 columns. The only filter I put aside from the profiler itself is more than 3 seconds of duration. and the max size is 20MB. Normally, when it runs for 30 minutes the file size is only 1-3MB, now it runs only few seconds, maybe, around 1 minute, it reached the 20MB already and the trace stopped but when I check the content, only few entries, maybe less than 100 entries are logged. Any known issue? It only happen today. No changes was made in the profiler job and script. any quick feedback is appreciated. Thanks. What columns are you tracing? Some things that can cause lots of data are
Binary data, Showplans and Textdata if the batch is large. Are you sure there is only 100 events in the file? -- Show quoteAndrew J. Kelly SQL MVP Solid Quality Mentors "Bry" <fqies_***@yahoo.com> wrote in message news:1189134037.581969.34030@y42g2000hsy.googlegroups.com... > Hi, > > I have a profiler trace running every 30 minutes in a production > database, The trace include SQL:STMTCompleted, SQL:BatchCompleted, > RPC:Completed, SP:Recompile then 16 columns. The only filter I put > aside from the profiler itself is more than 3 seconds of duration. and > the max size is 20MB. Normally, when it runs for 30 minutes the file > size is only 1-3MB, now it runs only few seconds, maybe, around 1 > minute, it reached the 20MB already and the trace stopped but when I > check the content, only few entries, maybe less than 100 entries are > logged. Any known issue? It only happen today. No changes was made in > the profiler job and script. > > any quick feedback is appreciated. Thanks. > Bry (fqies_***@yahoo.com) writes:
> I have a profiler trace running every 30 minutes in a production I can't say what is going on, but I would encourage you to look into> database, The trace include SQL:STMTCompleted, SQL:BatchCompleted, > RPC:Completed, SP:Recompile then 16 columns. The only filter I put > aside from the profiler itself is more than 3 seconds of duration. and > the max size is 20MB. Normally, when it runs for 30 minutes the file > size is only 1-3MB, now it runs only few seconds, maybe, around 1 > minute, it reached the 20MB already and the trace stopped but when I > check the content, only few entries, maybe less than 100 entries are > logged. Any known issue? It only happen today. No changes was made in > the profiler job and script. > > any quick feedback is appreciated. Thanks. server-side traces, since they take less resources than running traces from Profiler. It's fairly easy to get a kickstart with server-side traces. Set up the trace in Profiler, and then save the script from Profiler. Read about the various sp_trace_ procedures and fn_trace functions in Books Online. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx On Sep 8, 5:11 am, Erland Sommarskog <esq***@sommarskog.se> wrote:
Show quote > Bry (fqies_***@yahoo.com) writes: Hi all,> > I have a profiler trace running every 30 minutes in a production > > database, The trace include SQL:STMTCompleted, SQL:BatchCompleted, > > RPC:Completed, SP:Recompile then 16 columns. The only filter I put > > aside from the profiler itself is more than 3 seconds of duration. and > > the max size is 20MB. Normally, when it runs for 30 minutes the file > > size is only 1-3MB, now it runs only few seconds, maybe, around 1 > > minute, it reached the 20MB already and the trace stopped but when I > > check the content, only few entries, maybe less than 100 entries are > > logged. Any known issue? It only happen today. No changes was made in > > the profiler job and script. > > > any quick feedback is appreciated. Thanks. > > I can't say what is going on, but I would encourage you to look into > server-side traces, since they take less resources than running traces > from Profiler. It's fairly easy to get a kickstart with server-side > traces. Set up the trace in Profiler, and then save the script from > Profiler. > > Read about the various sp_trace_ procedures and fn_trace functions in > Books Online. > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx The traces are at its normal size since noon of Friday last week, I didn't do any fixes. it just back to normal. a weird behavior. Here are the columns included in the trace: EventClass, TextData, NTUserName, Hostname, ClientProcessID, ApplicationName, LoginName, SPID, Duration, StartTime, Reads, Writes, CPU, EventSubClassIndexID, ServerName. There is no unusual entries in textdata that could be cause of very large data that could make a 20MB trace file. I am using sp_trace_* procedures since the profiler is setup as a job that dump trace to trace files. Server_trace is similar to perfmon? Sounds like too much beer that day :). Hopefully it was just a fluke wand
wont reoccur again. -- Show quoteAndrew J. Kelly SQL MVP Solid Quality Mentors "fqiesbry" <fqies_***@yahoo.com> wrote in message news:1189391445.447254.12490@22g2000hsm.googlegroups.com... > On Sep 8, 5:11 am, Erland Sommarskog <esq***@sommarskog.se> wrote: >> Bry (fqies_***@yahoo.com) writes: >> > I have a profiler trace running every 30 minutes in a production >> > database, The trace include SQL:STMTCompleted, SQL:BatchCompleted, >> > RPC:Completed, SP:Recompile then 16 columns. The only filter I put >> > aside from the profiler itself is more than 3 seconds of duration. and >> > the max size is 20MB. Normally, when it runs for 30 minutes the file >> > size is only 1-3MB, now it runs only few seconds, maybe, around 1 >> > minute, it reached the 20MB already and the trace stopped but when I >> > check the content, only few entries, maybe less than 100 entries are >> > logged. Any known issue? It only happen today. No changes was made in >> > the profiler job and script. >> >> > any quick feedback is appreciated. Thanks. >> >> I can't say what is going on, but I would encourage you to look into >> server-side traces, since they take less resources than running traces >> from Profiler. It's fairly easy to get a kickstart with server-side >> traces. Set up the trace in Profiler, and then save the script from >> Profiler. >> >> Read about the various sp_trace_ procedures and fn_trace functions in >> Books Online. >> >> -- >> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >> >> Books Online for SQL Server 2005 >> athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... >> Books Online for SQL Server 2000 >> athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > Hi all, > > The traces are at its normal size since noon of Friday last week, I > didn't do any fixes. it just back to normal. a weird behavior. Here > are the columns included in the trace: EventClass, TextData, > NTUserName, Hostname, ClientProcessID, ApplicationName, LoginName, > SPID, Duration, StartTime, Reads, Writes, CPU, EventSubClassIndexID, > ServerName. > > There is no unusual entries in textdata that could be cause of very > large data that could make a 20MB trace file. > > I am using sp_trace_* procedures since the profiler is setup as a job > that dump trace to trace files. Server_trace is similar to perfmon? > fqiesbry (fqies_***@yahoo.com) writes:
> I am using sp_trace_* procedures since the profiler is setup as a job I'm a little confused here. Running sp_trace_* directly is precisely a > that dump trace to trace files. Server_trace is similar to perfmon? server-side trace, so I don't really see where Profiler comes in. Yes, Profiler uses the same procedures, but usually when one says "Profiler", this implies using the GUI tool. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx That was a weird behavior. Luckily, no issues happened that time
because no data has been logged for several trace file. only at the starting point are logged. less than 1 minute of activities. Erland, thanks for clarifying some terminologies. :) Thanks all. If someone here experienced such case, and you have find the cause, please let me know. ;)  |
|||||||||||||||||||||||
Other interesting topics