|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Getting data from sp_trace_getdata SPHi,
I want to get data from the sp_Trace_getdata SP in SQL Server 2005, but if I pass "1" as the second argument I get only 1 event and all the events after that are cleared. Secondly if I use the "0" as the second argument the SP returns only when the trace is being stopped, I want a way by which I can get the data and put it into a table. Writing to a file is an option but I want the data to be periodically put into a table. Now suppose I stop the trace for that amount of time I might miss out on the events in that time frame. TIA Prasad. Prasad (ekke_nik***@yahoo.co.uk) writes:
> I want to get data from the sp_Trace_getdata SP in SQL Server 2005, sp_trace_getdata is not described in Books Online, which would indicate> but if I pass "1" as the second argument I get only 1 event and all the > events after that are cleared. > Secondly if I use the "0" as the second argument the SP returns only > when the trace is being stopped, I want a way by which I can get the data > and put it into a table. > Writing to a file is an option but I want the data to be periodically > put into a table. Now suppose I stop the trace for that amount of time I > might miss out on the events in that time frame. that it's not supported. Any special reason you don't want to use fn_trace_gettable()? By the way, it is much recommended to trace to file, as this is far leaner on recources than tracing to a table. In SQL fn_trace_gettable is able to read from a file, even if a trace is running. -- 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 Thnx Erland
I understand that fn_trace_gettable will read the trace file even if the trace is running but suppose for the 1st time I read the file and put the data into a table, now I want to delete the trace file or the contents that I have read into a table, I dont want this duplication of data. Is it possible some how ? I was thinking of stopping the trace and creating another one and deleting the old trace files. But I might loose events the work around I thought was to start the next trace before stopping the previous trace which would lead to duplication of data. Any suggestions ? Thanks Prasad Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9811F25D47C97Yazorman@127.0.0.1... > Prasad (ekke_nik***@yahoo.co.uk) writes: >> I want to get data from the sp_Trace_getdata SP in SQL Server 2005, >> but if I pass "1" as the second argument I get only 1 event and all the >> events after that are cleared. >> Secondly if I use the "0" as the second argument the SP returns only >> when the trace is being stopped, I want a way by which I can get the data >> and put it into a table. >> Writing to a file is an option but I want the data to be periodically >> put into a table. Now suppose I stop the trace for that amount of time I >> might miss out on the events in that time frame. > > sp_trace_getdata is not described in Books Online, which would indicate > that it's not supported. > > Any special reason you don't want to use fn_trace_gettable()? > > By the way, it is much recommended to trace to file, as this is far > leaner on recources than tracing to a table. In SQL fn_trace_gettable > is able to read from a file, even if a trace is running. > > > -- > 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 Prasad (ekke_nik***@yahoo.co.uk) writes:
> I understand that fn_trace_gettable will read the trace file even if the Why not just delete the contents in the table, and reload it again with > trace is running but suppose for the 1st time I read the file and put > the data into a table, now I want to delete the trace file or the > contents that I have read into a table, I dont want this duplication of > data. Is it possible some how ? fn_trace_gettable? Or add a filter on StartTime, and only load new events. (But this is a little devilish, as you could miss Completed events that started before your previous load.) Alas, fn_trace_gettable does not come with a useful primary key. And you can even load different reads of the trace into different tables. -- 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  |
|||||||||||||||||||||||
Other interesting topics