Solved

Keeping a running trace file of users SQL

Posted on 2006-10-26
14
534 Views
Last Modified: 2008-02-01
Hi everyone

I have Microsoft SQL 2000 Cluster up and running.  I'd like to get a running trace of users SQL - but I really don't want to have to manually start the SQL profiler and set up a trace file each and every time.

What I would like to do is either get a batch process started up - or use the microsoft SQL Server agent - to start up a process - maybe a stored procedure perhaps.  I'd then like to "capture" all SQL coming into the database and store the userID, date, time, and TSQL into a text file.  I'd like to also ensure that there is a filter - 99% of SQL comes in from an application that uses a user ID of "OWUSER" - so I don't want to see any of that SQL at all - just the users SQL.
0
Comment
Question by:altquark
  • 7
  • 5
14 Comments
 
LVL 1

Author Comment

by:altquark
ID: 17813907
I noticed there was a similar post not long ago - evidently creating a stored procedure and using the SP_TRACE_xxxx commands :


It is not necessary to be logged on. You can create a auto-startup stored procedure in master database.

use master
GO
/*
create disk trace file c:\SecureMSSQLTrace.trc, using template SQLProfilerStandard, roll over 200 MB, shutdown when the disk is full
autostart with SQL Server  
*/
create proc usp_SQLProfilerStandard AS begin
declare @P1 int
exec sp_trace_create @P1 output, 6, 'c:\SecureMSSQLTrace.trc', 200, NULL
exec sp_trace_setevent @P1, 10, 1, 1
exec sp_trace_setevent @P1, 10, 6, 1
exec sp_trace_setevent @P1, 10, 9, 1
exec sp_trace_setevent @P1, 10, 10, 1
exec sp_trace_setevent @P1, 10, 11, 1
exec sp_trace_setevent @P1, 10, 12, 1
exec sp_trace_setevent @P1, 10, 13, 1
exec sp_trace_setevent @P1, 10, 14, 1
exec sp_trace_setevent @P1, 10, 16, 1
exec sp_trace_setevent @P1, 10, 17, 1
exec sp_trace_setevent @P1, 10, 18, 1
exec sp_trace_setevent @P1, 10, 27, 1
exec sp_trace_setevent @P1, 12, 1, 1
exec sp_trace_setevent @P1, 12, 6, 1
exec sp_trace_setevent @P1, 12, 9, 1
exec sp_trace_setevent @P1, 12, 10, 1
exec sp_trace_setevent @P1, 12, 11, 1
exec sp_trace_setevent @P1, 12, 12, 1
exec sp_trace_setevent @P1, 12, 13, 1
exec sp_trace_setevent @P1, 12, 14, 1
exec sp_trace_setevent @P1, 12, 16, 1
exec sp_trace_setevent @P1, 12, 17, 1
exec sp_trace_setevent @P1, 12, 18, 1
exec sp_trace_setevent @P1, 12, 27, 1
exec sp_trace_setevent @P1, 14, 1, 1
exec sp_trace_setevent @P1, 14, 6, 1
exec sp_trace_setevent @P1, 14, 9, 1
exec sp_trace_setevent @P1, 14, 10, 1
exec sp_trace_setevent @P1, 14, 11, 1
exec sp_trace_setevent @P1, 14, 12, 1
exec sp_trace_setevent @P1, 14, 13, 1
exec sp_trace_setevent @P1, 14, 14, 1
exec sp_trace_setevent @P1, 14, 16, 1
exec sp_trace_setevent @P1, 14, 17, 1
exec sp_trace_setevent @P1, 14, 18, 1
exec sp_trace_setevent @P1, 14, 27, 1
exec sp_trace_setevent @P1, 15, 1, 1
exec sp_trace_setevent @P1, 15, 6, 1
exec sp_trace_setevent @P1, 15, 9, 1
exec sp_trace_setevent @P1, 15, 10, 1
exec sp_trace_setevent @P1, 15, 11, 1
exec sp_trace_setevent @P1, 15, 12, 1
exec sp_trace_setevent @P1, 15, 13, 1
exec sp_trace_setevent @P1, 15, 14, 1
exec sp_trace_setevent @P1, 15, 16, 1
exec sp_trace_setevent @P1, 15, 17, 1
exec sp_trace_setevent @P1, 15, 18, 1
exec sp_trace_setevent @P1, 15, 27, 1
exec sp_trace_setevent @P1, 17, 1, 1
exec sp_trace_setevent @P1, 17, 6, 1
exec sp_trace_setevent @P1, 17, 9, 1
exec sp_trace_setevent @P1, 17, 10, 1
exec sp_trace_setevent @P1, 17, 11, 1
exec sp_trace_setevent @P1, 17, 12, 1
exec sp_trace_setevent @P1, 17, 13, 1
exec sp_trace_setevent @P1, 17, 14, 1
exec sp_trace_setevent @P1, 17, 16, 1
exec sp_trace_setevent @P1, 17, 17, 1
exec sp_trace_setevent @P1, 17, 18, 1
exec sp_trace_setevent @P1, 17, 27, 1
exec sp_trace_setstatus @P1, 1
end
GO
exec sp_procoption @ProcName= 'usp_SQLProfilerStandard' , @OptionName =  'startup' , @OptionValue = 'on'
GO
exec proc usp_SQLProfilerStandard
GO

as well as

sp_trace_setfilter  1, 10, 0, 6, N'SQLT%'
sp_trace_setfilter  1, 10, 0, 6, N'MS%'
sp_trace_setfilter  1, 11, 0, 0, N'joe'

to set up the filters.

I'm a dullard - and I really need a little more to get me going.  This is a little confusing, and I'm looking for an easy answer.  What I want is JUST the TSQL, UserID, DATE and TIME to be logged to the file - and only for users NOT like 'OWUSER%', 'TRACKATOOL%' or 'sa'

Can someone adapt the above so I can put onto our server ?  Big 500point reward to whoever helps !
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17815837
altquark I am facing same issue , exec sp_trace_setstatus is just "Adds or removes an event or event column to a trace. sp_trace_setevent may be executed only on existing traces that are stopped (status is 0). Microsoft® SQL Server™ 2000 will return an error if this stored procedure is executed on a trace that does not exist or whose status is not 0."

I manually start my SQL profiler all the time when sql server starts.
You can save your trace template so you dont have to re-create it everytime but you will have to manually start profiler, I havent had the time to look deeply into it, but I am sure it can be done.

rw3admin
0
 
LVL 1

Author Comment

by:altquark
ID: 17817320
Maybe theres a way that I can start the profiler from the command line perhaps ?  I wouldn't mind being able to start the process from the scheduler in a batch script, and using PSTools to kill the profiler each night, backup the trace logs, then restart - can the profiler be activated from the command line with a trace file ?
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17819808
so far what I have seen that doesnt look like a possibility, I wonder if one can write a Macro to do this...
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17819898
0
 
LVL 1

Author Comment

by:altquark
ID: 17820131
well - that is promising, it seems the profiler might have some command line options.  Anyone want to post a follow up on what all the command options might be ?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:altquark
ID: 17820158
while looking, I did find this :

http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx

so do I earn my reward myself !  I still like to hear and see from someone who has implemented this type of auditing !
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17820218
aha, I found something too... maybe we both found something same... let me read your URL first
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17820267
sorry man I was also fighting with a server that I thought had some data corruption .... whew... saved
ok I just glanced at your link thats good info, I was thinking of creating a DTS with the create trace script that you have and starting DTS package from command line. Ofcourse SQL takes a while to come online after OS starts so I was thinking of writing code in batch file that would first confirm if sqlservice is running and profiler service is not running start DTS the DTS package.

Dont worry about points man, you can go to expert care and ask a moderator to close this question

rw3admin
0
 
LVL 1

Author Comment

by:altquark
ID: 17894770
OK - here goes !

This is what we were looking for - I updated this subject in case anyone wants to see :






/*
Script to start a trace on the database
roll over after 512mb
SHUTDOWN WHEN THE DISK IS FULL
AUTOSTART WITH SQL SERVER

written by Jon Steel and Steve Cambric
11/07/2006
*/

CREATE  PROC USP_SQLProfilerStandard AS BEGIN

-- Create a Queue
Print 'Begin creating a queue.  Declaring variables.'
declare @rc int
declare @traceid int
declare @maxfilesize bigint
-- declare @maxfilesize int
DECLARE @filename nvarchar(1000)
declare @on bit
declare @starttime datetime
declare @durationsec int
declare @subject varchar(100)
declare @servername varchar(100)

/************************************************************************************/
--Will need to set to server local path
SET @filename = 'C:\SQLProfilerTrace_'
/************************************************************************************/



Print 'Setting variables equal to values...'
Print 'Setting server name...'

-- Takes only first 9 char of server name to handle named instances...NBRDS1611\NBRDS1624 would return NBRDS1624
SET @servername = (SELECT LEFT(CONVERT(varchar(100),  @@servername),9))

PRINT 'Setting filename and path...'

SET @filename = @filename + @servername + ''  +  '_' + convert( varchar, GETDATE(), 112) + REPLACE( convert( varchar, GETDATE(), 108), ':', '')

Print 'Setting maxfilesize...'
      set @maxfilesize = 512
Print 'Executing sp_trace_create with traceID output, filename, maxfilesize...'
      exec @rc = sp_trace_create @traceid output, 2, @filename, @maxfilesize, NULL
      if (@rc != 0) goto error
      
      -- Set the events
Print 'Setting @on = 1...executes sp_trace_setevent for trace items...'
      set @on = 1

-- New List, columns, and descriptions

      exec sp_trace_setevent @traceid, 10, 1, @on --RPC:Completed, TextData
      exec sp_trace_setevent @traceid, 10, 2, @on --RPC:Completed, BinaryData
      exec sp_trace_setevent @traceid, 10, 3, @on --RPC:Completed, DatabaseID
      exec sp_trace_setevent @traceid, 10, 4, @on --RPC:Completed, TransactionID
      exec sp_trace_setevent @traceid, 10, 6, @on --RPC:Completed, NTUserName
      exec sp_trace_setevent @traceid, 10, 7, @on --RPC:Completed, NTDomainName
      exec sp_trace_setevent @traceid, 10, 8, @on --RPC:Completed, ClientHostName
      exec sp_trace_setevent @traceid, 10, 9, @on --RPC:Completed, ClientProcessID
      exec sp_trace_setevent @traceid, 10, 10, @on --RPC:Completed, ApplicationName
      exec sp_trace_setevent @traceid, 10, 11, @on --RPC:Completed, SQLSecurityLoginName
      exec sp_trace_setevent @traceid, 10, 12, @on --RPC:Completed, SPID
      exec sp_trace_setevent @traceid, 10, 13, @on --RPC:Completed, Duration
      exec sp_trace_setevent @traceid, 10, 14, @on --RPC:Completed, StartTime
      exec sp_trace_setevent @traceid, 10, 15, @on --RPC:Completed, EndTime
      exec sp_trace_setevent @traceid, 10, 16, @on --RPC:Completed, Reads
      exec sp_trace_setevent @traceid, 10, 17, @on --RPC:Completed, Writes
      exec sp_trace_setevent @traceid, 10, 18, @on --RPC:Completed, CPU
      exec sp_trace_setevent @traceid, 10, 21, @on --RPC:Completed, EventSubClass
      exec sp_trace_setevent @traceid, 10, 22, @on --RPC:Completed, ObjectID
      exec sp_trace_setevent @traceid, 10, 25, @on --RPC:Completed, IntegerData
      exec sp_trace_setevent @traceid, 10, 27, @on --RPC:Completed, Eventclass
      
      exec sp_trace_setevent @traceid, 11, 1, @on --RPC:Started, TextData
      exec sp_trace_setevent @traceid, 11, 2, @on --RPC:Started, BinaryData
      exec sp_trace_setevent @traceid, 11, 3, @on --RPC:Started, DatabaseID
      exec sp_trace_setevent @traceid, 11, 4, @on --RPC:Started, TransactionID
      exec sp_trace_setevent @traceid, 11, 6, @on --RPC:Started, NTUserName
      exec sp_trace_setevent @traceid, 11, 7, @on --RPC:Started, NTDomainName
      exec sp_trace_setevent @traceid, 11, 8, @on --RPC:Started, ClientHostName
      exec sp_trace_setevent @traceid, 11, 9, @on --RPC:Started, ClientProcessID
      exec sp_trace_setevent @traceid, 11, 10, @on --RPC:Started, ApplicationName
      exec sp_trace_setevent @traceid, 11, 11, @on --RPC:Started, SQLSecurityLoginName
      exec sp_trace_setevent @traceid, 11, 12, @on --RPC:Started, SPID
      exec sp_trace_setevent @traceid, 11, 13, @on --RPC:Started, Duration
      exec sp_trace_setevent @traceid, 11, 14, @on --RPC:Started, StartTime
      exec sp_trace_setevent @traceid, 11, 15, @on --RPC:Started, EndTime
      exec sp_trace_setevent @traceid, 11, 16, @on --RPC:Started, Reads
      exec sp_trace_setevent @traceid, 11, 17, @on --RPC:Started, Writes
      exec sp_trace_setevent @traceid, 11, 18, @on --RPC:Started, CPU
      exec sp_trace_setevent @traceid, 11, 21, @on --RPC:Started, EventSubClass
      exec sp_trace_setevent @traceid, 11, 22, @on --RPC:Started, ObjectID
      exec sp_trace_setevent @traceid, 11, 25, @on --RPC:Started, IntegerData
      exec sp_trace_setevent @traceid, 11, 27, @on --RPC:Started, Eventclass

      exec sp_trace_setevent @traceid, 12, 1, @on --SQL:BatchCompleted, TextData
      exec sp_trace_setevent @traceid, 12, 2, @on --SQL:BatchCompleted, BinaryData
      exec sp_trace_setevent @traceid, 12, 3, @on --SQL:BatchCompleted, DatabaseID
      exec sp_trace_setevent @traceid, 12, 4, @on --SQL:BatchCompleted, TransactionID
      exec sp_trace_setevent @traceid, 12, 6, @on --SQL:BatchCompleted, NTUserName
      exec sp_trace_setevent @traceid, 12, 7, @on --SQL:BatchCompleted, NTDomainName
      exec sp_trace_setevent @traceid, 12, 8, @on --SQL:BatchCompleted, ClientHostName
      exec sp_trace_setevent @traceid, 12, 9, @on --SQL:BatchCompleted, ClientProcessID
      exec sp_trace_setevent @traceid, 12, 10, @on --SQL:BatchCompleted, ApplicationName
      exec sp_trace_setevent @traceid, 12, 11, @on --SQL:BatchCompleted, SQLSecurityLoginName
      exec sp_trace_setevent @traceid, 12, 12, @on --SQL:BatchCompleted, SPID
      exec sp_trace_setevent @traceid, 12, 13, @on --SQL:BatchCompleted, Duration
      exec sp_trace_setevent @traceid, 12, 14, @on --SQL:BatchCompleted, StartTime
      exec sp_trace_setevent @traceid, 12, 15, @on --SQL:BatchCompleted, EndTime
      exec sp_trace_setevent @traceid, 12, 16, @on --SQL:BatchCompleted, Reads
      exec sp_trace_setevent @traceid, 12, 17, @on --SQL:BatchCompleted, Writes
      exec sp_trace_setevent @traceid, 12, 18, @on --SQL:BatchCompleted, CPU
      exec sp_trace_setevent @traceid, 12, 21, @on --SQL:BatchCompleted, EventSubClass
      exec sp_trace_setevent @traceid, 12, 22, @on --SQL:BatchCompleted, ObjectID
      exec sp_trace_setevent @traceid, 12, 25, @on --SQL:BatchCompleted, IntegerData
      exec sp_trace_setevent @traceid, 12, 27, @on --RPC:BatchCompleted, Eventclass
      
      exec sp_trace_setevent @traceid, 13, 1, @on --SQL:BatchStarting, TextData
      exec sp_trace_setevent @traceid, 13, 2, @on --SQL:BatchStarting, BinaryData
      exec sp_trace_setevent @traceid, 13, 3, @on --SQL:BatchStarting, DatabaseID
      exec sp_trace_setevent @traceid, 13, 4, @on --SQL:BatchStarting, TransactionID
      exec sp_trace_setevent @traceid, 13, 6, @on --SQL:BatchStarting, NTUserName
      exec sp_trace_setevent @traceid, 13, 7, @on --SQL:BatchStarting, NTDomainName
      exec sp_trace_setevent @traceid, 13, 8, @on --SQL:BatchStarting, ClientHostName
      exec sp_trace_setevent @traceid, 13, 9, @on --SQL:BatchStarting, ClientProcessID
      exec sp_trace_setevent @traceid, 13, 10, @on --SQL:BatchStarting, ApplicationName
      exec sp_trace_setevent @traceid, 13, 11, @on --SQL:BatchStarting, SQLSecurityLoginName
      exec sp_trace_setevent @traceid, 13, 12, @on --SQL:BatchStarting, SPID
      exec sp_trace_setevent @traceid, 13, 13, @on --SQL:BatchStarting, Duration
      exec sp_trace_setevent @traceid, 13, 14, @on --SQL:BatchStarting, StartTime
      exec sp_trace_setevent @traceid, 13, 15, @on --SQL:BatchStarting, EndTime
      exec sp_trace_setevent @traceid, 13, 16, @on --SQL:BatchStarting, Reads
      exec sp_trace_setevent @traceid, 13, 17, @on --SQL:BatchStarting, Writes
      exec sp_trace_setevent @traceid, 13, 18, @on --SQL:BatchStarting, CPU
      exec sp_trace_setevent @traceid, 13, 21, @on --SQL:BatchStarting, EventSubClass
      exec sp_trace_setevent @traceid, 13, 22, @on --SQL:BatchStarting, ObjectID
      exec sp_trace_setevent @traceid, 13, 23, @on --SQL:BatchStarting, IntegerData
      exec sp_trace_setevent @traceid, 13, 27, @on --RPC:BatchStarting, Eventclass

      exec sp_trace_setevent @traceid, 25, 1, @on --Lock:Deadlock, TextData
      exec sp_trace_setevent @traceid, 25, 2, @on --Lock:Deadlock, BinaryData
      exec sp_trace_setevent @traceid, 25, 3, @on --Lock:Deadlock, DatabaseID
      exec sp_trace_setevent @traceid, 25, 4, @on --Lock:Deadlock, TransactionID
      exec sp_trace_setevent @traceid, 25, 6, @on --Lock:Deadlock, NTUserName
      exec sp_trace_setevent @traceid, 25, 7, @on --Lock:Deadlock, NTDomainName
      exec sp_trace_setevent @traceid, 25, 8, @on --Lock:Deadlock, ClientHostName
      exec sp_trace_setevent @traceid, 25, 9, @on --Lock:Deadlock, ClientProcessID
      exec sp_trace_setevent @traceid, 25, 10, @on --Lock:Deadlock, ApplicationName
      exec sp_trace_setevent @traceid, 25, 11, @on --Lock:Deadlock, SQLSecurityLoginName
      exec sp_trace_setevent @traceid, 25, 12, @on --Lock:Deadlock, SPID
      exec sp_trace_setevent @traceid, 25, 13, @on --Lock:Deadlock, Duration
      exec sp_trace_setevent @traceid, 25, 14, @on --Lock:Deadlock, StartTime
      exec sp_trace_setevent @traceid, 25, 15, @on --Lock:Deadlock, EndTime
      exec sp_trace_setevent @traceid, 25, 16, @on --Lock:Deadlock, Reads
      exec sp_trace_setevent @traceid, 25, 17, @on --Lock:Deadlock, Writes
      exec sp_trace_setevent @traceid, 25, 18, @on --Lock:Deadlock, CPU
      exec sp_trace_setevent @traceid, 25, 21, @on --Lock:Deadlock, EventSubClass
      exec sp_trace_setevent @traceid, 25, 22, @on --Lock:Deadlock, ObjectID
      exec sp_trace_setevent @traceid, 25, 25, @on --Lock:Deadlock, IntegerData
      exec sp_trace_setevent @traceid, 25, 27, @on --Lock:Deadlock, Eventclass

      exec sp_trace_setevent @traceid, 27, 1, @on --Lock:Timeout, TextData
      exec sp_trace_setevent @traceid, 27, 2, @on --Lock:Timeout, BinaryData
      exec sp_trace_setevent @traceid, 27, 3, @on --Lock:Timeout, DatabaseID
      exec sp_trace_setevent @traceid, 27, 4, @on --Lock:Timeout, TransactionID
      exec sp_trace_setevent @traceid, 27, 6, @on --Lock:Timeout, NTUserName
      exec sp_trace_setevent @traceid, 27, 7, @on --Lock:Timeout, NTDomainName
      exec sp_trace_setevent @traceid, 27, 8, @on --Lock:Timeout, ClientHostName
      exec sp_trace_setevent @traceid, 27, 9, @on --Lock:Timeout, ClientProcessID
      exec sp_trace_setevent @traceid, 27, 10, @on --Lock:Timeout, ApplicationName
      exec sp_trace_setevent @traceid, 27, 11, @on --Lock:Timeout, SQLSecurityLoginName
      exec sp_trace_setevent @traceid, 27, 12, @on --Lock:Timeout, SPID
      exec sp_trace_setevent @traceid, 27, 13, @on --Lock:Timeout, Duration
      exec sp_trace_setevent @traceid, 27, 14, @on --Lock:Timeout, StartTime
      exec sp_trace_setevent @traceid, 27, 15, @on --Lock:Timeout, EndTime
      exec sp_trace_setevent @traceid, 27, 16, @on --Lock:Timeout, Reads
      exec sp_trace_setevent @traceid, 27, 17, @on --Lock:Timeout, Writes
      exec sp_trace_setevent @traceid, 27, 18, @on --Lock:Timeout, CPU
      exec sp_trace_setevent @traceid, 27, 21, @on --Lock:Timeout, EventSubClass
      exec sp_trace_setevent @traceid, 27, 22, @on --Lock:Timeout, ObjectID
      exec sp_trace_setevent @traceid, 27, 25, @on --Lock:Timeout, IntegerData
      exec sp_trace_setevent @traceid, 27, 27, @on --Lock:Timeout, Eventclass

      exec sp_trace_setevent @traceid, 28, 1, @on --DOP Event, TextData
      exec sp_trace_setevent @traceid, 28, 2, @on --DOP Event, BinaryData
      exec sp_trace_setevent @traceid, 28, 3, @on --DOP Event, DatabaseID
      exec sp_trace_setevent @traceid, 28, 4, @on --DOP Event, TransactionID
      exec sp_trace_setevent @traceid, 28, 6, @on --DOP Event, NTUserName
      exec sp_trace_setevent @traceid, 28, 7, @on --DOP Event, NTDomainName
      exec sp_trace_setevent @traceid, 28, 8, @on --DOP Event, ClientHostName
      exec sp_trace_setevent @traceid, 28, 9, @on --DOP Event, ClientProcessID
      exec sp_trace_setevent @traceid, 28, 10, @on --DOP Event, ApplicationName
      exec sp_trace_setevent @traceid, 28, 11, @on --DOP Event, SQLSecurityLoginName
      exec sp_trace_setevent @traceid, 28, 12, @on --DOP Event, SPID
      exec sp_trace_setevent @traceid, 28, 13, @on --DOP Event, Duration
      exec sp_trace_setevent @traceid, 28, 14, @on --DOP Event, StartTime
      exec sp_trace_setevent @traceid, 28, 15, @on --DOP Event, EndTime
      exec sp_trace_setevent @traceid, 28, 16, @on --DOP Event, Reads
      exec sp_trace_setevent @traceid, 28, 17, @on --DOP Event, Writes
      exec sp_trace_setevent @traceid, 28, 18, @on --DOP Event, CPU
      exec sp_trace_setevent @traceid, 28, 21, @on --DOP Event, EventSubClass
      exec sp_trace_setevent @traceid, 28, 22, @on --DOP Event, ObjectID
      exec sp_trace_setevent @traceid, 28, 25, @on --DOP Event, IntegerData
      exec sp_trace_setevent @traceid, 28, 27, @on --DOP Event, Eventclass

      exec sp_trace_setevent @traceid, 37, 1, @on --SP:Recompile, TextData
      exec sp_trace_setevent @traceid, 37, 2, @on --SP:Recompile, BinaryData
      exec sp_trace_setevent @traceid, 37, 3, @on --SP:Recompile, DatabaseID
      exec sp_trace_setevent @traceid, 37, 4, @on --SP:Recompile, TransactionID
      exec sp_trace_setevent @traceid, 37, 6, @on --SP:Recompile, NTUserName
      exec sp_trace_setevent @traceid, 37, 7, @on --SP:Recompile, NTDomainName
      exec sp_trace_setevent @traceid, 37, 8, @on --SP:Recompile, ClientHostName
      exec sp_trace_setevent @traceid, 37, 9, @on --SP:Recompile, ClientProcessID
      exec sp_trace_setevent @traceid, 37, 10, @on --SP:Recompile, ApplicationName
      exec sp_trace_setevent @traceid, 37, 11, @on --SP:Recompile, SQLSecurityLoginName
      exec sp_trace_setevent @traceid, 37, 12, @on --SP:Recompile, SPID
      exec sp_trace_setevent @traceid, 37, 13, @on --SP:Recompile, Duration
      exec sp_trace_setevent @traceid, 37, 14, @on --SP:Recompile, StartTime
      exec sp_trace_setevent @traceid, 37, 15, @on --SP:Recompile, EndTime
      exec sp_trace_setevent @traceid, 37, 16, @on --SP:Recompile, Reads
      exec sp_trace_setevent @traceid, 37, 17, @on --SP:Recompile, Writes
      exec sp_trace_setevent @traceid, 37, 18, @on --SP:Recompile, CPU
      exec sp_trace_setevent @traceid, 37, 21, @on --SP:Recompile, EventSubClass
      exec sp_trace_setevent @traceid, 37, 22, @on --SP:Recompile, ObjectID
      exec sp_trace_setevent @traceid, 37, 25, @on --SP:Recompile, IntegerData
      exec sp_trace_setevent @traceid, 37, 27, @on --SP:Recompile, Eventclass

        exec sp_trace_setevent @traceid, 40, 1, @on --SQL:StmtStarting, TextData
      exec sp_trace_setevent @traceid, 40, 2, @on --SQL:StmtStarting, BinaryData
      exec sp_trace_setevent @traceid, 40, 3, @on --SQL:StmtStarting, DatabaseID
      exec sp_trace_setevent @traceid, 40, 4, @on --SQL:StmtStarting, TransactionID
      exec sp_trace_setevent @traceid, 40, 6, @on --SQL:StmtStarting, NTUserName
      exec sp_trace_setevent @traceid, 40, 7, @on --SQL:StmtStarting, NTDomainName
      exec sp_trace_setevent @traceid, 40, 8, @on --SQL:StmtStarting, ClientHostName
      exec sp_trace_setevent @traceid, 40, 9, @on --SQL:StmtStarting, ClientProcessID
      exec sp_trace_setevent @traceid, 40, 10, @on --SQL:StmtStarting, ApplicationName
      exec sp_trace_setevent @traceid, 40, 11, @on --SQL:StmtStarting, SQLSecurityLoginName
      exec sp_trace_setevent @traceid, 40, 12, @on --SQL:StmtStarting, SPID
      exec sp_trace_setevent @traceid, 40, 13, @on --SQL:StmtStarting, Duration
      exec sp_trace_setevent @traceid, 40, 14, @on --SQL:StmtStarting, StartTime
      exec sp_trace_setevent @traceid, 40, 15, @on --SQL:StmtStarting, EndTime
      exec sp_trace_setevent @traceid, 40, 16, @on --SQL:StmtStarting, Reads
      exec sp_trace_setevent @traceid, 40, 17, @on --SQL:StmtStarting, Writes
      exec sp_trace_setevent @traceid, 40, 18, @on --SQL:StmtStarting, CPU
      exec sp_trace_setevent @traceid, 40, 21, @on --SQL:StmtStarting, EventSubClass
      exec sp_trace_setevent @traceid, 40, 22, @on --SQL:StmtStarting, ObjectID
      exec sp_trace_setevent @traceid, 40, 25, @on --SQL:StmtStarting, IntegerData
      exec sp_trace_setevent @traceid, 40, 27, @on --SQL:StmtStarting, Eventclass

        exec sp_trace_setevent @traceid, 41, 1, @on --SQL:StmtCompleted, TextData
      exec sp_trace_setevent @traceid, 41, 2, @on --SQL:StmtCompleted, BinaryData
      exec sp_trace_setevent @traceid, 41, 3, @on --SQL:StmtCompleted, DatabaseID
      exec sp_trace_setevent @traceid, 41, 4, @on --SQL:StmtCompleted, TransactionID
      exec sp_trace_setevent @traceid, 41, 6, @on --SQL:StmtCompleted, NTUserName
      exec sp_trace_setevent @traceid, 41, 7, @on --SQL:StmtCompleted, NTDomainName
      exec sp_trace_setevent @traceid, 41, 8, @on --SQL:StmtCompleted, ClientHostName
      exec sp_trace_setevent @traceid, 41, 9, @on --SQL:StmtCompleted, ClientProcessID
      exec sp_trace_setevent @traceid, 41, 10, @on --SQL:StmtCompleted, ApplicationName
      exec sp_trace_setevent @traceid, 41, 11, @on --SQL:StmtCompleted, SQLSecurityLoginName
      exec sp_trace_setevent @traceid, 41, 12, @on --SQL:StmtCompleted, SPID
      exec sp_trace_setevent @traceid, 41, 13, @on --SQL:StmtCompleted, Duration
      exec sp_trace_setevent @traceid, 41, 14, @on --SQL:StmtCompleted, StartTime
      exec sp_trace_setevent @traceid, 41, 15, @on --SQL:StmtCompleted, EndTime
      exec sp_trace_setevent @traceid, 41, 16, @on --SQL:StmtCompleted, Reads
      exec sp_trace_setevent @traceid, 41, 17, @on --SQL:StmtCompleted, Writes
      exec sp_trace_setevent @traceid, 41, 18, @on --SQL:StmtCompleted, CPU
      exec sp_trace_setevent @traceid, 41, 21, @on --SQL:StmtCompleted, EventSubClass
      exec sp_trace_setevent @traceid, 41, 22, @on --SQL:StmtCompleted, ObjectID
      exec sp_trace_setevent @traceid, 41, 25, @on --SQL:StmtCompleted, IntegerData
      exec sp_trace_setevent @traceid, 41, 27, @on --SQL:StmtCompleted, Eventclass

        exec sp_trace_setevent @traceid, 42, 1, @on --SP:Starting, TextData
      exec sp_trace_setevent @traceid, 42, 2, @on --SP:Starting, BinaryData
      exec sp_trace_setevent @traceid, 42, 3, @on --SP:Starting, DatabaseID
      exec sp_trace_setevent @traceid, 42, 4, @on --SP:Starting, TransactionID
      exec sp_trace_setevent @traceid, 42, 6, @on --SP:Starting, NTUserName
      exec sp_trace_setevent @traceid, 42, 7, @on --SP:Starting, NTDomainName
      exec sp_trace_setevent @traceid, 42, 8, @on --SP:Starting, ClientHostName
      exec sp_trace_setevent @traceid, 42, 9, @on --SP:Starting, ClientProcessID
      exec sp_trace_setevent @traceid, 42, 10, @on --SP:Starting, ApplicationName
      exec sp_trace_setevent @traceid, 42, 11, @on --SP:Starting, SQLSecurityLoginName
      exec sp_trace_setevent @traceid, 42, 12, @on --SP:Starting, SPID
      exec sp_trace_setevent @traceid, 42, 13, @on --SP:Starting, Duration
      exec sp_trace_setevent @traceid, 42, 14, @on --SP:Starting, StartTime
      exec sp_trace_setevent @traceid, 42, 15, @on --SP:Starting, EndTime
      exec sp_trace_setevent @traceid, 42, 16, @on --SP:Starting, Reads
      exec sp_trace_setevent @traceid, 42, 17, @on --SP:Starting, Writes
      exec sp_trace_setevent @traceid, 42, 18, @on --SP:Starting, CPU
      exec sp_trace_setevent @traceid, 42, 21, @on --SP:Starting, EventSubClass
      exec sp_trace_setevent @traceid, 42, 22, @on --SP:Starting, ObjectID
      exec sp_trace_setevent @traceid, 42, 25, @on --SP:Starting, IntegerData
      exec sp_trace_setevent @traceid, 42, 27, @on --SP:Starting, Eventclass

        exec sp_trace_setevent @traceid, 43, 1, @on --SP:Completed, TextData
      exec sp_trace_setevent @traceid, 43, 2, @on --SP:Completed, BinaryData
      exec sp_trace_setevent @traceid, 43, 3, @on --SP:Completed, DatabaseID
      exec sp_trace_setevent @traceid, 43, 4, @on --SP:Completed, TransactionID
      exec sp_trace_setevent @traceid, 43, 6, @on --SP:Completed, NTUserName
      exec sp_trace_setevent @traceid, 43, 7, @on --SP:Completed, NTDomainName
      exec sp_trace_setevent @traceid, 43, 8, @on --SP:Completed, ClientHostName
      exec sp_trace_setevent @traceid, 43, 9, @on --SP:Completed, ClientProcessID
      exec sp_trace_setevent @traceid, 43, 10, @on --SP:Completed, ApplicationName
      exec sp_trace_setevent @traceid, 43, 11, @on --SP:Completed, SQLSecurityLoginName
      exec sp_trace_setevent @traceid, 43, 12, @on --SP:Completed, SPID
      exec sp_trace_setevent @traceid, 43, 13, @on --SP:Completed, Duration
      exec sp_trace_setevent @traceid, 43, 14, @on --SP:Completed, StartTime
      exec sp_trace_setevent @traceid, 43, 15, @on --SP:Completed, EndTime
      exec sp_trace_setevent @traceid, 43, 16, @on --SP:Completed, Reads
      exec sp_trace_setevent @traceid, 43, 17, @on --SP:Completed, Writes
      exec sp_trace_setevent @traceid, 43, 18, @on --SP:Completed, CPU
      exec sp_trace_setevent @traceid, 43, 21, @on --SP:Completed, EventSubClass
      exec sp_trace_setevent @traceid, 43, 22, @on --SP:Completed, ObjectID
      exec sp_trace_setevent @traceid, 43, 25, @on --SP:Completed, IntegerData
      exec sp_trace_setevent @traceid, 43, 27, @on --SP:Completed, Eventclass

        exec sp_trace_setevent @traceid, 55, 1, @on --Hash Warning, TextData
      exec sp_trace_setevent @traceid, 55, 2, @on --Hash Warning, BinaryData
      exec sp_trace_setevent @traceid, 55, 3, @on --Hash Warning, DatabaseID
      exec sp_trace_setevent @traceid, 55, 4, @on --Hash Warning, TransactionID
      exec sp_trace_setevent @traceid, 55, 6, @on --Hash Warning, NTUserName
      exec sp_trace_setevent @traceid, 55, 7, @on --Hash Warning, NTDomainName
      exec sp_trace_setevent @traceid, 55, 8, @on --Hash Warning, ClientHostName
      exec sp_trace_setevent @traceid, 55, 9, @on --Hash Warning, ClientProcessID
      exec sp_trace_setevent @traceid, 55, 10, @on --Hash Warning, ApplicationName
      exec sp_trace_setevent @traceid, 55, 11, @on --Hash Warning, SQLSecurityLoginName
      exec sp_trace_setevent @traceid, 55, 12, @on --Hash Warning, SPID
      exec sp_trace_setevent @traceid, 55, 13, @on --Hash Warning, Duration
      exec sp_trace_setevent @traceid, 55, 14, @on --Hash Warning, StartTime
      exec sp_trace_setevent @traceid, 55, 15, @on --Hash Warning, EndTime
      exec sp_trace_setevent @traceid, 55, 16, @on --Hash Warning, Reads
      exec sp_trace_setevent @traceid, 55, 17, @on --Hash Warning, Writes
      exec sp_trace_setevent @traceid, 55, 18, @on --Hash Warning, CPU
      exec sp_trace_setevent @traceid, 55, 21, @on --Hash Warning, EventSubClass
      exec sp_trace_setevent @traceid, 55, 22, @on --Hash Warning, ObjectID
      exec sp_trace_setevent @traceid, 55, 25, @on --Hash Warning, IntegerData
      exec sp_trace_setevent @traceid, 55, 27, @on --Hash warning, Eventclass

        exec sp_trace_setevent @traceid, 59, 1, @on --Lock:Deadlock Chain, TextData
      exec sp_trace_setevent @traceid, 59, 2, @on --Lock:Deadlock Chain, BinaryData
      exec sp_trace_setevent @traceid, 59, 3, @on --Lock:Deadlock Chain, DatabaseID
      exec sp_trace_setevent @traceid, 59, 4, @on --Lock:Deadlock Chain, TransactionID
      exec sp_trace_setevent @traceid, 59, 6, @on --Lock:Deadlock Chain, NTUserName
      exec sp_trace_setevent @traceid, 59, 7, @on --Lock:Deadlock Chain, NTDomainName
      exec sp_trace_setevent @traceid, 59, 8, @on --Lock:Deadlock Chain, ClientHostName
      exec sp_trace_setevent @traceid, 59, 9, @on --Lock:Deadlock Chain, ClientProcessID
      exec sp_trace_setevent @traceid, 59, 10, @on --Lock:Deadlock Chain, ApplicationName
      exec sp_trace_setevent @traceid, 59, 11, @on --Lock:Deadlock Chain, SQLSecurityLoginName
      exec sp_trace_setevent @traceid, 59, 12, @on --Lock:Deadlock Chain, SPID
      exec sp_trace_setevent @traceid, 59, 13, @on --Lock:Deadlock Chain, Duration
      exec sp_trace_setevent @traceid, 59, 14, @on --Lock:Deadlock Chain, StartTime
      exec sp_trace_setevent @traceid, 59, 15, @on --Lock:Deadlock Chain, EndTime
      exec sp_trace_setevent @traceid, 59, 16, @on --Lock:Deadlock Chain, Reads
      exec sp_trace_setevent @traceid, 59, 17, @on --Lock:Deadlock Chain, Writes
      exec sp_trace_setevent @traceid, 59, 18, @on --Lock:Deadlock Chain, CPU
      exec sp_trace_setevent @traceid, 59, 21, @on --Lock:Deadlock Chain, EventSubClass
      exec sp_trace_setevent @traceid, 59, 22, @on --Lock:Deadlock Chain, ObjectID
      exec sp_trace_setevent @traceid, 59, 25, @on --Lock:Deadlock Chain, IntegerData
      exec sp_trace_setevent @traceid, 59, 27, @on --Lock:Deadlock Chain, Eventclass

        exec sp_trace_setevent @traceid, 60, 1, @on --Lock:Escalation, TextData

      exec sp_trace_setevent @traceid, 60, 2, @on --Lock:Escalation, BinaryData
      exec sp_trace_setevent @traceid, 60, 3, @on --Lock:Escalation, DatabaseID
      exec sp_trace_setevent @traceid, 60, 4, @on --Lock:Escalation, TransactionID
      exec sp_trace_setevent @traceid, 60, 6, @on --Lock:Escalation, NTUserName
      exec sp_trace_setevent @traceid, 60, 7, @on --Lock:Escalation, NTDomainName
      exec sp_trace_setevent @traceid, 60, 8, @on --Lock:Escalation, ClientHostName
      exec sp_trace_setevent @traceid, 60, 9, @on --Lock:Escalation, ClientProcessID
      exec sp_trace_setevent @traceid, 60, 10, @on --Lock:Escalation, ApplicationName
      exec sp_trace_setevent @traceid, 60, 11, @on --Lock:Escalation, SQLSecurityLoginName
      exec sp_trace_setevent @traceid, 60, 12, @on --Lock:Escalation, SPID
      exec sp_trace_setevent @traceid, 60, 13, @on --Lock:Escalation, Duration
      exec sp_trace_setevent @traceid, 60, 14, @on --Lock:Escalation, StartTime
      exec sp_trace_setevent @traceid, 60, 15, @on --Lock:Escalation, EndTime
      exec sp_trace_setevent @traceid, 60, 16, @on --Lock:Escalation, Reads
      exec sp_trace_setevent @traceid, 60, 17, @on --Lock:Escalation, Writes
      exec sp_trace_setevent @traceid, 60, 18, @on --Lock:Escalation, CPU
      exec sp_trace_setevent @traceid, 60, 21, @on --Lock:Escalation, EventSubClass
      exec sp_trace_setevent @traceid, 60, 22, @on --Lock:Escalation, ObjectID
      exec sp_trace_setevent @traceid, 60, 25, @on --Lock:Escalation, IntegerData
      exec sp_trace_setevent @traceid, 60, 27, @on --Lock:Escalation, Eventclass

        exec sp_trace_setevent @traceid, 68, 1, @on --Execution Plan, TextData
      exec sp_trace_setevent @traceid, 68, 2, @on --Execution Plan, BinaryData
      exec sp_trace_setevent @traceid, 68, 3, @on --Execution Plan, DatabaseID
      exec sp_trace_setevent @traceid, 68, 4, @on --Execution Plan, TransactionID
      exec sp_trace_setevent @traceid, 68, 6, @on --Execution Plan, NTUserName
      exec sp_trace_setevent @traceid, 68, 7, @on --Execution Plan, NTDomainName
      exec sp_trace_setevent @traceid, 68, 8, @on --Execution Plan, ClientHostName
      exec sp_trace_setevent @traceid, 68, 9, @on --Execution Plan, ClientProcessID
      exec sp_trace_setevent @traceid, 68, 10, @on --Execution Plan, ApplicationName
      exec sp_trace_setevent @traceid, 68, 11, @on --Execution Plan, SQLSecurityLoginName
      exec sp_trace_setevent @traceid, 68, 12, @on --Execution Plan, SPID
      exec sp_trace_setevent @traceid, 68, 13, @on --Execution Plan, Duration
      exec sp_trace_setevent @traceid, 68, 14, @on --Execution Plan, StartTime
      exec sp_trace_setevent @traceid, 68, 15, @on --Execution Plan, EndTime
      exec sp_trace_setevent @traceid, 68, 16, @on --Execution Plan, Reads
      exec sp_trace_setevent @traceid, 68, 17, @on --Execution Plan, Writes
      exec sp_trace_setevent @traceid, 68, 18, @on --Execution Plan, CPU
      exec sp_trace_setevent @traceid, 68, 21, @on --Execution Plan, EventSubClass
      exec sp_trace_setevent @traceid, 68, 22, @on --Execution Plan, ObjectID
      exec sp_trace_setevent @traceid, 68, 25, @on --Execution Plan, IntegerData
      exec sp_trace_setevent @traceid, 68, 27, @on --Execution Plan, Eventclass

        exec sp_trace_setevent @traceid, 69, 1, @on --Sort Warnings, TextData
      exec sp_trace_setevent @traceid, 69, 2, @on --Sort Warnings, BinaryData
      exec sp_trace_setevent @traceid, 69, 3, @on --Sort Warnings, DatabaseID
      exec sp_trace_setevent @traceid, 69, 4, @on --Sort Warnings, TransactionID
      exec sp_trace_setevent @traceid, 69, 6, @on --Sort Warnings, NTUserName
      exec sp_trace_setevent @traceid, 69, 7, @on --Sort Warnings, NTDomainName
      exec sp_trace_setevent @traceid, 69, 8, @on --Sort Warnings, ClientHostName
      exec sp_trace_setevent @traceid, 69, 9, @on --Sort Warnings, ClientProcessID
      exec sp_trace_setevent @traceid, 69, 10, @on --Sort Warnings, ApplicationName
      exec sp_trace_setevent @traceid, 69, 11, @on --Sort Warnings, SQLSecurityLoginName
      exec sp_trace_setevent @traceid, 69, 12, @on --Sort Warnings, SPID
      exec sp_trace_setevent @traceid, 69, 13, @on --Sort Warnings, Duration
      exec sp_trace_setevent @traceid, 69, 14, @on --Sort Warnings, StartTime
      exec sp_trace_setevent @traceid, 69, 15, @on --Sort Warnings, EndTime
      exec sp_trace_setevent @traceid, 69, 16, @on --Sort Warnings, Reads
      exec sp_trace_setevent @traceid, 69, 17, @on --Sort Warnings, Writes
      exec sp_trace_setevent @traceid, 69, 18, @on --Sort Warnings, CPU
      exec sp_trace_setevent @traceid, 69, 21, @on --Sort Warnings, EventSubClass
      exec sp_trace_setevent @traceid, 69, 22, @on --Sort Warnings, ObjectID
      exec sp_trace_setevent @traceid, 69, 25, @on --Sort Warnings, IntegerData
      exec sp_trace_setevent @traceid, 69, 27, @on --Sort Warnings, Eventclass

        exec sp_trace_setevent @traceid, 79, 1, @on --Missing Column Statistics, TextData
      exec sp_trace_setevent @traceid, 79, 2, @on --Missing Column Statistics, BinaryData
      exec sp_trace_setevent @traceid, 79, 3, @on --Missing Column Statistics, DatabaseID
      exec sp_trace_setevent @traceid, 79, 4, @on --Missing Column Statistics, TransactionID
      exec sp_trace_setevent @traceid, 79, 6, @on --Missing Column Statistics, NTUserName
      exec sp_trace_setevent @traceid, 79, 7, @on --Missing Column Statistics, NTDomainName
      exec sp_trace_setevent @traceid, 79, 8, @on --Missing Column Statistics, ClientHostName
      exec sp_trace_setevent @traceid, 79, 9, @on --Missing Column Statistics, ClientProcessID
      exec sp_trace_setevent @traceid, 79, 10, @on --Missing Column Statistics, ApplicationName
      exec sp_trace_setevent @traceid, 79, 11, @on --Missing Column Statistics, SQLSecurityLoginName
      exec sp_trace_setevent @traceid, 79, 12, @on --Missing Column Statistics, SPID
      exec sp_trace_setevent @traceid, 79, 13, @on --Missing Column Statistics, Duration
      exec sp_trace_setevent @traceid, 79, 14, @on --Missing Column Statistics, StartTime
      exec sp_trace_setevent @traceid, 79, 15, @on --Missing Column Statistics, EndTime
      exec sp_trace_setevent @traceid, 79, 16, @on --Missing Column Statistics, Reads
      exec sp_trace_setevent @traceid, 79, 17, @on --Missing Column Statistics, Writes
      exec sp_trace_setevent @traceid, 79, 18, @on --Missing Column Statistics, CPU
      exec sp_trace_setevent @traceid, 79, 21, @on --Missing Column Statistics, EventSubClass
      exec sp_trace_setevent @traceid, 79, 22, @on --Missing Column Statistics, ObjectID
      exec sp_trace_setevent @traceid, 79, 25, @on --Missing Column Statistics, IntegerData
      exec sp_trace_setevent @traceid, 79, 27, @on --Missing Column Statistics, Eventclass

Print 'End setting up trace event items...'
      -- Original filter
        -- exec sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Profiler'

      -- New filters for Accenture

Print 'Setting filters to remove SQL Profiler and system info...'
      exec sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Profiler' -- Eliminates Profiler self-reporting
      exec sp_trace_setfilter @traceid, 3, 0, 1, 1 --master  1 in third number column is Not Equal To
      exec sp_trace_setfilter @traceid, 3, 0, 1, 3 --model   1 in third number column is Not Equal To  
      exec sp_trace_setfilter @traceid, 3, 0, 1, 4 --msdb    1 in third number column is Not Equal To
      exec sp_trace_setfilter @traceid, 12, 0, 2, 50 --user spids only     2 in third column is Greater Than
      exec sp_trace_setfilter @traceid, 22, 0, 2, 100 --user objects only     2 in third column is Greater Than
      exec sp_trace_setfilter @traceid, 11, 0, 7, N'sa%' -- Not like sa stuff
      exec sp_trace_setfilter @traceid, 11, 0, 7, N'%TRACK%' -- Not like Trackatool crud
      exec sp_trace_setfilter @traceid, 11, 0, 7, N'%itrix%' -- Not like Citrix crud
      exec sp_trace_setfilter @traceid, 11, 0, 7, N'%ertex%' -- Not like Vertex crud



Print 'Setting trace status @traceid'
      exec sp_trace_setstatus @traceid, 1
      set @starttime = getdate()      

      -- display trace id for future references
      select traceid=@traceid

      goto finish
Print 'Finished setting up trace...'

ERROR:

Print 'selecting error code...'
select ErrorCode=@rc


FINISH:

-- START TRACE ...
Print 'Start trace'
-- START SQL Profiler trace collection...
-- Declare and set trace ID
set @traceid = 1


END

exec sp_procoption N'USP_SQLProfilerStandard', N'startup', N'true'
GO


/*-----------------------------------------------------------------------------------------------------------

The above will create a stored procedure "USP_SQLProfilerStandard " and will start it up when the database starts.  It will create a file on the C: drive called "C:\SQLProfilerTrace_{servername}{date}{time}".trc - and will roll these when the server gets to 512mb.  As can be seen, there are some filters set up - where certain user ID's are not being logged - including SA's SQL.  

It works like a charm - every SQL Server should have this !  Now I can see exactly WHO is running WHAT and WHEN !
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17894812
Cool man, thats awesome, I will try this on my servers

rw3admin
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 18069155
PinalDave,
I worked with author on this question but actually he provided a solution that works for him, I will feel bad getting points for this, this should be closed and points refunded

rw3admin
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 18106242
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now