Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Real-time trace file updates? Traces as txt files?

Author  Topic 

acoleman616
Starting Member

8 Posts
Posted - 2006-10-20 : 09:30:53
Hello.

I've created an auditing trace in MS SQL server, but am having some problems.

First of all, the trace files only seem to update when the server is stopped and re-started. Is there any possible way to get them to update in "real-time"?

Secondly, I have a tool to analyze the traces, but it needs input of text files, not .trc's. Is there any way for trace files to be generated as text files, or any way to convert them?

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-10-20 : 12:49:13
You need to explain how you have implemented your trace in order for us to know why it is only working when you stop and restart the server.

You can do a Save As in SQL Profiler to convert them.

Tara Kizer
Go to Top of Page

acoleman616
Starting Member

8 Posts
Posted - 2006-10-20 : 13:14:20
quote:
Originally posted by tkizer

You need to explain how you have implemented your trace in order for us to know why it is only working when you stop and restart the server.

You can do a Save As in SQL Profiler to convert them.

Tara Kizer


In the profiler, I do not see an option to save as a text file. The options I get are: Trace Template, Trace File, Trace Table, and SQL Script.

As for the trace, it was implemented using the sql file below.


if exists (select * from sysobjects where id = object_id('dbo.Audit_sp') 
		and sysstat & 0xf = 4)
	drop procedure dbo.Audit_sp
go
CREATE PROCEDURE Audit_sp
as
begin
--see what traces are running
--SELECT * FROM :: fn_trace_getinfo(default) 
--turn off a trace
--exec sp_trace setstatus @TraceID,0
--set sql server to use this proc on startup
--exec sp_procoption 'Audit_sp','startup','true'
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @location nvarchar(128)
declare @on bit
set @maxfilesize = 200 --mb
--change file location below for each install
set @location = 'C:\Audit\' + convert(varchar,datepart(ms,getdate()))
set @on = 1
exec @rc = sp_trace_create @TraceID output, 6, @location, @maxfilesize, NULL
if @rc = 0
begin
	--Audit Add DB User
	exec sp_trace_setevent @TraceID, 109, 10, @on
	exec sp_trace_setevent @TraceID, 109, 11, @on
	exec sp_trace_setevent @TraceID, 109, 12, @on
	exec sp_trace_setevent @TraceID, 109, 14, @on
	exec sp_trace_setevent @TraceID, 109, 15, @on
	exec sp_trace_setevent @TraceID, 109, 21, @on
	exec sp_trace_setevent @TraceID, 109, 22, @on
	exec sp_trace_setevent @TraceID, 109, 23, @on
	exec sp_trace_setevent @TraceID, 109, 28, @on
	exec sp_trace_setevent @TraceID, 109, 35, @on
	exec sp_trace_setevent @TraceID, 109, 41, @on
	exec sp_trace_setevent @TraceID, 109, 8, @on
	--Audit Add Login to Server Role
	exec sp_trace_setevent @TraceID, 108, 10, @on
	exec sp_trace_setevent @TraceID, 108, 11, @on
	exec sp_trace_setevent @TraceID, 108, 12, @on
	exec sp_trace_setevent @TraceID, 108, 14, @on
	exec sp_trace_setevent @TraceID, 108, 15, @on
	exec sp_trace_setevent @TraceID, 108, 21, @on
	exec sp_trace_setevent @TraceID, 108, 22, @on
	exec sp_trace_setevent @TraceID, 108, 23, @on
	exec sp_trace_setevent @TraceID, 108, 28, @on
	exec sp_trace_setevent @TraceID, 108, 35, @on
	exec sp_trace_setevent @TraceID, 108, 41, @on
	exec sp_trace_setevent @TraceID, 108, 8, @on
	--Audit Add Member to DB
	exec sp_trace_setevent @TraceID, 110, 10, @on
	exec sp_trace_setevent @TraceID, 110, 11, @on
	exec sp_trace_setevent @TraceID, 110, 12, @on
	exec sp_trace_setevent @TraceID, 110, 14, @on
	exec sp_trace_setevent @TraceID, 110, 15, @on
	exec sp_trace_setevent @TraceID, 110, 21, @on
	exec sp_trace_setevent @TraceID, 110, 22, @on
	exec sp_trace_setevent @TraceID, 110, 23, @on
	exec sp_trace_setevent @TraceID, 110, 28, @on
	exec sp_trace_setevent @TraceID, 110, 35, @on
	exec sp_trace_setevent @TraceID, 110, 41, @on
	exec sp_trace_setevent @TraceID, 110, 8, @on
	--Audit Add/Drop Role
	exec sp_trace_setevent @TraceID, 111, 10, @on
	exec sp_trace_setevent @TraceID, 111, 11, @on
	exec sp_trace_setevent @TraceID, 111, 12, @on
	exec sp_trace_setevent @TraceID, 111, 14, @on
	exec sp_trace_setevent @TraceID, 111, 15, @on
	exec sp_trace_setevent @TraceID, 111, 21, @on
	exec sp_trace_setevent @TraceID, 111, 22, @on
	exec sp_trace_setevent @TraceID, 111, 23, @on
	exec sp_trace_setevent @TraceID, 111, 28, @on
	exec sp_trace_setevent @TraceID, 111, 35, @on
	exec sp_trace_setevent @TraceID, 111, 41, @on
	exec sp_trace_setevent @TraceID, 111, 8, @on
	--Audit Add/Drop Login
	exec sp_trace_setevent @TraceID, 104, 10, @on
	exec sp_trace_setevent @TraceID, 104, 11, @on
	exec sp_trace_setevent @TraceID, 104, 12, @on
	exec sp_trace_setevent @TraceID, 104, 14, @on
	exec sp_trace_setevent @TraceID, 104, 15, @on
	exec sp_trace_setevent @TraceID, 104, 21, @on
	exec sp_trace_setevent @TraceID, 104, 22, @on
	exec sp_trace_setevent @TraceID, 104, 23, @on
	exec sp_trace_setevent @TraceID, 104, 28, @on
	exec sp_trace_setevent @TraceID, 104, 35, @on
	exec sp_trace_setevent @TraceID, 104, 41, @on
	exec sp_trace_setevent @TraceID, 104, 8, @on
	--App Role Pass Change
	exec sp_trace_setevent @TraceID, 112, 10, @on
	exec sp_trace_setevent @TraceID, 112, 11, @on
	exec sp_trace_setevent @TraceID, 112, 12, @on
	exec sp_trace_setevent @TraceID, 112, 14, @on
	exec sp_trace_setevent @TraceID, 112, 15, @on
	exec sp_trace_setevent @TraceID, 112, 21, @on
	exec sp_trace_setevent @TraceID, 112, 22, @on
	exec sp_trace_setevent @TraceID, 112, 23, @on
	exec sp_trace_setevent @TraceID, 112, 28, @on
	exec sp_trace_setevent @TraceID, 112, 35, @on
	exec sp_trace_setevent @TraceID, 112, 41, @on
	exec sp_trace_setevent @TraceID, 112, 8, @on
	--Audit Backup/Restore
	exec sp_trace_setevent @TraceID, 115, 10, @on
	exec sp_trace_setevent @TraceID, 115, 11, @on
	exec sp_trace_setevent @TraceID, 115, 12, @on
	exec sp_trace_setevent @TraceID, 115, 14, @on
	exec sp_trace_setevent @TraceID, 115, 15, @on
	exec sp_trace_setevent @TraceID, 115, 21, @on
	exec sp_trace_setevent @TraceID, 115, 22, @on
	exec sp_trace_setevent @TraceID, 115, 23, @on
	exec sp_trace_setevent @TraceID, 115, 28, @on
	exec sp_trace_setevent @TraceID, 115, 35, @on
	exec sp_trace_setevent @TraceID, 115, 41, @on
	exec sp_trace_setevent @TraceID, 115, 8, @on
	--Audit Change Audit
	exec sp_trace_setevent @TraceID, 117, 10, @on
	exec sp_trace_setevent @TraceID, 117, 11, @on
	exec sp_trace_setevent @TraceID, 117, 12, @on
	exec sp_trace_setevent @TraceID, 117, 14, @on
	exec sp_trace_setevent @TraceID, 117, 15, @on
	exec sp_trace_setevent @TraceID, 117, 21, @on
	exec sp_trace_setevent @TraceID, 117, 22, @on
	exec sp_trace_setevent @TraceID, 117, 23, @on
	exec sp_trace_setevent @TraceID, 117, 28, @on
	exec sp_trace_setevent @TraceID, 117, 35, @on
	exec sp_trace_setevent @TraceID, 117, 41, @on
	exec sp_trace_setevent @TraceID, 117, 8, @on
	--Audit DBCC
	exec sp_trace_setevent @TraceID, 116, 10, @on
	exec sp_trace_setevent @TraceID, 116, 11, @on
	exec sp_trace_setevent @TraceID, 116, 12, @on
	exec sp_trace_setevent @TraceID, 116, 14, @on
	exec sp_trace_setevent @TraceID, 116, 15, @on
	exec sp_trace_setevent @TraceID, 116, 21, @on
	exec sp_trace_setevent @TraceID, 116, 22, @on
	exec sp_trace_setevent @TraceID, 116, 23, @on
	exec sp_trace_setevent @TraceID, 116, 28, @on
	exec sp_trace_setevent @TraceID, 116, 35, @on
	exec sp_trace_setevent @TraceID, 116, 41, @on
	exec sp_trace_setevent @TraceID, 116, 8, @on
--add text to DBCC event
--exec sp_trace_setevent 1, 116, 1, @on
	--Login
	exec sp_trace_setevent @TraceID, 14, 10, @on
	exec sp_trace_setevent @TraceID, 14, 11, @on
	exec sp_trace_setevent @TraceID, 14, 12, @on
	exec sp_trace_setevent @TraceID, 14, 14, @on
	exec sp_trace_setevent @TraceID, 14, 15, @on
	exec sp_trace_setevent @TraceID, 14, 21, @on
	exec sp_trace_setevent @TraceID, 14, 22, @on
	exec sp_trace_setevent @TraceID, 14, 23, @on
	exec sp_trace_setevent @TraceID, 14, 28, @on
	exec sp_trace_setevent @TraceID, 14, 35, @on
	exec sp_trace_setevent @TraceID, 14, 41, @on
	exec sp_trace_setevent @TraceID, 14, 8, @on
	--Audit Login Change Password
	exec sp_trace_setevent @TraceID, 107, 10, @on
	exec sp_trace_setevent @TraceID, 107, 11, @on
	exec sp_trace_setevent @TraceID, 107, 12, @on
	exec sp_trace_setevent @TraceID, 107, 14, @on
	exec sp_trace_setevent @TraceID, 107, 15, @on
	exec sp_trace_setevent @TraceID, 107, 21, @on
	exec sp_trace_setevent @TraceID, 107, 22, @on
	exec sp_trace_setevent @TraceID, 107, 23, @on
	exec sp_trace_setevent @TraceID, 107, 28, @on
	exec sp_trace_setevent @TraceID, 107, 35, @on
	exec sp_trace_setevent @TraceID, 107, 41, @on
	exec sp_trace_setevent @TraceID, 107, 8, @on
	--Audit Login Change Property
	exec sp_trace_setevent @TraceID, 106, 10, @on
	exec sp_trace_setevent @TraceID, 106, 11, @on
	exec sp_trace_setevent @TraceID, 106, 12, @on
	exec sp_trace_setevent @TraceID, 106, 14, @on
	exec sp_trace_setevent @TraceID, 106, 15, @on
	exec sp_trace_setevent @TraceID, 106, 21, @on
	exec sp_trace_setevent @TraceID, 106, 22, @on
	exec sp_trace_setevent @TraceID, 106, 23, @on
	exec sp_trace_setevent @TraceID, 106, 28, @on
	exec sp_trace_setevent @TraceID, 106, 35, @on
	exec sp_trace_setevent @TraceID, 106, 41, @on
	exec sp_trace_setevent @TraceID, 106, 8, @on
	--Login Failed
	exec sp_trace_setevent @TraceID, 20, 10, @on
	exec sp_trace_setevent @TraceID, 20, 11, @on
	exec sp_trace_setevent @TraceID, 20, 12, @on
	exec sp_trace_setevent @TraceID, 20, 14, @on
	exec sp_trace_setevent @TraceID, 20, 15, @on
	exec sp_trace_setevent @TraceID, 20, 21, @on
	exec sp_trace_setevent @TraceID, 20, 22, @on
	exec sp_trace_setevent @TraceID, 20, 23, @on
	exec sp_trace_setevent @TraceID, 20, 28, @on
	exec sp_trace_setevent @TraceID, 20, 35, @on
	exec sp_trace_setevent @TraceID, 20, 41, @on
	exec sp_trace_setevent @TraceID, 20, 8, @on
	--Audit Login GDR
	exec sp_trace_setevent @TraceID, 105, 10, @on
	exec sp_trace_setevent @TraceID, 105, 11, @on
	exec sp_trace_setevent @TraceID, 105, 12, @on
	exec sp_trace_setevent @TraceID, 105, 14, @on
	exec sp_trace_setevent @TraceID, 105, 15, @on
	exec sp_trace_setevent @TraceID, 105, 21, @on
	exec sp_trace_setevent @TraceID, 105, 22, @on
	exec sp_trace_setevent @TraceID, 105, 23, @on
	exec sp_trace_setevent @TraceID, 105, 28, @on
	exec sp_trace_setevent @TraceID, 105, 35, @on
	exec sp_trace_setevent @TraceID, 105, 41, @on
	exec sp_trace_setevent @TraceID, 105, 8, @on
	--Logout
	exec sp_trace_setevent @TraceID, 15, 10, @on
	exec sp_trace_setevent @TraceID, 15, 11, @on
	exec sp_trace_setevent @TraceID, 15, 12, @on
	exec sp_trace_setevent @TraceID, 15, 14, @on
	exec sp_trace_setevent @TraceID, 15, 15, @on
	exec sp_trace_setevent @TraceID, 15, 21, @on
	exec sp_trace_setevent @TraceID, 15, 22, @on
	exec sp_trace_setevent @TraceID, 15, 23, @on
	exec sp_trace_setevent @TraceID, 15, 28, @on
	exec sp_trace_setevent @TraceID, 15, 35, @on
	exec sp_trace_setevent @TraceID, 15, 41, @on
	exec sp_trace_setevent @TraceID, 15, 8, @on
	--Audit Object Derived Permission
	exec sp_trace_setevent @TraceID, 118, 10, @on
	exec sp_trace_setevent @TraceID, 118, 11, @on
	exec sp_trace_setevent @TraceID, 118, 12, @on
	exec sp_trace_setevent @TraceID, 118, 14, @on
	exec sp_trace_setevent @TraceID, 118, 15, @on
	exec sp_trace_setevent @TraceID, 118, 21, @on
	exec sp_trace_setevent @TraceID, 118, 22, @on
	exec sp_trace_setevent @TraceID, 118, 23, @on
	exec sp_trace_setevent @TraceID, 118, 28, @on
	exec sp_trace_setevent @TraceID, 118, 35, @on
	exec sp_trace_setevent @TraceID, 118, 41, @on
	exec sp_trace_setevent @TraceID, 118, 8, @on
	--Audit Object GDR
	exec sp_trace_setevent @TraceID, 103, 10, @on
	exec sp_trace_setevent @TraceID, 103, 11, @on
	exec sp_trace_setevent @TraceID, 103, 12, @on
	exec sp_trace_setevent @TraceID, 103, 14, @on
	exec sp_trace_setevent @TraceID, 103, 15, @on
	exec sp_trace_setevent @TraceID, 103, 21, @on
	exec sp_trace_setevent @TraceID, 103, 22, @on
	exec sp_trace_setevent @TraceID, 103, 23, @on
	exec sp_trace_setevent @TraceID, 103, 28, @on
	exec sp_trace_setevent @TraceID, 103, 35, @on
	exec sp_trace_setevent @TraceID, 103, 41, @on
	exec sp_trace_setevent @TraceID, 103, 8, @on
	--ServiceControl
	exec sp_trace_setevent @TraceID, 18, 10, @on
	exec sp_trace_setevent @TraceID, 18, 11, @on
	exec sp_trace_setevent @TraceID, 18, 12, @on
	exec sp_trace_setevent @TraceID, 18, 14, @on
	exec sp_trace_setevent @TraceID, 18, 15, @on
	exec sp_trace_setevent @TraceID, 18, 21, @on
	exec sp_trace_setevent @TraceID, 18, 22, @on
	exec sp_trace_setevent @TraceID, 18, 23, @on
	exec sp_trace_setevent @TraceID, 18, 28, @on
	exec sp_trace_setevent @TraceID, 18, 35, @on
	exec sp_trace_setevent @TraceID, 18, 41, @on
	exec sp_trace_setevent @TraceID, 18, 8, @on
	--Audit Statement GDR
	exec sp_trace_setevent @TraceID, 102, 10, @on
	exec sp_trace_setevent @TraceID, 102, 11, @on
	exec sp_trace_setevent @TraceID, 102, 12, @on
	exec sp_trace_setevent @TraceID, 102, 14, @on
	exec sp_trace_setevent @TraceID, 102, 15, @on
	exec sp_trace_setevent @TraceID, 102, 21, @on
	exec sp_trace_setevent @TraceID, 102, 22, @on
	exec sp_trace_setevent @TraceID, 102, 23, @on
	exec sp_trace_setevent @TraceID, 102, 28, @on
	exec sp_trace_setevent @TraceID, 102, 35, @on
	exec sp_trace_setevent @TraceID, 102, 41, @on
	exec sp_trace_setevent @TraceID, 102, 8, @on
	--Audit Statement Permission
	exec sp_trace_setevent @TraceID, 113, 10, @on
	exec sp_trace_setevent @TraceID, 113, 11, @on
	exec sp_trace_setevent @TraceID, 113, 12, @on
	exec sp_trace_setevent @TraceID, 113, 14, @on
	exec sp_trace_setevent @TraceID, 113, 15, @on
	exec sp_trace_setevent @TraceID, 113, 21, @on
	exec sp_trace_setevent @TraceID, 113, 22, @on
	exec sp_trace_setevent @TraceID, 113, 23, @on
	exec sp_trace_setevent @TraceID, 113, 28, @on
	exec sp_trace_setevent @TraceID, 113, 35, @on
	exec sp_trace_setevent @TraceID, 113, 41, @on
	exec sp_trace_setevent @TraceID, 113, 8, @on
	exec sp_trace_setstatus @TraceID,1
end
end
go


Thanks so much for the help Tara.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-10-20 : 13:18:21
Put the stored procedure in a job. Have it run any time the Agent starts.

Tara Kizer
Go to Top of Page

acoleman616
Starting Member

8 Posts
Posted - 2006-10-20 : 13:36:21
quote:
Originally posted by tkizer

Put the stored procedure in a job. Have it run any time the Agent starts.

Tara Kizer


I'm not sure I quite understand.

And any other ideas on saving the traces as text files?

Thanks again.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-10-20 : 13:39:45
You need to select a different tool to analyze your files as saving it to a text file will require to hops (from saving it to a table then exporting it to csv) and who knows what the tool requires as the format.

The owner of this site has a tool to analyze trace data:
http://weblogs.sqlteam.com/billg/archive/2006/07/10/10555.aspx

Tara Kizer
Go to Top of Page

acoleman616
Starting Member

8 Posts
Posted - 2006-10-20 : 13:47:15
Tara,

If my sql code file is too long for one task in the job, should it just be split up into different parts, creating multiple tasks?

Thanks.
Go to Top of Page

acoleman616
Starting Member

8 Posts
Posted - 2006-10-20 : 13:50:49
And actually, it appears that your suggestion ultimately does the same thing. I currently have the sql auditing script setup to run whenever the sql server service is started, and it collects the data realtime...my dilemma is that it does not update the actual trace file until the server is stopped and restarted. i don't want to have to create new trace files, I just want the trace file to update itself when a new event is recorded. As it stands right now, the trace file appears to be of size 0 until the service is stopped and restarted.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-10-20 : 13:59:08
I guess I don't understand what the problem is then.

Tara Kizer
Go to Top of Page

acoleman616
Starting Member

8 Posts
Posted - 2006-10-20 : 14:02:25
quote:
Originally posted by tkizer

I guess I don't understand what the problem is then.

Tara Kizer


I want the trace file to update itself real-time, or periodically at least, with all of the events it recorded (audited) without having to stop and re-start the server.

Here's how it works right now:
-The SQL Server service is started.
-A new trace file is recorded, but is 0kb.
-Events are recorded, but the trace file is still 0kb.
-The SQL Server service is stopped, and NOW the trace file shows size, and you can view all of the events it's recorded.

I want to be able to keep the SQL server service runner WHILE the trace file updates itself.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-10-20 : 14:09:49
I'll post what we use. 


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



ALTER  PROCEDURE Trace_Start 

	@Srvr	VARCHAR(128),
	@Path	NVARCHAR(256)

AS
BEGIN /* Procedure */

	DECLARE 
		@Hndl 		INT,
		@RetVal	INT,
		@Bucket 	INT,
		@BucketSize 	BIGINT,
		@OnBit		BIT,
		@sqltext	VARCHAR(1000),
		@Trace	NVARCHAR(256),
		@TraceFile	NVARCHAR(256)

	SELECT 
		@Srvr = REPLACE(@@SERVERNAME, '\', '$'),
		@OnBit = 1,
		@Bucket = 0,
		@BucketSize = 1024,
		@Trace = @Path + N'\' + @Srvr + N'_Trace',
		@TraceFile = @Trace + N'_' + CONVERT(NVARCHAR, @Bucket),
		@sqltext = N'del "' + @TraceFile + N'.trc"'

	TRUNCATE TABLE [dbo].[Trace_QueueInfo]

	-- For SQL 2005, you will need to enable xp_cmdshell with sp_configure
	EXEC @RetVal = master.dbo.xp_cmdshell @sqltext

	EXEC @RetVal = dbo.sp_trace_create 
		@Hndl OUTPUT,	-- @traceid
		0,			-- @options
		@TraceFile,		-- @tracefile
		@maxfilesize = @BucketSize

	INSERT INTO [dbo].[Trace_QueueInfo] 
	(
		[Handle], 
		[Dest_Server],
		[Dest_File],
		[Bucket]
	) 
	VALUES
	(
		@Hndl, 
		@Srvr,
		@Trace,
		@Bucket
	)

	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 1, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 3, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 6, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 7, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 8, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 10, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 11, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 13, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 14, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 15, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 16, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 17, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 18, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 35, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 40, @on = @OnBit
	
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 1, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 3, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 6, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 7, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 8, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 10, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 11, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 13, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 14, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 15, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 16, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 17, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 18, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 35, @on = @OnBit
	EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 40, @on = @OnBit
	EXEC dbo.sp_trace_setstatus @traceid = @Hndl, @status = 1 -- starts the trace

END /* Procedure */

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



There's a lot more code involved with what we use, but this should give you an idea. BTW, I didn't write any of this code. I attended PASS 2005 conference last year and this was presented to us. I've been running it for the past 6 months or so in production.

Tara Kizer
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-10-20 : 14:11:37
Here's the swap that runs every minute so that we have access to the data:


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



ALTER  PROCEDURE Trace_Swap

AS
BEGIN /* Procedure */

	DECLARE 
		@Hndl			INT,
		@Srvr			VARCHAR(128),
		@Trace		NVARCHAR(256),
		@OldTraceFile		NVARCHAR(256),
		@NewTraceFile	NVARCHAR(256),
		@OldBucket		INT,
		@NewBucket		INT,
		@BucketSize		BIGINT,
		@RetVal		INT,
		@OnBit			BIT,
		@sqltext		VARCHAR(1000)

	CREATE TABLE #TraceQueues
	(
		[Handle]		INT
	)
	
	INSERT INTO #TraceQueues
	SELECT DISTINCT 
		[traceid] 
	FROM 
		:: fn_trace_getinfo(DEFAULT) 

	SET @OnBit = 1
	SET @BucketSize = 1024

	SELECT 
		@Hndl = [Handle],
		@Srvr = [Dest_Server],
		@Trace = [Dest_File],
		@OldBucket = [Bucket]
	 FROM 
		[dbo].[Trace_QueueInfo] WITH (NOLOCK)

	SET @OldTraceFile = @Trace + N'_' + CONVERT(NCHAR(1), @OldBucket)

	SET @NewBucket = 1 - @OldBucket -- switch to the new bucket

	SET @NewTraceFile = @Trace + N'_' + CONVERT(NCHAR(1), @NewBucket)  -- create a new trace file

	TRUNCATE TABLE [dbo].[Trace_QueueInfo]

	IF @Hndl IN (SELECT [Handle] FROM #TraceQueues) 
	BEGIN
		EXEC dbo.sp_trace_setstatus @traceid = @Hndl, @status = 0 -- stops the trace
		EXEC dbo.sp_trace_setstatus @traceid = @Hndl, @status = 2 -- removes the trace

		SET @sqltext = N'del "' + @NewTraceFile + N'.trc"'
	
		-- For SQL 2005, you will need to enable xp_cmdshell with sp_configure
		EXEC @RetVal = master.dbo.xp_cmdshell @sqltext  -- make sure it is deleted before we start a new trace

		EXEC @RetVal = dbo.sp_trace_create 
			@Hndl OUTPUT,	-- @traceid
			0,				-- @options
			@NewTraceFile,	-- @tracefile
			@maxfileSize = @BucketSize

		INSERT INTO [dbo].[Trace_QueueInfo]
		(
			[Handle], 
			[Dest_Server],
			[Dest_File],
			[Bucket]
		) 
		VALUES
		(
			@Hndl, 
			@Srvr,
			@Trace,
			@NewBucket
		)
	
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 1, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 3, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 6, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 7, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 8, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 10, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 11, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 13, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 14, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 15, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 16, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 17, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 18, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 35, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 10, @columnid = 40, @on = @OnBit
		
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 1, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 3, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 6, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 7, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 8, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 10, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 11, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 13, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 14, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 15, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 16, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 17, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 18, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 35, @on = @OnBit
		EXEC dbo.sp_trace_setevent @traceid = @Hndl, @eventid = 12, @columnid = 40, @on = @OnBit
	
		EXEC dbo.sp_trace_setstatus @traceid = @Hndl, @status = 1 -- starts the trace
		
		SET @sqltext = '[' + REPLACE(@Srvr, '$', '\') + '].[Admin].[dbo].[Trace_Swap_Summarize] ''' + @OldTraceFile + N'.trc'''

		EXEC (@sqltext)
	END
	ELSE
	BEGIN
		EXEC msdb.dbo.sp_start_job 'Trace_Start'
	END

END /* Procedure */
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



Tara Kizer
Go to Top of Page

acoleman616
Starting Member

8 Posts
Posted - 2006-10-20 : 14:37:38
That seems to be exactly what I need.

Is there any possible way you could point me in the right direction into how to integrate that into the code I originally posted?

Thanks for bearing with me. This has been a huge help.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-10-20 : 14:44:30
No I can't show you how to do that. I just don't have the time right now for that type of in-depth help. I am swamped at work! Trying to complete everything before I go on maternity leave...

Tara Kizer
Go to Top of Page

acoleman616
Starting Member

8 Posts
Posted - 2006-10-20 : 14:55:22
Okay, well hopefully I should be able to get it.

Thanks so so much for all the help!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site