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/
