There are one way to setup a trace programmatically but 2 ways to acquire and consume the trace data. To setup a trace, you need to to call trace supporting procedures to define events and columns. If you specify the location of the trace target, SQL Server will write trace data to files. By this approach, the SQL Server instance which reads the data files need to have file access permission to them. The another approach is that you don’t specify the target location. By this approach, the trace data will be “streamed” to the client directly.

Continue reading “SQL Server Tracing System (2) — 2 Ways to Receive Trace Data” →

SQL Server tracing system is used very often by DBAs and programmers for pinpointing issues in the databases. It’s supported by few system objects that describes itself including list of events, columns of each event might return, start and stop event, retrieve event data, status of each event sessions, etc. SQL Profiler utilizes those objects to retrieve and show the trace information on the screen. From debugging perspective, it’s enough for a SQL developer. DBAs, however, prefers to create trace programmatically and save the trace in the database or file system for alerting and later performance analysis. You can use SQL Profiler to trace the SQLs issued by anbother SQL Profiler to generate the trace procedure sequences. Alternatively, you can also browse trace related DMVs to find out all trace related metadata.

Continue reading “SQL Server Tracing System (1) — DMVs” →