Auto Generate an Hourly SQL Server Profiler Trace File

By:   |   Comments (38)   |   Related: > Profiler and Trace


Problem

While investigating performance issues as a DBA I'm often asked to profile a SQL system and provide the trace files to the requestor ASAP. The requestor could be a developer, tester or 3rd party vendor or the information could be useful to the DBA. I need a secure, consistent, reliable and quick way to deliver on the request without going through the hassle of starting a new Profiler session and configuring all the properties for a new trace. The trace files generated need to be clearly labeled and in a location where the requestor can access them. Also, I would like the option on some servers of having a profiler trace running 24x7 in the background. I could then access and read these trace files to investigate issues after the fact - much like the default trace that came out with SQL 2005.

Solution

I create a job that is scheduled to run a pre-configured server side Profiler trace. This job will execute every hour and on each execution will label and timestamp the current trace file and move it to a shared area for analysis as well as re-start a new server-side trace. The trace file will be labeled using convention SERVER_YYYYMMDDHHMMSS.trc. The server side trace should capture enough events and columns that will make it meaningful when reviews to see what SQL was happening on the server during the capture time.

Prerequisites

You will need the following in place on the server you intend profiling for this Profiler job to work:

  1. xp_cmdshell enabled
  2. forfiles.exe - This handy executable is included on Windows Server 2003 and 2008 but if you're still running Server 2000 then you'll need to download this and copy it to Windows\System32 directory
  3. A directory on the server to hold the current live trace file and the archived trace files i.e. C:\MSSQL\trace
  4. A subdirectory to hold the archived trace files i.e. C:\MSSQL\trace\archive

The Job

The job consists of 5 steps:

  1. delete old trace files - uses forfiles.exe to delete all trace files over a certain number of days
  2. stop current trace - stops the current trace and deletes the definition from the server
  3. rename trace and move to \archive folder - timestamps the trace file and moves it to the shared area
  4. start trace again - starts collecting trace data once again
  5. stop trace completely - this is a stand-alone step that can be run on its own and will stop the current trace and copy the final trace file to the \archive folder
trace job start sql job

 

The Script

USE [msdb]

GO

/****** Object: Job [_MSSQLTIPS Profiler Trace] Script Date: 09/11/2009 11:19:00 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 09/11/2009 11:19:00 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'_MSSQLTIPS Profiler Trace',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'Descrition: Trace to provide TSQL_Replay data for analysis

Author: Alan Cranfield - MSSQLTIPS.com',

@category_name=N'[Uncategorized (Local)]',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [delete old trace files] Script Date: 09/11/2009 11:19:00 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'delete old trace files',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=3,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'xp_cmdshell ''forfiles /P c:\mssql\trace\archive /M *.* /D -2 /C "cmd /C echo @FILE|DEL @FILE''',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [stop current trace] Script Date: 09/11/2009 11:19:00 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'stop current trace',

@step_id=2,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'-- get trace_id

declare @trace_id INT

select @trace_id = 0

select @trace_id = traceid

from ::fn_trace_getinfo (NULL)

where value = ''c:\MSSQL\trace\Profiler.trc''

IF @trace_id <> 0

BEGIN

-- Stops the specified trace.

EXEC master..sp_trace_setstatus @traceid = @trace_id ,@status = 0

-- Closes the specified trace and deletes its definition from the server.

EXEC master..sp_trace_setstatus @traceid = @trace_id ,@status = 2

END',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [rename trace file and move to \archive folder] Script Date: 09/11/2009 11:19:00 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'rename trace file and move to \archive folder',

@step_id=3,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'-- declare variables

declare @server varchar(30), @date char(14), @file char(100), @cmd varchar(250)

select @server = REPLACE(UPPER(@@servername),''\'',''_'')

select @date = replace(replace(replace(convert(char(19),getdate(),120),'':'',''''),''-'',''''),'' '','''')

select @file = @server+''_''+@date+''.trc''

-- change trace file name

select @cmd = ''RENAME c:\MSSQL\Trace\Profiler.trc ''+ @file

exec master..xp_cmdshell @cmd

-- move trace file to \archive

select @cmd = ''MOVE c:\MSSQL\Trace\''+@file+'' c:\MSSQL\Trace\archive''

exec master..xp_cmdshell @cmd',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [start trace again] Script Date: 09/11/2009 11:19:00 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'start trace again',

@step_id=4,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=3,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'-- this code generated by Profiler GUI

-- Create a Queue

declare @rc int ,@TraceID int ,@maxfilesize bigint

,@filecount bigint

set @maxfilesize = 5000 -- Mbytes

set @filecount = 10 -- max number of file rollovers

exec @rc = sp_trace_create @TraceID output, 2,

N''c:\MSSQL\trace\Profiler'',

@maxfilesize,

NULL

if (@rc != 0) goto error

-- Set the events

-- 1) Stored Procedure - RPC:Completed 2) TSQL - SQL:BatchCompleted

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 10, 15, @on

exec sp_trace_setevent @TraceID, 10, 16, @on

exec sp_trace_setevent @TraceID, 10, 1, @on

exec sp_trace_setevent @TraceID, 10, 17, @on

exec sp_trace_setevent @TraceID, 10, 10, @on

exec sp_trace_setevent @TraceID, 10, 18, @on

exec sp_trace_setevent @TraceID, 10, 11, @on

exec sp_trace_setevent @TraceID, 10, 12, @on

exec sp_trace_setevent @TraceID, 10, 13, @on

exec sp_trace_setevent @TraceID, 10, 14, @on

exec sp_trace_setevent @TraceID, 12, 15, @on

exec sp_trace_setevent @TraceID, 12, 16, @on

exec sp_trace_setevent @TraceID, 12, 1, @on

exec sp_trace_setevent @TraceID, 12, 17, @on

exec sp_trace_setevent @TraceID, 12, 10, @on

exec sp_trace_setevent @TraceID, 12, 14, @on

exec sp_trace_setevent @TraceID, 12, 18, @on

exec sp_trace_setevent @TraceID, 12, 11, @on

exec sp_trace_setevent @TraceID, 12, 12, @on

exec sp_trace_setevent @TraceID, 12, 13, @on

-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N''SQL Server Profiler%''

exec sp_trace_setstatus @TraceID, 1

select TraceID=@TraceID

goto finish

error:

select ErrorCode=@rc

finish:

go

',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [stop trace COMPLETELY] Script Date: 09/11/2009 11:19:00 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'stop trace COMPLETELY',

@step_id=5,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'-- get traceid

declare @trace_id INT

select @trace_id = 0

select @trace_id = traceid

from ::fn_trace_getinfo (NULL)

where value = ''c:\MSSQL\trace\Profiler.trc''

IF @trace_id <> 0

BEGIN

-- Stops the specified trace.

EXEC master..sp_trace_setstatus @traceid = @trace_id ,@status = 0

-- Closes the specified trace and deletes its definition from the server.

EXEC master..sp_trace_setstatus @traceid = @trace_id ,@status = 2

END

-- declare variables

declare @server varchar(12), @date char(14), @file char(100), @cmd varchar(250)

select @server = UPPER(@@servername)

select @date = replace(replace(replace(convert(char(19),getdate(),120),'':'',''''),''-'',''''),'' '','''')

select @file = @server+''_''+@date+''.trc''

-- change trace file name

select @cmd = ''RENAME c:\MSSQL\Trace\Profiler.trc ''+ @file

exec master..xp_cmdshell @cmd

-- move file to \archive

select @cmd = ''MOVE c:\MSSQL\Trace\''+@file+'' c:\MSSQL\Trace\archive''

exec master..xp_cmdshell @cmd',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'every 1 hours',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=4,

@freq_subday_interval=10,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20080815,

@active_end_date=99991231,

@active_start_time=0,

@active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

 

Practice

Once this job is enabled and successfully running every hour it will start collecting Profiler data and send the hourly profiler trace files to the \archive folder to be read and analyzed by those who need it.

sql server trace files

Locate the trace file for the hour that you are concerned with and open with Profiler to review the information provided:

sql server trace output
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Alan Cranfield Alan Cranfield is a versatile SQL Server DBA with over 10 years experience managing critical systems in large distributed environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, May 6, 2015 - 1:49:50 AM - Ricky Back To Top (37101)

 

Hi Sir Alan,

 

I'm new to SQL Server. I find this article to be very useful. I would like to implement this procedure in my current project. May you please send me the scripts for each step in the SQL Server Agent Job?

 

Thank you.

 

Best Regards,

Ricky

Monday, February 7, 2011 - 4:25:59 AM - Alan Back To Top (12854)

no. not possible right now using this procedure....

Thursday, January 27, 2011 - 11:46:39 AM - Fernando Jacinto Back To Top (12729)

and could be possible to configure for send all results to a table without have to send to trc files and insert into a table later?

Friday, March 26, 2010 - 8:22:25 PM - --cranfield Back To Top (5128)

thanks man.

Friday, March 26, 2010 - 4:31:33 PM - maneoman Back To Top (5127)

Hey Alan,

 As my kid would say.. This is off the hook.  Helped me out a great deal.  Thanks for posting it.

 maneoman

Monday, February 22, 2010 - 9:36:43 AM - --cranfield Back To Top (4931)

The Vista/2004 version of Forfiles.exe is not compatible with Windows 2000. You will need to download the Windows 2000 Resource kit version.

http://www.dynawell.com/download/reskit/microsoft/win2000/forfiles.zip

Sunday, February 21, 2010 - 10:08:43 PM - johnnyusa123 Back To Top (4928)

Alan,
I copied forfiles.exe to C:\WINNT\system32 directory  on Windows 2000 Server(SP4) and executed below script got error like to delete 2 days older files.

C:\> forfiles /P D:\Test\DeletOldFiles\ /M *.txt /D -2 /C "cmd /C echo @FILE|DEL @FILE"
ERROR: " The target system must be running Windows XP or above."

Note: Same script worked on Windows Server 2003 without any error but not in Windows 2000 Server.

How can i execute the forfiles.exe on Windows 20000 Server ? Is there any other way to run forfiles.exe ?

Thanks in Advance
John

Thursday, February 4, 2010 - 11:18:17 AM - --cranfield Back To Top (4848)

I have not seen this problem before - sorry.  If its an intermittent problem then it will be difficult to diagnose

Tuesday, February 2, 2010 - 7:01:42 PM - johnnyusa123 Back To Top (4840)

I am able to see the trace files using below command:

EXEC xp_cmdshell 'dir \\RemoteServername\d$\trace\Profiler.*'

The trace file was not opened and it's stopped.

Is it the problem with loading the trace files remote server to other server ?

Thanks in Advance
John.

 


 

 

 

Tuesday, January 26, 2010 - 5:58:57 AM - --cranfield Back To Top (4790)

I am not aware of any bug.  Perhaps put the following in your code as a debug to determine if the file does actually exist:

 EXEC xp_cmdshell 'dir \\RemoteServername\d$\trace\Profiler.*'

Perhaps if the file is still open then you will not be able to read from it so ensure that the trace has been stopped first.

Monday, January 25, 2010 - 6:35:32 PM - johnnyusa123 Back To Top (4787)

Hi Alan,

We have an issue while loading the .trc(trace) file data into SQL Server table using below query.
Insert into  TraceTableName  Select all_column_names  From  ::fn_trace_gettable('\\RemoteServername\d$\trace\Profiler.trc',default).

Error: The .trc files does not exist or there is no files exist.

But actually the .trc files exist given location in remote server (\\RemoteServername\d$\trace\). There is a shared drive on remote server and read write permision. This is happening only some times not always. How can we trouble shoot for this issue.Is there any bug on SQL Server 2000, this is happening only while loading .trce files from remote server only.

Thanks in advance
John.

 

 

 

Monday, January 25, 2010 - 10:43:37 AM - johnnyusa123 Back To Top (4781)

Thanks Alan for you reply.
I created  Clustered Index for [StartTime] ASC, and  non-clustered index for  [LoginName] ASC.

The above  query also works fine.

 

Thanks once again.
John.

 

Wednesday, January 13, 2010 - 11:25:44 AM - --cranfield Back To Top (4707)

John

The following clustered index would help with your query example. You could also create this as your primary key.

 CREATE CLUSTERED INDEX ix_tracetable_clustered ON [dbo].[tracetable]

(

[StartTime] ASC,

[LoginName] ASC

)

Obviously, loading data in the table would now be slowe as the index may need to be maintained...

Sunday, January 10, 2010 - 5:03:46 PM - johnnyusa123 Back To Top (4692)

Alan,
Thanks for your reply.

Initially i created table with all culumns in tracefile without indexes and primary key. The query most of the time running using where clause as start time and loginname.The query running slowly. For this using where clause starttime and loginname which coulumns you recommand for creating which indexes and primary key.

eg. select few clumns from tracetable where starttime='12-1-2009' and loginname ='testuser''
This query running slowly.The table does not have primary key and indexes.

Thanks
John

Friday, January 8, 2010 - 4:07:13 AM - --cranfield Back To Top (4685)

John

 Glad to hear you have it working.

Indexing choice will depend on the nature of the queries or reports you are running againt the data.

I would not advise the primary key to be on the textdata column. I would use a combination of a date field plus another field to be your primary key.

Not that having too many indexes may slow down the INSERT.

Provide an example of a query you are doing against the TraceTableName and we can design suitable indexes for that.

Are you having problems with the queries being slow? Any string functions that you use against the textdata will usually be slow and ineffeicent anyway.

 

Friday, January 8, 2010 - 12:26:15 AM - johnnyusa123 Back To Top (4684)

Hi Alan,

Thanks for your suggetion.But i did not find any option to load trace file data into sql table using DTS/SSIS. After long hours researching found that to load trace file data into remote server is as given

INSERT INTO TraceTableName  SELECT  all_column_names  FROM ::fn_trace_gettable('\\RemoteServername\d$\trace\Profiler.trc',default)
This works fine without any issues.

Questions for creating Indexes on the tracefile data table:
The data is keep on going and query running slow.Most of the time query will run ung where clause as 'starttime' and 'loginname.Already one primary key creaated on textdata column(This means clustered index created).Planning to create indexes on the table to improve the query performance.
1.Which index should we create ?
2.Which columns are good for creating index ?
3.How many idexes should we create for better performance

Thanks Alan in advance.

Thanks
John

 

 

 

 

Thursday, November 19, 2009 - 5:19:05 AM - --cranfield Back To Top (4462)

You can create an SSIS package to do this.  Add a Data Flow Task to your SSIS, confgure the SOURCE to be query above and the Destination to be the table you want to load the trace date to.

This is not the forum for a lesson in how to use SSIS. I recommend this tutorial:

http://www.mssqltips.com/tutorial.asp?id=200

Good luck

Tuesday, November 17, 2009 - 2:05:16 PM - johnnyusa123 Back To Top (4445)

Alan,

Can't we use DTS/SSIS packages to load the .trc(trace file) data  into table?
if yes, how can we use?

Thanks
John

 

Tuesday, November 17, 2009 - 11:01:48 AM - --cranfield Back To Top (4444)

the profiler.trc file is not in a delimited format. It is a binary file that can only be read using the ::fn_trace_gettable function.  So your source must be a database query:

 

SELECT * FROM ::fn_trace_gettable(@pathfile,default)

 

Sunday, November 15, 2009 - 10:20:02 PM - johnnyusa123 Back To Top (4430)

Hi Alan,
Thanks for your reply and the code working fine. We want to load the SQL Profiler Trace data(profiler.trc) into table using the SSIS/DTS. In DTS/SSIS which one should we select for source and what is the driver name should we select for profiler.trc files.Also what is the delimitor for .trc(trace files) files ?

Thanks in advance.

John :)

Monday, November 2, 2009 - 6:58:58 AM - --cranfield Back To Top (4352)

John

There are a number of ways you could do this. If, as you say, you are loading this trace data into another server then you have to decide whether to laod the data direct i.e over UNC \\servername\c$ or copy the trace files to your target server first.

Once you have the trace files on your target server then you could load the files using T-SQL or SSIS package. To keep track of which files you have already loaded you could copy the loaded files to \loaded directory after the load or keep track in a table.

Here is an example of loading mutiple trace files using T-SQL:

declare @Import table (f varchar(100))

declare @file varchar(100)

declare @pathfile varchar(200)

set nocount on

insert @Import

exec master..xp_cmdshell 'dir /B c:\mssql\trace\archive\*.trc'

select top 1 @file = f from @Import

select @pathfile = 'c:\MSSQL\Trace\archive\'+ @file

while (select top 1 * from @Import where f like '%.trc') LIKE '%.trc'

begin

INSERT INTO TraceTable SELECT * FROM ::fn_trace_gettable(@pathfile,default)

delete from @Import where f = @file

select top 1 @file = f from @Import

select @pathfile = 'c:\MSSQL\Trace\archive\'+ @file

end

Friday, October 30, 2009 - 3:54:01 PM - johnnyusa123 Back To Top (4344)

Alan,

This will work,but if data is more then it takes more time for laoding and trace will not start immediately. That time tracing will be missed.
I am thinking best option is to create new another job for loading the data from all \archive folder files  to table. 

--" Also note there will be a gap in your tracing from when you stop the trace, load the trace data and then start the trace again. You could get around this by restarting the trace before you load the trace data into the table. "

We are loading data into different server. Please suggest me.

Thanks
John

 

 

 

Friday, October 30, 2009 - 2:05:51 PM - --cranfield Back To Top (4343)

If you wanted to insert the data into a table on the same instance you are monitioring then you could do it this way by editing step no 3 as follows:

-- declare variables
declare @server varchar(30), @date char(14), @file varchar(100), @cmd varchar(250)
select @server = REPLACE(UPPER(@@servername),'\','_')
select @date = replace(replace(replace(convert(char(19),getdate(),120),':',''),'-',''),' ','')
select @file = @server+'_'+@date+'.trc'
-- change trace file name
select @cmd = 'RENAME c:\MSSQL\Trace\Profiler.trc '+ @file
exec master..xp_cmdshell @cmd
-- move trace file to \archive
select @cmd = 'MOVE c:\MSSQL\Trace\'+@file+' c:\MSSQL\Trace\archive'
exec master..xp_cmdshell @cmd

-- load trace file into table
select @file = 'c:\MSSQL\Trace\archive\'+ @file

INSERT INTO TraceTable SELECT * FROM ::fn_trace_gettable(@file,default)

 

Please note you will need to have created the TraceTable in master database before hand.  You can do this by using a select into as follows:

SELECT * into TraceTable

FROM ::fn_trace_gettable('C:\mssql\trace\Profiler.trc',default)

where 1 = 0

 

If your server is very bust its not recommended to load trace data into same database as you are profiling but if the servers is not too busy and the trace data is required for other reasons then this will work fine.

Also note there will be a gap in your tracing from when you stop the trace, load the trace data and then start the trace again. You could get around this by restarting the trace before you load the trace data into the table.

 Hope this helps

Friday, October 30, 2009 - 1:12:24 PM - johnnyusa123 Back To Top (4342)

Hi Alan,

The trace file data should be insert into table every 10 minutes with in that job. How can i do that ?
INSERT INTO TraceTable SELECT * FROM ::fn_trace_gettable('C:\mssql\trace\Profiler.trc',default) 

Here i want insert dynamically every 10 minutes in that job.
How can i pass the path here for dynamically: 'C:\mssql\trace\Profiler.trc'

Thanks in advance.

John

 

 

Tuesday, October 27, 2009 - 10:20:00 PM - --cranfield Back To Top (4318)

glad to hear its working for you now.

Tuesday, October 27, 2009 - 8:54:10 PM - johnnyusa123 Back To Top (4317)

Alan,
The above issue fixed for sql server 2000.Just removed  " .trc"  in step 2 .Then moved the file every 10 minutes to archive folder.

Thanks Alan once again.

John

Monday, October 26, 2009 - 7:55:08 AM - --cranfield Back To Top (4301)

or better still change the above line of code to this:

 where value IN ('c:\MSSQL\trace\Profiler.trc','c:\MSSQL\trace\Profiler')

this will now work for both SQL2000/2005

Monday, October 26, 2009 - 7:38:37 AM - --cranfield Back To Top (4300)

Hi John

Thanks for pointing this out.  I've discovered the problem. There is a small difference in the way that the ::fn_trace_getinfo function returns info for SQL2000:

 To fix this - In [step 2 stop current trace], of the SQL job find the code line:

where value = 'c:\MSSQL\trace\Profiler.trc'

-- change to the below i.e. remove the ".trc" from the filename:

where value = 'c:\MSSQL\trace\Profiler'

 

This should fix it.

Please let me know.

 

cheers

Alan

Sunday, October 25, 2009 - 10:42:12 PM - johnnyusa123 Back To Top (4297)

Hi,

This helped me a lot. Wasted lot of time running script on enterprise manager. It worked through SSMS for 2000 version.
I have an issue like... Running this script on SSMS 2005 working fine without any issue and moving trace to archive Folder.

But in Enterprise Manager SQL Server 2000 not moving file to archive folder.Trace running and storing single trace file and not moving every 10 minutes.

I used same script which works in 2005 and not working on 2000.I have 2005 instance and 2000 instance on same host.
Any bug SQl server 2000 version. 

Thanks for advace  help..

John 

 

Thursday, September 24, 2009 - 10:49:24 AM - pnewhart Back To Top (4088)

I was getting the error when I used Query Analyzer.  However, I was able to paste in to SQL Mgmt Studio and execute the script without a problem.  Not sure what the diff would be.

Thanks. 

 

Thursday, September 24, 2009 - 8:00:00 AM - --cranfield Back To Top (4086)

I just tested again on SQL2000 and SQL2005 and the pasted script compiles just fine.     I've coped it again here for you:

 

USE [msdb]

GO

/****** Object: Job [_MSSQLTIPS Profiler Trace] Script Date: 09/24/2009 15:42:46 ******/

BEGIN TRANSACTION DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 09/24/2009 15:42:46 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'_MSSQLTIPS Profiler Trace',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'Descrition: Trace to provide TSQL_Replay data for analysis

Author: Alan Cranfield - MSSQLTIPS.com',

@category_name=N'[Uncategorized (Local)]',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [delete old trace files] Script Date: 09/24/2009 15:42:46 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'delete old trace files',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=3,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'xp_cmdshell ''forfiles /P c:\mssql\trace\archive /M *.* /D -2 /C "cmd /C echo @FILE|DEL @FILE''', @database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [stop current trace] Script Date: 09/24/2009 15:42:46 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'stop current trace',

@step_id=2,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'-- get trace_id

declare @trace_id INT

select @trace_id = 0

select @trace_id = traceid

from ::fn_trace_getinfo (NULL)

where value = ''c:\MSSQL\trace\Profiler.trc''

IF @trace_id <> 0

BEGIN

-- Stops the specified trace.

EXEC master..sp_trace_setstatus @traceid = @trace_id ,@status = 0

-- Closes the specified trace and deletes its definition from the server.

EXEC master..sp_trace_setstatus @traceid = @trace_id ,@status = 2

END', @database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [rename trace file and move to \archive folder] Script Date: 09/24/2009 15:42:46 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'rename trace file and move to \archive folder',

@step_id=3,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'-- declare variables

declare @server varchar(30), @date char(14), @file char(100), @cmd varchar(250)

select @server = REPLACE(UPPER(@@servername),''\'',''_'')

select @date = replace(replace(replace(convert(char(19),getdate(),120),'':'',''''),''-'',''''),'' '','''')

select @file = @server+''_''+@date+''.trc''

-- change trace file name

select @cmd = ''RENAME c:\MSSQL\Trace\Profiler.trc ''+ @file

exec master..xp_cmdshell @cmd

-- move trace file to \archive

select @cmd = ''MOVE c:\MSSQL\Trace\''+@file+'' c:\MSSQL\Trace\archive''

exec master..xp_cmdshell @cmd', @database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [start trace again] Script Date: 09/24/2009 15:42:47 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'start trace again',

@step_id=4,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=3,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'-- this code generated by Profiler GUI

-- Create a Queue

declare @rc int ,@TraceID int ,@maxfilesize bigint

,@filecount bigint

set @maxfilesize = 5000 -- Mbytes

set @filecount = 10 -- max number of file rollovers

exec @rc = sp_trace_create @TraceID output, 2,

N''c:\MSSQL\trace\Profiler'',

@maxfilesize,

NULL

if (@rc != 0) goto error

-- Set the events

-- 1) Stored Procedure - RPC:Completed 2) TSQL - SQL:BatchCompleted

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 10, 15, @on

exec sp_trace_setevent @TraceID, 10, 16, @on

exec sp_trace_setevent @TraceID, 10, 1, @on

exec sp_trace_setevent @TraceID, 10, 17, @on

exec sp_trace_setevent @TraceID, 10, 10, @on

exec sp_trace_setevent @TraceID, 10, 18, @on

exec sp_trace_setevent @TraceID, 10, 11, @on

exec sp_trace_setevent @TraceID, 10, 12, @on

exec sp_trace_setevent @TraceID, 10, 13, @on

exec sp_trace_setevent @TraceID, 10, 14, @on

exec sp_trace_setevent @TraceID, 12, 15, @on

exec sp_trace_setevent @TraceID, 12, 16, @on

exec sp_trace_setevent @TraceID, 12, 1, @on

exec sp_trace_setevent @TraceID, 12, 17, @on

exec sp_trace_setevent @TraceID, 12, 10, @on

exec sp_trace_setevent @TraceID, 12, 14, @on

exec sp_trace_setevent @TraceID, 12, 18, @on

exec sp_trace_setevent @TraceID, 12, 11, @on

exec sp_trace_setevent @TraceID, 12, 12, @on

exec sp_trace_setevent @TraceID, 12, 13, @on

-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N''SQL Server Profiler%''

exec sp_trace_setstatus @TraceID, 1

select TraceID=@TraceID

goto finish

error:

select ErrorCode=@rc

finish:

go

', @database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [stop trace COMPLETELY] Script Date: 09/24/2009 15:42:47 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'stop trace COMPLETELY',

@step_id=5,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'-- get traceid

declare @trace_id INT

select @trace_id = 0

select @trace_id = traceid

from ::fn_trace_getinfo (NULL)

where value = ''c:\MSSQL\trace\Profiler.trc''

IF @trace_id <> 0

BEGIN

-- Stops the specified trace.

EXEC master..sp_trace_setstatus @traceid = @trace_id ,@status = 0

-- Closes the specified trace and deletes its definition from the server.

EXEC master..sp_trace_setstatus @traceid = @trace_id ,@status = 2

END

-- declare variables

declare @server varchar(12), @date char(14), @file char(100), @cmd varchar(250)

select @server = UPPER(@@servername)

select @date = replace(replace(replace(convert(char(19),getdate(),120),'':'',''''),''-'',''''),'' '','''')

select @file = @server+''_''+@date+''.trc''

-- change trace file name

select @cmd = ''RENAME c:\MSSQL\Trace\Profiler.trc ''+ @file

exec master..xp_cmdshell @cmd

-- move file to \archive

select @cmd = ''MOVE c:\MSSQL\Trace\''+@file+'' c:\MSSQL\Trace\archive''

exec master..xp_cmdshell @cmd', @database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'every 1 hours',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=4,

@freq_subday_interval=10,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20080815,

@active_end_date=99991231,

@active_start_time=0,

@active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

Thursday, September 24, 2009 - 7:28:45 AM - pnewhart Back To Top (4085)

Is anyone else getting the following syntax error after pasting the script.....

Server: Msg 105, Level 15, State 1, Line 205
Unclosed quotation mark after the character string '-- this code generated by Profiler GUI

Wednesday, September 23, 2009 - 4:47:15 AM - --cranfield Back To Top (4081)

Jack

 I will keep an eye on that blog of yours. I see a Powershell book across the office on a colleagues desk - must borrow it.

 

thanks

Alan

Tuesday, September 22, 2009 - 5:05:20 AM - unclebiguns Back To Top (4072)
Alan, I just recently created a Powershell script with some help from a friend, that archives the default trace files. You don't have the option to stop and restart the default trace, so I my script returns the current file name from sys.traces and then excludes that file from results. Sort of like: For each trace file If trace file name = current trace file archive file end for That's not how it looks in Powershell, but that is the general idea. I will be blogging about it this week (www.wiseman-wiseguy.blogspot.com) and the blog will include the Powershell script.

Tuesday, September 22, 2009 - 12:26:03 AM - --cranfield Back To Top (4070)

thanks for the comments, Jack.

1. I have to confess to being a complete amateur with Powershell but do need to get up to speed soon especially with SQL2008 becoming more mainstream. Anything that reduces a DBAs reliance on xp_cmdshell has to be taken on.

2. I'm not sure what you mean by using Powershell to "skip" the active file when you stop and re-start the trace.  The active trace has to be stopped in order to rename it and move it to the \archive area.


Monday, September 21, 2009 - 8:06:00 AM - unclebiguns Back To Top (4065)
Good tip and interesting method. I have one other comment. If you use SSIS or Powershell to archive the files you would gain 2 things: 1. You would reduce the surface area of your sql server instance because you would not need xp_cmdshell enabled. This assumes you only use it for this process. OF course anything you need xp_cmdshell for could be done using Powershell. 2. You would not have to stop and restart the trace as you could "skip" the active file.

Monday, September 21, 2009 - 7:20:03 AM - --cranfield Back To Top (4064)

Hi Darshan

Thank you for reading my tip.  Please note we are actually running a "Server Side trace" on the server, not the Profiler GUI. The Profiler GUI can be used to read the trace files that are generated.  Sorry for any confusion. You are correct in that it is best practice to run the Profiler GUI on a different machine to the production server.

See this tip from Greg about explaining Server Side traces - http://www.mssqltips.com/tip.asp?tip=1035.

Server-side traces can be run 24x7 on production servers. In fact there is already the default trace which runs on all SQL2005 servers and this already runs 24x7 - http://www.mssqltips.com/tip.asp?tip=1111.

SQL 2008 provides a less intrusive form of tracing known as Auditing. Read all about that here - http://thomaslarock.com/2009/03/implementing-sql-2008-sql-server-audit/.

Alan Cranfield

Monday, September 21, 2009 - 5:50:26 AM - Darshan Shah Back To Top (4063)

I beleave that this process is too much expensive to database server.we can not run profiler on production server for 24x7.We have to run SQL profiler on other server. and run this job on other server.

Thanks

Darshan Shah

 

 

 














get free sql tips
agree to terms