Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL TRACE Expand / Collapse
Author
Message
Posted Wednesday, July 1, 2009 10:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, July 1, 2009 10:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 12,910, Visits: 32,020
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
Posted Thursday, July 2, 2009 10:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Sunday, July 5, 2009 10:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 2, 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
Posted Monday, July 6, 2009 5:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 12,910, Visits: 32,020
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
Posted Monday, July 6, 2009 6:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 2, 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
Posted Monday, July 6, 2009 6:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 12,910, Visits: 32,020
vinothd (7/6/2009)
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

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
Posted Monday, July 6, 2009 10:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 2, 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
Posted Monday, July 6, 2009 10:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 12,910, Visits: 32,020
vinothd (7/6/2009)
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?

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
Posted Sunday, July 26, 2009 8:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 11:58 AM
Points: 1,279, Visits: 1,886
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 »

Add to briefcase 12»»

Permissions Expand / Collapse