SQL TRACE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » SQL TRACE

16 posts, Page 1 of 2

1 2 »»

SQL TRACE

Rate Topic

Display Mode

Topic Options

Author

Message

dev_programmer

Posted Wednesday, July 01, 2009 10:09 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 20, 2012 3:32 AM
Points: 34, Visits: 126

Dear Experts,
I want to create a Light weight trace on my production server just like the SQL SERVER 2005 default trace through T-SQL statements. Further I want to schedule it for 24 - Hrs tracing as there are case where the data on the prod. server was deleted without any trace as of who did that. Also i want the data to imported to the sql server database table from the trace file on date wise basis i.e i want to create a seperate table for each days trace data.
Pls help

Thanks in Advance

Regards,

Thank you,
Regards,
Dev
email id :- dev_programmer@yahoo.co.in

Post #745574

Lowell

Posted Wednesday, July 01, 2009 10:19 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 11,605, Visits: 27,645

here's the proc I use to create the trace I use; note that it creates a view at the end so I can also see the results easily.

this runs till you turn it off;
hope this helps:

CREATE procedure sp_AddMyTrace  
AS  
BEGIN  
  SET NOCOUNT ON  
  SET XACT_ABORT ON  
  BEGIN TRAN  
  
  declare @sql varchar(1000)    
  declare @path nvarchar(256)  
  declare @traceidout int  
  declare @maxfilesize bigint  
  declare @maxRolloverFiles int  
  declare @on bit  
  
  set @on = 1  
  set @maxRolloverFiles = 2  
  set @maxfilesize = 50  
  
  --we want the current trace folder  
  --ie c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG  
SELECT @path = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) FROM sys.traces WHERE is_default = 1  
  SET @path = @path + N'MyDMLtrace' --system appends .trc automatically for the filename  
  
  --create the trace  
  exec sp_trace_create @traceidout output, @maxRolloverFiles, @path, @maxfilesize, NULL  
  
  --for the Event Every SQL statement completed, capture all 64 columns of accessible data  
  exec sp_trace_setevent @traceidout, 12, 1, @on --SQL:BatchCompleted,TextData   
  exec sp_trace_setevent @traceidout, 12, 2, @on --SQL:BatchCompleted,BinaryData   
  exec sp_trace_setevent @traceidout, 12, 3, @on --SQL:BatchCompleted,DatabaseID   
  exec sp_trace_setevent @traceidout, 12, 4, @on --SQL:BatchCompleted,TransactionID   
  exec sp_trace_setevent @traceidout, 12, 5, @on --SQL:BatchCompleted,LineNumber   
  exec sp_trace_setevent @traceidout, 12, 6, @on --SQL:BatchCompleted,NTUserName   
  exec sp_trace_setevent @traceidout, 12, 7, @on --SQL:BatchCompleted,NTDomainName   
  exec sp_trace_setevent @traceidout, 12, 8, @on --SQL:BatchCompleted,HostName   
  exec sp_trace_setevent @traceidout, 12, 9, @on --SQL:BatchCompleted, ClientProcessID   
  exec sp_trace_setevent @traceidout, 12,10, @on --SQL:BatchCompleted,ApplicationName   
  exec sp_trace_setevent @traceidout, 12,11, @on --SQL:BatchCompleted,LoginName   
  exec sp_trace_setevent @traceidout, 12,12, @on --SQL:BatchCompleted,SPID   
  exec sp_trace_setevent @traceidout, 12,13, @on --SQL:BatchCompleted,Duration   
  exec sp_trace_setevent @traceidout, 12,14, @on --SQL:BatchCompleted,StartTime   
  exec sp_trace_setevent @traceidout, 12,15, @on --SQL:BatchCompleted,EndTime   
  exec sp_trace_setevent @traceidout, 12,16, @on --SQL:BatchCompleted,Reads   
  exec sp_trace_setevent @traceidout, 12,17, @on --SQL:BatchCompleted,Writes   
  exec sp_trace_setevent @traceidout, 12,18, @on --SQL:BatchCompleted,CPU   
  exec sp_trace_setevent @traceidout, 12,19, @on --SQL:BatchCompleted,Permissions   
  exec sp_trace_setevent @traceidout, 12,20, @on --SQL:BatchCompleted,Severity   
  exec sp_trace_setevent @traceidout, 12,21, @on --SQL:BatchCompleted,EventSubClass   
  exec sp_trace_setevent @traceidout, 12,22, @on --SQL:BatchCompleted,ObjectID   
  exec sp_trace_setevent @traceidout, 12,23, @on --SQL:BatchCompleted,Success   
  exec sp_trace_setevent @traceidout, 12,24, @on --SQL:BatchCompleted,IndexID   
  exec sp_trace_setevent @traceidout, 12,25, @on --SQL:BatchCompleted,IntegerData   
  exec sp_trace_setevent @traceidout, 12,26, @on --SQL:BatchCompleted,ServerName   
  exec sp_trace_setevent @traceidout, 12,27, @on --SQL:BatchCompleted,EventClass   
  exec sp_trace_setevent @traceidout, 12,28, @on --SQL:BatchCompleted,ObjectType   
  exec sp_trace_setevent @traceidout, 12,29, @on --SQL:BatchCompleted,NestLevel   
  exec sp_trace_setevent @traceidout, 12,30, @on --SQL:BatchCompleted,State   
  exec sp_trace_setevent @traceidout, 12,31, @on --SQL:BatchCompleted,Error   
  exec sp_trace_setevent @traceidout, 12,32, @on --SQL:BatchCompleted,Mode   
  exec sp_trace_setevent @traceidout, 12,33, @on --SQL:BatchCompleted,Handle   
  exec sp_trace_setevent @traceidout, 12,34, @on --SQL:BatchCompleted,ObjectName   
  exec sp_trace_setevent @traceidout, 12,35, @on --SQL:BatchCompleted,DatabaseName   
  exec sp_trace_setevent @traceidout, 12,36, @on --SQL:BatchCompleted,FileName   
  exec sp_trace_setevent @traceidout, 12,37, @on --SQL:BatchCompleted,OwnerName   
  exec sp_trace_setevent @traceidout, 12,38, @on --SQL:BatchCompleted,RoleName   
  exec sp_trace_setevent @traceidout, 12,39, @on --SQL:BatchCompleted,TargetUserName   
  exec sp_trace_setevent @traceidout, 12,40, @on --SQL:BatchCompleted,DBUserName   
  exec sp_trace_setevent @traceidout, 12,41, @on --SQL:BatchCompleted,LoginSid   
  exec sp_trace_setevent @traceidout, 12,42, @on --SQL:BatchCompleted,TargetLoginName   
  exec sp_trace_setevent @traceidout, 12,43, @on --SQL:BatchCompleted,TargetLoginSid   
  exec sp_trace_setevent @traceidout, 12,44, @on --SQL:BatchCompleted,ColumnPermissions   
  exec sp_trace_setevent @traceidout, 12,45, @on --SQL:BatchCompleted,LinkedServerName   
  exec sp_trace_setevent @traceidout, 12,46, @on --SQL:BatchCompleted,ProviderName   
  exec sp_trace_setevent @traceidout, 12,47, @on --SQL:BatchCompleted,MethodName   
  exec sp_trace_setevent @traceidout, 12,48, @on --SQL:BatchCompleted,RowCounts   
  exec sp_trace_setevent @traceidout, 12,49, @on --SQL:BatchCompleted,RequestID   
  exec sp_trace_setevent @traceidout, 12,50, @on --SQL:BatchCompleted,XactSequence   
  exec sp_trace_setevent @traceidout, 12,51, @on --SQL:BatchCompleted,EventSequence   
  exec sp_trace_setevent @traceidout, 12,52, @on --SQL:BatchCompleted,BigintData1   
  exec sp_trace_setevent @traceidout, 12,53, @on --SQL:BatchCompleted,BigintData2   
  exec sp_trace_setevent @traceidout, 12,54, @on --SQL:BatchCompleted,GUID   
  exec sp_trace_setevent @traceidout, 12,55, @on --SQL:BatchCompleted,IntegerData2   
  exec sp_trace_setevent @traceidout, 12,56, @on --SQL:BatchCompleted,ObjectID2   
  exec sp_trace_setevent @traceidout, 12,57, @on --SQL:BatchCompleted,Type   
  exec sp_trace_setevent @traceidout, 12,58, @on --SQL:BatchCompleted,OwnerID   
  exec sp_trace_setevent @traceidout, 12,59, @on --SQL:BatchCompleted,ParentName   
  exec sp_trace_setevent @traceidout, 12,60, @on --SQL:BatchCompleted,IsSystem   
  exec sp_trace_setevent @traceidout, 12,61, @on --SQL:BatchCompleted,Offset   
  exec sp_trace_setevent @traceidout, 12,62, @on --SQL:BatchCompleted,SourceDatabaseID   
  exec sp_trace_setevent @traceidout, 12,63, @on --SQL:BatchCompleted,SqlHandle   
  exec sp_trace_setevent @traceidout, 12,64, @on --SQL:BatchCompleted,SessionLoginName   
  --adding RPC calls: When an application executes a stored procedure, only a simple, small RPC (remote procedure call) is made from the client to SQL Server.  
  exec sp_trace_setevent @traceidout, 10, 1, @on --RPC:Completed,TextData   
  exec sp_trace_setevent @traceidout, 10, 2, @on --RPC:Completed,BinaryData   
  exec sp_trace_setevent @traceidout, 10, 3, @on --RPC:Completed,DatabaseID   
  exec sp_trace_setevent @traceidout, 10, 4, @on --RPC:Completed,TransactionID   
  exec sp_trace_setevent @traceidout, 10, 5, @on --RPC:Completed,LineNumber   
  exec sp_trace_setevent @traceidout, 10, 6, @on --RPC:Completed,NTUserName   
  exec sp_trace_setevent @traceidout, 10, 7, @on --RPC:Completed,NTDomainName   
  exec sp_trace_setevent @traceidout, 10, 8, @on --RPC:Completed,HostName   
  exec sp_trace_setevent @traceidout, 10, 9, @on --RPC:Completed, ClientProcessID   
  exec sp_trace_setevent @traceidout, 10,10, @on --RPC:Completed,ApplicationName   
  exec sp_trace_setevent @traceidout, 10,11, @on --RPC:Completed,LoginName   
  exec sp_trace_setevent @traceidout, 10,12, @on --RPC:Completed,SPID   
  exec sp_trace_setevent @traceidout, 10,13, @on --RPC:Completed,Duration   
  exec sp_trace_setevent @traceidout, 10,14, @on --RPC:Completed,StartTime   
  exec sp_trace_setevent @traceidout, 10,15, @on --RPC:Completed,EndTime   
  exec sp_trace_setevent @traceidout, 10,16, @on --RPC:Completed,Reads   
  exec sp_trace_setevent @traceidout, 10,17, @on --RPC:Completed,Writes   
  exec sp_trace_setevent @traceidout, 10,18, @on --RPC:Completed,CPU   
  exec sp_trace_setevent @traceidout, 10,19, @on --RPC:Completed,Permissions   
  exec sp_trace_setevent @traceidout, 10,20, @on --RPC:Completed,Severity   
  exec sp_trace_setevent @traceidout, 10,21, @on --RPC:Completed,EventSubClass   
  exec sp_trace_setevent @traceidout, 10,22, @on --RPC:Completed,ObjectID   
  exec sp_trace_setevent @traceidout, 10,23, @on --RPC:Completed,Success   
  exec sp_trace_setevent @traceidout, 10,24, @on --RPC:Completed,IndexID   
  exec sp_trace_setevent @traceidout, 10,25, @on --RPC:Completed,IntegerData   
  exec sp_trace_setevent @traceidout, 10,26, @on --RPC:Completed,ServerName   
  exec sp_trace_setevent @traceidout, 10,27, @on --RPC:Completed,EventClass   
  exec sp_trace_setevent @traceidout, 10,28, @on --RPC:Completed,ObjectType   
  exec sp_trace_setevent @traceidout, 10,29, @on --RPC:Completed,NestLevel   
  exec sp_trace_setevent @traceidout, 10,30, @on --RPC:Completed,State   
  exec sp_trace_setevent @traceidout, 10,31, @on --RPC:Completed,Error   
  exec sp_trace_setevent @traceidout, 10,32, @on --RPC:Completed,Mode   
  exec sp_trace_setevent @traceidout, 10,33, @on --RPC:Completed,Handle   
  exec sp_trace_setevent @traceidout, 10,34, @on --RPC:Completed,ObjectName   
  exec sp_trace_setevent @traceidout, 10,35, @on --RPC:Completed,DatabaseName   
  exec sp_trace_setevent @traceidout, 10,36, @on --RPC:Completed,FileName   
  exec sp_trace_setevent @traceidout, 10,37, @on --RPC:Completed,OwnerName   
  exec sp_trace_setevent @traceidout, 10,38, @on --RPC:Completed,RoleName   
  exec sp_trace_setevent @traceidout, 10,39, @on --RPC:Completed,TargetUserName   
  exec sp_trace_setevent @traceidout, 10,40, @on --RPC:Completed,DBUserName   
  exec sp_trace_setevent @traceidout, 10,41, @on --RPC:Completed,LoginSid   
  exec sp_trace_setevent @traceidout, 10,42, @on --RPC:Completed,TargetLoginName   
  exec sp_trace_setevent @traceidout, 10,43, @on --RPC:Completed,TargetLoginSid   
  exec sp_trace_setevent @traceidout, 10,44, @on --RPC:Completed,ColumnPermissions   
  exec sp_trace_setevent @traceidout, 10,45, @on --RPC:Completed,LinkedServerName   
  exec sp_trace_setevent @traceidout, 10,46, @on --RPC:Completed,ProviderName   
  exec sp_trace_setevent @traceidout, 10,47, @on --RPC:Completed,MethodName   
  exec sp_trace_setevent @traceidout, 10,48, @on --RPC:Completed,RowCounts   
  exec sp_trace_setevent @traceidout, 10,49, @on --RPC:Completed,RequestID   
  exec sp_trace_setevent @traceidout, 10,50, @on --RPC:Completed,XactSequence   
  exec sp_trace_setevent @traceidout, 10,51, @on --RPC:Completed,EventSequence   
  exec sp_trace_setevent @traceidout, 10,52, @on --RPC:Completed,BigintData1   
  exec sp_trace_setevent @traceidout, 10,53, @on --RPC:Completed,BigintData2   
  exec sp_trace_setevent @traceidout, 10,54, @on --RPC:Completed,GUID   
  exec sp_trace_setevent @traceidout, 10,55, @on --RPC:Completed,IntegerData2   
  exec sp_trace_setevent @traceidout, 10,56, @on --RPC:Completed,ObjectID2   
  exec sp_trace_setevent @traceidout, 10,57, @on --RPC:Completed,Type   
  exec sp_trace_setevent @traceidout, 10,58, @on --RPC:Completed,OwnerID   
  exec sp_trace_setevent @traceidout, 10,59, @on --RPC:Completed,ParentName   
  exec sp_trace_setevent @traceidout, 10,60, @on --RPC:Completed,IsSystem   
  exec sp_trace_setevent @traceidout, 10,61, @on --RPC:Completed,Offset   
  exec sp_trace_setevent @traceidout, 10,62, @on --RPC:Completed,SourceDatabaseID   
  exec sp_trace_setevent @traceidout, 10,63, @on --RPC:Completed,SqlHandle   
  exec sp_trace_setevent @traceidout, 10,64, @on --RPC:Completed,SessionLoginName   
  --turn on the trace  
  exec sp_trace_setstatus @traceidout, 1 ---start trace  
  --exec sp_trace_setstatus TRACEID, 0 ---stop trace, you must know the traceid to stop it  
  --exec sp_trace_setstatus TRACEID, 2 ---close trace you must know the traceid to delete it  
IF EXISTS(select * from master.dbo.sysobjects where xtype='V' and name='sp_DMLTrace')    
  BEGIN    
    SET @sql = 'ALTER VIEW sp_DMLTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path +'.trc'', default)'    
    exec(@sql)    
  END    
ELSE    
  BEGIN    
    SET @sql = 'CREATE VIEW sp_DMLTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path + '.trc'', default)'    
    exec(@sql)    
  END    
  COMMIT TRAN  
END

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #745586

dev_programmer

Posted Thursday, July 02, 2009 10:07 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 20, 2012 3:32 AM
Points: 34, Visits: 126

Dear Expert,
Thank You very much for your help. I will revert as soon as i test this.

Thanks so much

Thank you,
Regards,
Dev
email id :- dev_programmer@yahoo.co.in

Post #746423

vinothd

Posted Sunday, July 05, 2009 10:25 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 02, 2010 9:53 PM
Points: 3, Visits: 26

Sir,
With regard to the post i've ran the SP file after that wha would happen?As i'm new to the Profiler i'm not sure what would happen.

Post #747496

Lowell

Posted Monday, July 06, 2009 5:46 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 11,605, Visits: 27,645

Hi vinothd;
basically the script does two things, it creates a trace on the server; server side traces do not have an appreciable impact on the server.
second, it creates a view so that you can review the results of the trace. simply do a
SELECT * FROM sp_DMLTrace
to see the whole trace, and then start adding WHERE statements to limit the rows you see and the specific transactions you want to review.

vinothd (7/5/2009)

Sir,
With regard to the post i've ran the SP file after that wha would happen?As i'm new to the Profiler i'm not sure what would happen.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #747634

vinothd

Posted Monday, July 06, 2009 6:06 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 02, 2010 9:53 PM
Points: 3, Visits: 26

Hi Lowell,

Thanks for u'r prompt reply.I went and viewed the trace thru the view created.
I have a couple of questions.

1.Is there any Option to Run the trace for a selected DB
2.Where does the trace file gets stored physically?can we view the trace file?

Thanks ,
Vinoth.D

Post #747643

Lowell

Posted Monday, July 06, 2009 6:24 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 11,605, Visits: 27,645

vinothd (7/6/2009)

the actual folder depends on your machine; run this statement to get the actual path:

 SELECT REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) FROM sys.traces WHERE is_default = 1

on my machine, the path is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

physical files named MyDMLtrace.trc, and eventually MyDMLtrace_1.trc,MyDMLtrace_2.trc, etc will get created in the folder below; you can open them in notepad or a good text editor, but it looks a little funky sometimes. the view is MUCH better for reviewing the details.

take a look at the level of detail in he view; since it has some of the performance statistics in there as well, you can also use it to see what queries are running the slowest, to help you evaluate those types of factors as well.

yours may be slightly different.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #747651

vinothd

Posted Monday, July 06, 2009 10:16 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 02, 2010 9:53 PM
Points: 3, Visits: 26

Lowell,

I tried executing the Procedure on my server and both the trace and the view have sucessfully got created .I have one last question.The Event Viewer tries to track all the events like the security audit part.Can we prevent this to get recorded?Futher i have files as MyDMLtrace,MyDMLtrace_1,MyDMLtrace_2 getting created why are there more than one file with the same name?

Thanks in Advance,
Vinoth.D

Post #748294

Lowell

Posted Monday, July 06, 2009 10:46 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 11,605, Visits: 27,645

vinothd (7/6/2009)

Not exactly sure what you are refering to...
the trace i proffered catches most statements...to exclude anything, you'd have to adjust it...i see no harm in captuing lots of data, and then selecting the data you want via a WHERE statement.
if you can offer a specific item that was in the trace that you do not want to capture, I might be able to offer a suggestion.

Futher i have files as MyDMLtrace,MyDMLtrace_1,MyDMLtrace_2 getting created why are there more than one file with the same name?

Thanks in Advance,
Vinoth.D

this has to do with two variables in the trace creation:@maxfilesize bigint , which is 50 meg in our example, and @maxRolloverFiles int , which we set at 2, the number of additional files to create once each file gets filled with 50 meg of data. you can read up on the details of creating a trace for that, you could make it one huge 1 gig or more file, with no additional rollover files, but that's all up to you...this is just a good example for you to play with.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #748303

Krasavita

Posted Sunday, July 26, 2009 8:23 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, April 19, 2013 8:15 AM
Points: 1,251, Visits: 1,840

Thank you,I created sp under master,but don't see the view, where the view should appear? I search for it and can't find it

Post #759770

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions