Have you ever wanted to start a SQL trace (server-side trace!) from SQL agent and let it stop after X minutes and write the TRC to a location? Well this is how you would do it:

 ALTER PROCEDURE [dbo].[ScriptTrace] @RunTime INT,

 @FileName NVARCHAR(100) 

AS 

SET NOCOUNT ON 

DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT -- Specify Max File Size
DECLARE @Now DATETIME
DECLARE @StopTime DATETIME
DECLARE @FileStamp NVARCHAR(25)
DECLARE @COMMAND NVARCHAR(125) 

SET @Now = GETDATE() 

SET @StopTime = DATEADD (Mi , @RunTime , @Now ) 

SET @FileName = @FileName+'_' SET @FileStamp = CAST(DATEPART(YEAR, GETDATE()) AS NVARCHAR) + CAST(DATEPART(MONTH, GETDATE()) AS NVARCHAR) + CAST(DATEPART(DAY, GETDATE()) AS NVARCHAR) + CAST(DATEPART(HOUR, GETDATE()) AS NVARCHAR) + CAST(DATEPART(MINUTE, GETDATE()) AS NVARCHAR) 

SET @FileName = @FileName + @FileStamp SET @maxfilesize = 18000 

EXEC @rc = sp_trace_create @TraceID OUTPUT, 0,
@FQFileName, @maxfilesize, @StopTime 

IF (@rc != 0) GOTO error 

SET @COMMAND = 'SQL Trace Log @' + @FQFileName + ' activated for ' + CAST(@RunTime AS VARCHAR) + ' minute(s)' PRINT @COMMAND 

--Set the events for example

DECLARE @on BIT SET @on = 1 

EXEC sp_trace_setevent @TraceID, 14, 1, @on
EXEC sp_trace_setevent @TraceID, 14, 9, @on
EXEC sp_trace_setevent @TraceID, 14, 6, @on
EXEC sp_trace_setevent @TraceID, 14, 10, @on
EXEC sp_trace_setevent @TraceID, 14, 14, @on
EXEC sp_trace_setevent @TraceID, 14, 11, @on
EXEC sp_trace_setevent @TraceID, 15, 10, @on
EXEC sp_trace_setevent @TraceID, 15, 14, @on
EXEC sp_trace_setevent @TraceID, 15, 18, @on
EXEC sp_trace_setevent @TraceID, 15, 11, @on
EXEC sp_trace_setevent @TraceID, 15, 12, @on 

--Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT 

-- filter 1 include databaseId 5
EXEC sp_trace_setfilter @TraceID, 3, 1, 0, 5 

-- filter 2
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler' EXEC sp_trace_setstatus @TraceID, 1 

SELECT TraceID=@TraceID
GOTO finish ERROR:
SELECT ErrorCode=@rc FINISH:

--Now call it
EXEC dbo.ScriptTrace '1','G:\Logs\Trace'

output example: SQL Trace Log @G:\Logs\Trace_20153101419 activated for 1 minute(s)