July 1, 2009 at 10:09 am
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
July 1, 2009 at 10:19 am
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
July 2, 2009 at 10:07 am
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
July 5, 2009 at 10:25 pm
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.
July 6, 2009 at 5:46 am
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
July 6, 2009 at 6:06 am
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
July 6, 2009 at 6:24 am
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
July 6, 2009 at 10:16 pm
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
July 6, 2009 at 10:46 pm
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
July 26, 2009 at 8:23 am
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
July 26, 2009 at 9:30 am
just to clarify, if you run the script above, ti doesn't do anything except add a stored procedure...
if you call the procedure, ie EXEC sp_AddMyTrace, that creates/starts the trace and creates the view....did you do that second step? I'm thinking that is the piece that is missing.
Lowell
July 26, 2009 at 11:20 am
Yes, I ran your code to create the view and then I ran EXEC sp_AddMyTrace and I get:
Cannot create a new trace because the trace file path is found in the existing traces.
March 1, 2010 at 2:25 pm
Lowell,
What is the command to STOP the trace once it has started running?
Thanks,
ZeeAtl
Accidental DBA
March 1, 2010 at 3:08 pm
ZeeAtl you need to find the traceid by select * from sys.traces
then:
--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
ZeeAtl (3/1/2010)
Lowell,What is the command to STOP the trace once it has started running?
Thanks,
ZeeAtl
Accidental DBA
Lowell
March 1, 2010 at 3:09 pm
http://msdn.microsoft.com/en-us/library/ms176034(SQL.90).aspx
Craig Outcalt
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply