How To Automate SQL Server Trace

How to automate SQL Server Trace:

============================

• Start SQL Profiler and select File > New Trace. Specify the events, columns, and filters you want in your trace.
• Start the trace and then stop it.
• Export the definition. Click File > Export > Script Trace Definition > For SQL Server 2005. ...
• Save the trace file.
• Open the file and create a stored procedure.
• Add below lines to sp to create trace with date and time

 declare @TrcFileName nvarchar(300) 

set @TrcFileName = N'C:\STARTTrace'+CAST(DATEPART(d,GETDATE()) AS varchar(2))+
CAST(DATEPART(M,GETDATE()) AS varchar(2))+CAST(DATEPART(YYYY,GETDATE()) AS varchar(4))
+CAST(DATEPART(HH,GETDATE()) AS varchar(2))+CAST(DATEPART(MI,GETDATE()) AS varchar(2)) 

exec @rc = sp_trace_create @TraceID output, 0, @TrcFileName, @maxfilesize, NULL 

• To make SP to start when sql server restarts:

USE MASTER
GO
EXEC SP_PROCOPTION SPNAME, 'STARTUP', 'ON'
GO

• To check SP which are running at start up:

SELECT ROUTINE_NAME
FROM MASTER.INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1

--see if trace is running

select * from sys.traces
--Mark Trace Store Proc startup option True
EXEC sp_procoption 'StoreProcName', 'startup', 'true';
--Verify start option value is 1
USE MASTER
GO
SELECT VALUE, VALUE_IN_USE, DESCRIPTION
FROM SYS.CONFIGURATIONS
WHERE NAME = 'scan for startup procs'
GO

Msg 19069, Level 16, State 1, Procedure sp_trace_create, Line 1 The trace file name is not valid because it contains a rollover file number (NNN in C:\file_NNN) while the trace rollover option is enabled.

In this case remove the _ (underscore) at the end of the filename.

To rollover files manually use 2 as a parameter in the script.


Lot of thanks to sources :
http://www.techbrothersit.com/search?q=sql+server+profiler
https://dbamohsin.wordpress.com/tag/file-rollover/