ALTER PROCEDURE RunSQLTrace
@Folder VARCHAR(255),
@TraceRunTime INT
AS
BEGIN
/*
Follow steps here to customise the trace parameters:
https://www.mssqltips.com/sqlservertip/1715/scheduling-a-sql-server-profiler-trace/
*/

--Start a SQL trace, zip the trace file up, delete the trace file, clean up old zips
SET nocount ON

-- To change the traces duration, modify the following statement
DECLARE @StopTime DATETIME;

SET @StopTime = Dateadd(minute, @TraceRunTime, Getdate())

DECLARE @StartDatetime VARCHAR(13);

SET @StartDatetime = CONVERT(CHAR(8), Getdate(), 112) + '_' + Cast(Replace(CONVERT(VARCHAR(5), Getdate(), 108), ':', '') AS CHAR(4)) --['YYYYMMDD_HHMM']

DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @TraceFile NVARCHAR(100)
DECLARE @MaxFileSize BIGINT;

SET @MaxFileSize = 50 -- The maximum trace file in megabytes

DECLARE @cmd NVARCHAR(2000)
DECLARE @msg NVARCHAR(200)

IF RIGHT(@Folder, 1) <> '\'
BEGIN
SET @Folder = @Folder + '\'
END

-- Check if Folder exists
SET @cmd = 'dir ' + @Folder

EXEC @rc = master..XP_CMDSHELL @cmd, no_output

IF ( @rc != 0 )
BEGIN
SET @msg = 'The specified folder ' + @Folder + ' does not exist, Please specify an existing drive:\folder ' + Cast(@rc AS VARCHAR(10))
RAISERROR(@msg,10,1)
RETURN( -1 )
END

SET @TraceFile = @Folder + '\trace_' + @StartDatetime

EXEC @rc = SP_TRACE_CREATE @TraceID output, 0, @TraceFile, @MaxFileSize, @StopTime

IF ( @rc != 0 )
BEGIN
SET @msg = 'Error creating trace : ' + Cast(@rc AS VARCHAR(10))
RAISERROR(@msg,10,1)
RETURN( -1 )
END

--> Using your saved trace file, add the '-- SET the events' section below <
-- DECLARE @on BIT
SET @on = 1
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 6, @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, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on

--> Using your saved trace file, add the '-- SET the Filters' section below <
-- DECLARE @INTfilter INT

DECLARE @bigINTfilter BIGINT

EXEC SP_TRACE_SETFILTER @TraceID, 10, 0, 7, N'SQL Server Profiler - b9305c33-2ab3-4cd4-b716-ce90d00b54b1'
--> Customization is now completed <--
-----------------------------------------------------------------------------
-- This filter is added to exclude all profiler traces.
EXEC SP_TRACE_SETFILTER @TraceID, 10, 0, 7, N'SQL Profiler%'

-- SET the trace status to start
EXEC SP_TRACE_SETSTATUS @TraceID, 1 -- start trace

SELECT 'Trace id = ',@TraceID,'Path=',@Folder + '\'

SELECT 'To Stop this trace sooner, execute these two commands'
SELECT ' EXEC sp_trace_SETstatus @traceid = ',@TraceID, ', @status = 0; -- Stop/pause Trace'
SELECT ' EXEC sp_trace_SETstatus @traceid = ',@TraceID, ', @status = 2; -- Close trace and delete it from the server'

DECLARE @WaitForTime CHAR(8)
SET @WaitForTime = (SELECT '00:'+FORMAT(@TraceRunTime + 1, '00'))

WAITFOR DELAY @WaitForTime
--Zip the trace file
DECLARE @ZipString VARCHAR(1000)

SET @ZipString = (SELECT 'powershell.exe -nologo -noprofile -command "& {Compress-Archive -LiteralPath '+ @Folder + 'trace_' + @StartDatetime +'.trc -CompressionLevel Optimal -DestinationPath '+ @Folder + 'trace_' + @StartDatetime + '.zip }"')

EXEC XP_CMDSHELL @ZipString

--Delete the trc file
SET @cmd = (SELECT 'Del ' + @Folder + 'trace_' + @StartDatetime + '.trc /Q')

EXEC xp_cmdshell @cmd

--Delete files older than 60 days
DECLARE @pscmd varchar(1000)
DECLARE @targetpath varchar(8000)
DECLARE @olddays int
DECLARE @extension varchar(5)
DECLARE @cmdstring varchar(1000)

SET @targetpath = @Folder
SET @olddays = -60 --pass the days with negative values
SET @extension = 'zip'
SET @pscmd = '"& '+ 'Get-ChildItem ' + Quotename(@targetpath,'''') + ' | '+ 'where {$_.lastWriteTime -lt ((Get-Date).AddDays('+ CAST(@olddays as varchar) +')) -and ($_.Extension -match ' + QUOTENAME(@extension,'''')+ ') } | ' + 'Remove-Item -force " '
SET @cmdstring = ' ""powershell.exe" '+ @pscmd

exec master..xp_cmdshell @cmdstring