Database Experimentation Assistant - Capture Trace


Capture trace allows you to easily produce a trace file (.trc) containing a log of what events occurred on the specified server within a given period of time. This action needs to be performed once per server.

Note:

  1. Before starting your trace capture, please make sure to backup all your target databases.
  2. Query caching in SQL may affect evaluation results. Restarting the SQL Server service (MSSQLSERVER) in the services application will improve consistency in evaluation results and is recommended.

STEP ONE: OPEN “CAPTURE TRACES"
Open the tool and click the hamburger icon on the left side of screen. This will open the left side bar menu. Next, click “Capture Traces” next to the camera icon . This will open the Capture Traces section.

Capture1

STEP TWO: ENTER INPUTS
Fill in the input fields before starting capture trace operation.

CaptureTraceInputs

  1. Server name – Provide a SQL server name where the server trace you want captured is running.
  2. Database name - Provide a Database name to start the trace on it. If you do not specify a database, trace will be captured on all the databases on the server.
  3. Name for capture – Name the trace file (.trc) for your capture.
  4. Max file size – Specify the rollover size for files; a new file will be created, as needed, at the specified file size. Recommended rollover size is 200 MB.
  5. Duration – Select the length of time (in minutes) you want the capture trace to run.
  6. Path to store output trace file – Specify the destination path for the trace file.
    Note: File path to trace file must be on the machine the SQL instance is on. If SQL Service is not set for a specific account, it may need write permissions to the specified folder in order for the trace file to be written.

STEP THREE: CLICK StartButton BUTTON
If the inputs are valid, the capture trace process will begin. Otherwise the fields which are invalid inputs will be highlighted with red, please make sure entered values are accurate and again click "Start" button.

What's Next?
You will need to wait until the capture trace has finished running and locate your new trace file in the file location you specified. You can monitor the progress of the capture by clicking on the bell icon at the bottom left of the navigation pane.

ProgressOnCapture

Trace File (.trc) – Capture Trace writes out a .trc file to the location specified. This file contains trace results of the activity of a SQL database. TRC files are designed to provide more information about errors that are detected and reported by SQL Server.

Comments (1)

  1. StartCaptureTrace.sql can be edited to allow parallel traces for other dbs. Edited Lines 26-33.

    --Raise a sql error if any existing traces are still running in the server
    declare @path nvarchar(300)
    set @path ='%DEAExperimentation_%' + @dbname +'%'
    if exists( select 1 from sys.traces with (nolock) where path like @path and [Status] = 1)
    begin
    RAISERROR('50000',16,-1)
    goto finish
    end

Skip to main content