Setup the SS replay trace on the Source SQL Server Server. I have create the SST for easy use
/****************************************************/ /* Created by: SQL Server 2012 Profiler */ /* Modified by : Mohammad Sufian */ /* Date: 05/15/2015 09:48:18 AM */ /****************************************************/ declare @svrName varchar(255) declare @sql nvarchar(4000) declare @maxfreedrive nvarchar(5) declare @chkdirectory as nvarchar(4000) declare @folder_exists as int declare @databasefilter nvarchar(500) declare @Split char(1) declare @start int=1 declare @end int declare @tsql nvarchar(4000) set @Split = ',' declare @tracefilter table (id int identity,traceid int,val1 int,val2 int,val3 int , filter nvarchar(256)) --by default it will take the current server name, we can the set the server name as well set @svrName = @@SERVERNAME --set the database filter set @databasefilter='Database1,Database2,...,...,...' --metion the database name to filter the trace set @sql = 'powershell "Get-WmiObject Win32_Volume | Where-Object {$_.blocksize -gt 0 } | SELECT caption,capacity,freespace | foreach{$_.caption+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"' --set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"' --creating a temporary table CREATE TABLE #output (line varchar(255)) --inserting disk name, total space and free space value in to temporary table insert #output EXEC xp_cmdshell @sql --script to retrieve the values in GB from PS Script output select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1, (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)' ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)' into #temp001 from #output where line like '[A-Z][:]%' order by drivename select top 1 @maxfreedrive=drivename from #temp001 order by [freespace(GB)] desc print @maxfreedrive set @chkdirectory = ''''+@maxfreedrive+'dreplaytrace'+'''' print @chkdirectory declare @file_results table (file_exists int,file_is_a_directory int,parent_directory_exists int) set @sql='master.dbo.xp_fileexist '+@chkdirectory insert into @file_results (file_exists, file_is_a_directory, parent_directory_exists) exec sp_executesql @sql select @folder_exists = file_is_a_directory from @file_results --script to create directory if @folder_exists = 0 begin print 'Directory does not exists, creating new one' set @sql='master.dbo.xp_create_subdir '+@chkdirectory exec sp_executesql @sql print @chkdirectory + 'created on ' + @@servername end drop table #output drop table #temp001 -- Create a Queue declare @rc int declare @TraceID int declare @maxfilesize bigint declare @tracefilepath nvarchar(4000) set @maxfilesize = 1 -- Please replace the text InsertFileNameHere, with an appropriate -- filename prefixed by a path, e.g., c:MyFolderMyTrace. The .trc extension -- will be appended to the filename automatically. If you are writing from -- remote server to local drive, please use UNC path and make sure server has -- write access to your network share set @tracefilepath=@maxfreedrive+'dreplaytrace'+'dreplay_'+replace(convert(varchar(max),@@SERVERNAME),'','_')+'_'+convert(varchar(2),day(getdate()))+convert(varchar(2),month(getdate()))+convert(varchar(4),Year(getdate())) +convert(varchar(2),DATEPART(HOUR, GETDATE()))+convert(varchar(2),DATEPART(MINUTE, GETDATE()))+convert(varchar(2),DATEPART(SECOND, GETDATE())) print @tracefilepath exec @rc = sp_trace_create @TraceID output, 2,@tracefilepath, @maxfilesize, NULL if (@rc != 0) goto error -- Client side File and Table cannot be scripted -- Set the events declare @on bit set @on = 1 exec sp_trace_setevent @TraceID, 78, 3, @on exec sp_trace_setevent @TraceID, 78, 11, @on exec sp_trace_setevent @TraceID, 78, 12, @on exec sp_trace_setevent @TraceID, 78, 6, @on exec sp_trace_setevent @TraceID, 78, 7, @on exec sp_trace_setevent @TraceID, 78, 8, @on exec sp_trace_setevent @TraceID, 78, 9, @on exec sp_trace_setevent @TraceID, 78, 10, @on exec sp_trace_setevent @TraceID, 78, 14, @on exec sp_trace_setevent @TraceID, 78, 26, @on exec sp_trace_setevent @TraceID, 78, 35, @on exec sp_trace_setevent @TraceID, 78, 51, @on exec sp_trace_setevent @TraceID, 78, 60, @on exec sp_trace_setevent @TraceID, 74, 3, @on exec sp_trace_setevent @TraceID, 74, 11, @on exec sp_trace_setevent @TraceID, 74, 12, @on exec sp_trace_setevent @TraceID, 74, 6, @on exec sp_trace_setevent @TraceID, 74, 7, @on exec sp_trace_setevent @TraceID, 74, 8, @on exec sp_trace_setevent @TraceID, 74, 9, @on exec sp_trace_setevent @TraceID, 74, 10, @on exec sp_trace_setevent @TraceID, 74, 14, @on exec sp_trace_setevent @TraceID, 74, 26, @on exec sp_trace_setevent @TraceID, 74, 35, @on exec sp_trace_setevent @TraceID, 74, 51, @on exec sp_trace_setevent @TraceID, 74, 60, @on exec sp_trace_setevent @TraceID, 53, 3, @on exec sp_trace_setevent @TraceID, 53, 11, @on exec sp_trace_setevent @TraceID, 53, 12, @on exec sp_trace_setevent @TraceID, 53, 6, @on exec sp_trace_setevent @TraceID, 53, 7, @on exec sp_trace_setevent @TraceID, 53, 8, @on exec sp_trace_setevent @TraceID, 53, 9, @on exec sp_trace_setevent @TraceID, 53, 10, @on exec sp_trace_setevent @TraceID, 53, 14, @on exec sp_trace_setevent @TraceID, 53, 26, @on exec sp_trace_setevent @TraceID, 53, 35, @on exec sp_trace_setevent @TraceID, 53, 51, @on exec sp_trace_setevent @TraceID, 53, 60, @on exec sp_trace_setevent @TraceID, 70, 3, @on exec sp_trace_setevent @TraceID, 70, 11, @on exec sp_trace_setevent @TraceID, 70, 12, @on exec sp_trace_setevent @TraceID, 70, 6, @on exec sp_trace_setevent @TraceID, 70, 7, @on exec sp_trace_setevent @TraceID, 70, 8, @on exec sp_trace_setevent @TraceID, 70, 9, @on exec sp_trace_setevent @TraceID, 70, 10, @on exec sp_trace_setevent @TraceID, 70, 14, @on exec sp_trace_setevent @TraceID, 70, 26, @on exec sp_trace_setevent @TraceID, 70, 35, @on exec sp_trace_setevent @TraceID, 70, 51, @on exec sp_trace_setevent @TraceID, 70, 60, @on exec sp_trace_setevent @TraceID, 77, 3, @on exec sp_trace_setevent @TraceID, 77, 11, @on exec sp_trace_setevent @TraceID, 77, 12, @on exec sp_trace_setevent @TraceID, 77, 6, @on exec sp_trace_setevent @TraceID, 77, 7, @on exec sp_trace_setevent @TraceID, 77, 8, @on exec sp_trace_setevent @TraceID, 77, 9, @on exec sp_trace_setevent @TraceID, 77, 10, @on exec sp_trace_setevent @TraceID, 77, 14, @on exec sp_trace_setevent @TraceID, 77, 26, @on exec sp_trace_setevent @TraceID, 77, 35, @on exec sp_trace_setevent @TraceID, 77, 51, @on exec sp_trace_setevent @TraceID, 77, 60, @on exec sp_trace_setevent @TraceID, 14, 1, @on exec sp_trace_setevent @TraceID, 14, 9, @on exec sp_trace_setevent @TraceID, 14, 2, @on exec sp_trace_setevent @TraceID, 14, 10, @on exec sp_trace_setevent @TraceID, 14, 3, @on exec sp_trace_setevent @TraceID, 14, 11, @on exec sp_trace_setevent @TraceID, 14, 6, @on exec sp_trace_setevent @TraceID, 14, 7, @on exec sp_trace_setevent @TraceID, 14, 8, @on exec sp_trace_setevent @TraceID, 14, 12, @on exec sp_trace_setevent @TraceID, 14, 14, @on exec sp_trace_setevent @TraceID, 14, 26, @on exec sp_trace_setevent @TraceID, 14, 35, @on exec sp_trace_setevent @TraceID, 14, 51, @on exec sp_trace_setevent @TraceID, 14, 60, @on exec sp_trace_setevent @TraceID, 15, 3, @on exec sp_trace_setevent @TraceID, 15, 11, @on exec sp_trace_setevent @TraceID, 15, 6, @on exec sp_trace_setevent @TraceID, 15, 7, @on exec sp_trace_setevent @TraceID, 15, 8, @on exec sp_trace_setevent @TraceID, 15, 9, @on exec sp_trace_setevent @TraceID, 15, 10, @on exec sp_trace_setevent @TraceID, 15, 12, @on exec sp_trace_setevent @TraceID, 15, 14, @on exec sp_trace_setevent @TraceID, 15, 15, @on exec sp_trace_setevent @TraceID, 15, 26, @on exec sp_trace_setevent @TraceID, 15, 35, @on exec sp_trace_setevent @TraceID, 15, 51, @on exec sp_trace_setevent @TraceID, 15, 60, @on exec sp_trace_setevent @TraceID, 17, 1, @on exec sp_trace_setevent @TraceID, 17, 9, @on exec sp_trace_setevent @TraceID, 17, 2, @on exec sp_trace_setevent @TraceID, 17, 10, @on exec sp_trace_setevent @TraceID, 17, 3, @on exec sp_trace_setevent @TraceID, 17, 11, @on exec sp_trace_setevent @TraceID, 17, 6, @on exec sp_trace_setevent @TraceID, 17, 7, @on exec sp_trace_setevent @TraceID, 17, 8, @on exec sp_trace_setevent @TraceID, 17, 12, @on exec sp_trace_setevent @TraceID, 17, 14, @on exec sp_trace_setevent @TraceID, 17, 26, @on exec sp_trace_setevent @TraceID, 17, 35, @on exec sp_trace_setevent @TraceID, 17, 51, @on exec sp_trace_setevent @TraceID, 17, 60, @on exec sp_trace_setevent @TraceID, 100, 1, @on exec sp_trace_setevent @TraceID, 100, 9, @on exec sp_trace_setevent @TraceID, 100, 3, @on exec sp_trace_setevent @TraceID, 100, 11, @on exec sp_trace_setevent @TraceID, 100, 6, @on exec sp_trace_setevent @TraceID, 100, 7, @on exec sp_trace_setevent @TraceID, 100, 8, @on exec sp_trace_setevent @TraceID, 100, 10, @on exec sp_trace_setevent @TraceID, 100, 12, @on exec sp_trace_setevent @TraceID, 100, 14, @on exec sp_trace_setevent @TraceID, 100, 26, @on exec sp_trace_setevent @TraceID, 100, 35, @on exec sp_trace_setevent @TraceID, 100, 51, @on exec sp_trace_setevent @TraceID, 100, 60, @on 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, 3, @on exec sp_trace_setevent @TraceID, 10, 6, @on exec sp_trace_setevent @TraceID, 10, 7, @on exec sp_trace_setevent @TraceID, 10, 8, @on exec sp_trace_setevent @TraceID, 10, 11, @on exec sp_trace_setevent @TraceID, 10, 12, @on exec sp_trace_setevent @TraceID, 10, 14, @on exec sp_trace_setevent @TraceID, 10, 15, @on exec sp_trace_setevent @TraceID, 10, 26, @on exec sp_trace_setevent @TraceID, 10, 31, @on exec sp_trace_setevent @TraceID, 10, 35, @on exec sp_trace_setevent @TraceID, 10, 51, @on exec sp_trace_setevent @TraceID, 10, 60, @on exec sp_trace_setevent @TraceID, 11, 9, @on exec sp_trace_setevent @TraceID, 11, 2, @on exec sp_trace_setevent @TraceID, 11, 10, @on exec sp_trace_setevent @TraceID, 11, 3, @on exec sp_trace_setevent @TraceID, 11, 6, @on exec sp_trace_setevent @TraceID, 11, 7, @on exec sp_trace_setevent @TraceID, 11, 8, @on exec sp_trace_setevent @TraceID, 11, 11, @on exec sp_trace_setevent @TraceID, 11, 12, @on exec sp_trace_setevent @TraceID, 11, 14, @on exec sp_trace_setevent @TraceID, 11, 26, @on exec sp_trace_setevent @TraceID, 11, 35, @on exec sp_trace_setevent @TraceID, 11, 51, @on exec sp_trace_setevent @TraceID, 11, 60, @on exec sp_trace_setevent @TraceID, 72, 3, @on exec sp_trace_setevent @TraceID, 72, 11, @on exec sp_trace_setevent @TraceID, 72, 12, @on exec sp_trace_setevent @TraceID, 72, 6, @on exec sp_trace_setevent @TraceID, 72, 7, @on exec sp_trace_setevent @TraceID, 72, 8, @on exec sp_trace_setevent @TraceID, 72, 9, @on exec sp_trace_setevent @TraceID, 72, 10, @on exec sp_trace_setevent @TraceID, 72, 14, @on exec sp_trace_setevent @TraceID, 72, 26, @on exec sp_trace_setevent @TraceID, 72, 35, @on exec sp_trace_setevent @TraceID, 72, 51, @on exec sp_trace_setevent @TraceID, 72, 60, @on exec sp_trace_setevent @TraceID, 71, 3, @on exec sp_trace_setevent @TraceID, 71, 11, @on exec sp_trace_setevent @TraceID, 71, 12, @on exec sp_trace_setevent @TraceID, 71, 6, @on exec sp_trace_setevent @TraceID, 71, 7, @on exec sp_trace_setevent @TraceID, 71, 8, @on exec sp_trace_setevent @TraceID, 71, 9, @on exec sp_trace_setevent @TraceID, 71, 10, @on exec sp_trace_setevent @TraceID, 71, 14, @on exec sp_trace_setevent @TraceID, 71, 26, @on exec sp_trace_setevent @TraceID, 71, 35, @on exec sp_trace_setevent @TraceID, 71, 51, @on exec sp_trace_setevent @TraceID, 71, 60, @on exec sp_trace_setevent @TraceID, 12, 1, @on exec sp_trace_setevent @TraceID, 12, 9, @on exec sp_trace_setevent @TraceID, 12, 3, @on exec sp_trace_setevent @TraceID, 12, 11, @on exec sp_trace_setevent @TraceID, 12, 6, @on exec sp_trace_setevent @TraceID, 12, 7, @on exec sp_trace_setevent @TraceID, 12, 8, @on exec sp_trace_setevent @TraceID, 12, 10, @on exec sp_trace_setevent @TraceID, 12, 12, @on exec sp_trace_setevent @TraceID, 12, 14, @on exec sp_trace_setevent @TraceID, 12, 15, @on exec sp_trace_setevent @TraceID, 12, 26, @on exec sp_trace_setevent @TraceID, 12, 31, @on exec sp_trace_setevent @TraceID, 12, 35, @on exec sp_trace_setevent @TraceID, 12, 51, @on exec sp_trace_setevent @TraceID, 12, 60, @on exec sp_trace_setevent @TraceID, 13, 1, @on exec sp_trace_setevent @TraceID, 13, 9, @on exec sp_trace_setevent @TraceID, 13, 3, @on exec sp_trace_setevent @TraceID, 13, 11, @on exec sp_trace_setevent @TraceID, 13, 6, @on exec sp_trace_setevent @TraceID, 13, 7, @on exec sp_trace_setevent @TraceID, 13, 8, @on exec sp_trace_setevent @TraceID, 13, 10, @on exec sp_trace_setevent @TraceID, 13, 12, @on exec sp_trace_setevent @TraceID, 13, 14, @on exec sp_trace_setevent @TraceID, 13, 26, @on exec sp_trace_setevent @TraceID, 13, 35, @on exec sp_trace_setevent @TraceID, 13, 51, @on exec sp_trace_setevent @TraceID, 13, 60, @on -- declare @filter nvarchar(4000)=@databasefilter declare @S varchar(max) --declare @Split char(1) declare @X xml set @start =1 set @end=0 set @Split = ',' --declare @tracefilter table (id int identity,traceid int,val1 int,val2 int,val3 int , filter nvarchar(256)) SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@filter,@Split,'</s><s>') + '</s></root>') insert into @tracefilter(traceid,val1,val2,val3,filter) SELECT @TraceID,11,1,6,T.c.value('.','varchar(20)') FROM @X.nodes('/root/s') T(c) SET @end=@@rowcount while @start <=@end begin select @tsql='exec sp_trace_setfilter '+convert(nvarchar(256),traceid)+','+convert(nvarchar(256),val1)+','+convert(nvarchar(256),val2)+',' +convert(nvarchar(256),val3)+','+'N'''+convert(nvarchar(256),filter)+'''' from @tracefilter where id=@start print @tsql exec sp_executesql @tsql set @start=@start + 1 end exec sp_trace_setstatus @TraceID, 1 -- display trace id for future references select TraceID=@TraceID goto finish error: select ErrorCode=@rc finish: go
To run the above trace in QA after having much planning and discussion with Application team / QA Team to generate maximum load. After 2 hour of trace running and capturing events i end up with 25 GB of trace file.
Before you can start a distributed replay with the Microsoft SQL Server Distributed Replay feature, you must prepare the input trace data by initiating the preprocess stage from the distributed replay administration tool. In the preprocess stage, the distributed replay controller processes the trace data and generates an intermediate file:
Copy all the trace file on the Controller Server to preprocess the trace file.
https://msdn.microsoft.com/en-us/library/ff878408.aspx
dreplay preprocess -m $env:COMPUTERNAME -i "C:SQLServerDatabaseMigrationStandardScriptsSetupReplayTraceOnSourceSQLServerSourceTracefilesReplay.trc" -d "C:SQLServerDatabaseMigrationStandardScriptsSetupReplayTraceOnSourceSQLServerReplayTraceProcessedFiles" -f 20
-i : Trace file location
-d : preprocessed file location
-f : (Optional) Use the status_interval parameter, -f, to specify if you want the administration tool to display status messages at a frequency different than 30 seconds.
Once the preprocess stage is done. The output will look as below