InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 11:13:46 GMT20http://www.sqlservercentral.com/Forums/Topic576752-146-1.aspxI am sorry I can start a new thread.To answer your question:Yes, I have started and stopped the profiler trace before scripting it.Wed, 12 Oct 2011 13:51:26 GMTsrilu_bannuhttp://www.sqlservercentral.com/Forums/Topic576752-146-1.aspx[quote][b]srilu_bannu (10/12/2011)[/b][hr]Sounds easy.But i was unable to script it.I went to file menu and then opened file and went to script file.It is not working.[/quote]Best to start a new thread really.Have you started and stopped the trace before attempting to script it?Wed, 12 Oct 2011 13:38:36 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic576752-146-1.aspxSounds easy.But i was unable to script it.I went to file menu and then opened file and went to script file.It is not working.Wed, 12 Oct 2011 13:18:15 GMTsrilu_bannuhttp://www.sqlservercentral.com/Forums/Topic576752-146-1.aspx[quote][b]DBA Cabuloso (7/10/2009)[/b]Do you know some way to enable the trace without using the profiler, save the data in tables, instead of in files? I really prefer to save in tables.[/quote]That's where the [code]SELECT * INTO temp_trcFROM ::fn_trace_gettable('d:\trace\mytrace.trc', default)[/code]comes in play... reading the trace files after the fact and that way you can also insert them into a table for analysis.Fri, 10 Jul 2009 13:41:56 GMTRichard M.http://www.sqlservercentral.com/Forums/Topic576752-146-1.aspxHello,I want to profile and write to a table. But in the script generated by the profiler, in the export funcionality, the following code is written:[code]-- Writing to a table is not supported through the SP's[/code]Do you know some way to enable the trace without using the profiler, save the data in tables, instead of in files? I really prefer to save in tables.Thanks in advanceDBA CabulosoFri, 10 Jul 2009 12:59:01 GMTDBA Cabulosohttp://www.sqlservercentral.com/Forums/Topic576752-146-1.aspx[quote][b]Lowell (6/3/2009)[/b][hr][quote][b]ALZDBA (6/3/2009)[/b][hr]Attached you'll find a version I use for quick and dirty CPR-trace ;-)[/quote]ALZDBA the version you posted has all the CrLf stripped out, making it difficult to test, since we have to reformat manually; can you edit it or repaste it?[/quote]You need to change the file extention !(just remove the .txt and keep the .SQL)If you then open it with ssms, it will be formatted OK.(I downloaded it from this thread, removed the .txt and it appeared ok in SSMS)Thu, 04 Jun 2009 00:28:00 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic576752-146-1.aspx[quote][b]ALZDBA (6/3/2009)[/b][hr]Attached you'll find a version I use for quick and dirty CPR-trace ;-)[/quote]ALZDBA the version you posted has all the CrLf stripped out, making it difficult to test, since we have to reformat manually; can you edit it or repaste it?Wed, 03 Jun 2009 06:43:17 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic576752-146-1.aspxTnx.Found some similar stuff myself, but your's more detailed & extended.Tnx again.Wed, 03 Jun 2009 06:23:13 GMTFreeHansjehttp://www.sqlservercentral.com/Forums/Topic576752-146-1.aspxAttached you'll find a version I use for quick and dirty CPR-trace ;-)Wed, 03 Jun 2009 05:33:27 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic576752-146-1.aspx[i]easyest way is to use sql server profiler.Create your trace with all filters (db) you want and script it.Then launch it in a startup job or procedure.[/i]Got that part, but the trace stops almost immediately. Since the job starts the trace-script, should I add the StopTrace and StartTrace procedures?Wed, 03 Jun 2009 05:18:23 GMTFreeHansjehttp://www.sqlservercentral.com/Forums/Topic576752-146-1.aspx[quote][b]Lowell (3/19/2009)[/b][hr]the level of granularity seemed a bit tedious to invoke.Thanks![/quote]its very rare though you would capture every column for an event ;)Thu, 19 Mar 2009 12:42:47 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic576752-146-1.aspxyeah, for event 12 SQL:BatchCompleted, even when everything is enabled, there is a lot of null columns, i can see that you want to capture just certain data depending on the event you are watching, but the level of granularity seemed a bit tedious to invoke.Thanks!Thu, 19 Mar 2009 11:14:33 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic576752-146-1.aspx[quote][b]Lowell (3/19/2009)[/b][hr]That's what made me think there must be a reason to enable some columns and not all columns.[/quote]Hi Lowellyou only really want to capture the events\columns you require otherwise reading the trace can be a little tedious. To capture a column for an event you need to use the setevent SP for each column. I showed the 2 columns for event 12 as an example, but you could specify any valid event\column (be aware not all events use all columns). Its really down to the events\columns you want to see.Thu, 19 Mar 2009 11:00:10 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic576752-146-1.aspxPerry your code example was very helpful for me today, as I wanted to create a DML trace to be a companion to the existing DDL default trace.so I played around with your example a bit, and saw your example trace enabled just two columns, the TextData and DbId;i took it a step further, and enabled columns i thought might be useful for auditing SQL statements...using the [b]sp_trace_setevent command[/b], I added everything I could find related to the user/login performing the action, and also everything i could find related to performance times,it worked exactly as I expected, and could help track down issues in the future.my question is really this; do you know if there is any impact of just simply enabling all 64 columns in the trace, instead of a select group of my 12 columns I thought were useful. I'll find out the hard way, since I'll leave it enabled to see how well it does, but it kind of seems like a monotonously wasteful timewise to explicitly call [b]sp_trace_setevent[/b] for all 64 columns, instead of having all columns enabled by default.That's what made me think there must be a reason to enable some columns and not all columns.Thu, 19 Mar 2009 10:28:07 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic576752-146-1.aspxHi Daksh, Its very usefull , thanks man..:)RegardsSivMon, 16 Mar 2009 04:21:31 GMTsivashankar-350147http://www.sqlservercentral.com/Forums/Topic576752-146-1.aspxeasyest way is to use sql server profiler.Create your trace with all filters (db) you want and script it.Then launch it in a startup job or procedure.Wed, 10 Dec 2008 02:04:01 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic576752-146-1.aspxI would like to know how to AutoTrace for a specific database, if i have 5 databases in the server and i need to enable the trace or autotrace to only 3 datbases which i like. So how do i go with it. Please helpWith RegardsDakshinTue, 09 Dec 2008 22:53:53 GMTdakshinamurthy-655138http://www.sqlservercentral.com/Forums/Topic576752-146-1.aspx[quote][b]ALZDBA (9/27/2008)[/b][hr]I think my little article regarding [b]sqlserver and SOx[/b] can help out.[quote]http://www.sqlservercentral.com/articles/Security/3203/[/quote][/quote]its ok but i think he just wanted a quick and dirty guide on the usage of the SP's and functions ;)I think they get a lot of peoiple confused first time roundTue, 30 Sep 2008 06:48:59 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic576752-146-1.aspxI think my little article regarding [b]sqlserver and SOx[/b] can help out.[quote]http://www.sqlservercentral.com/articles/Security/3203/[/quote]Sat, 27 Sep 2008 05:08:37 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic576752-146-1.aspxHi Perry Whittle,Thanks a lot, it worked and now i am able use sql trace automatically.With RegardsDakshinaSat, 27 Sep 2008 01:25:53 GMTdakshinamurthy-655138http://www.sqlservercentral.com/Forums/Topic576752-146-1.aspx[quote][b]dakshinamurthy (9/26/2008)[/b][hr]Hi, I would like to start and store the sql profiler trace in a location, but it should be automated, i mean to say instead of starting the sql profiler for a particular db manually is there any way where in i can schedule the tracing to start every day and stop. So that we can read the sql server trace later whenever we need.With RegardsDakshina[/quote]Hi Daksinathe 4 stored procedures you need areSP_TRACE_CREATESP_TRACE_SETEVENTSP_TRACE_SETFILTERSP_TRACE_SETSTATUSalso the functionsfn_trace_gettablefn_trace_getinfoto generate a trace definition use the following syntax[code]declare @traceidout intdeclare @maxfilesize bigintdeclare @on bitset @on = 1set @maxfilesize = 50exec sp_trace_create @traceidout output, 2, N'D:\Trace\mytrace', @maxfilesize, NULLexec sp_trace_setevent @traceidout, 12, 1, @onexec sp_trace_setevent @traceidout, 12, 3, @on[/code]change @maxfilesize to whatever value you require (its in MB)Dont append the .TRC to the path above it does it for you.refer to BOL for all trace events and columnsset a filter using the following syntax[code]exec sp_trace_setfilter @traceidout, 3, 0, 0, 7[/code]use the following to get your trace details and ID[code]select * from ::fn_trace_getinfo(default)[/code]use the following to start, stop and close the trace(must stop a trace before it can be closed. Must be closed before you can access the file)[code]exec sp_trace_setstatus TRACEID, 1 --start traceexec sp_trace_setstatus TRACEID, 0 --stop traceexec sp_trace_setstatus TRACEID, 2 --close trace[/code]finally to output to a table stop and close the trace then use the following syntax[code]SELECT * INTO temp_trcFROM ::fn_trace_gettable('d:\trace\mytrace.trc', default)[/code]You can create SQL jobs and schedule them using the code above. As it runs server side there are no I\O nasties that a client would generate and you can schedule it at will:cool: ;)Fri, 26 Sep 2008 09:47:45 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic576752-146-1.aspxWhat you can is setup your trace using the Profiler GUI, you can then script out the T-SQL needed to run the profiler and use it in a sql server job, and schedule as and when you need it. To get the script the option can found under in the profiler GUIYou may need seperate job/step to stop the trace to...Look up the neccessary SP in BOL.Fri, 26 Sep 2008 07:38:32 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic576752-146-1.aspxHi, I would like to start and store the sql profiler trace in a location, but it should be automated, i mean to say instead of starting the sql profiler for a particular db manually is there any way where in i can schedule the tracing to start every day and stop. So that we can read the sql server trace later whenever we need.With RegardsDakshinaFri, 26 Sep 2008 06:41:21 GMTdakshinamurthy-655138