Why don't I see my statements in the SQL Server trace file?
0
votes
1
answer
89
views
I've written a SQL-Server Profiler script file in order to see why some database actions are unsuccessful. The idea behind the script is:
- Show me the starting statements (where an application starts writing something in the database)
- Show me the finishing statements (where the mentioned application has finished writing something in the database)
- Show me the "Attention" ones (where something goes wrong)
The script looks as follows:
The result of
`
DECLARE @TraceID INT;
DECLARE @MaxFileSize BIGINT = 50; -- File size limit in MB
DECLARE @TraceFilePath NVARCHAR(256) = N'C:\SQLTraces\Trace_OrderPart'; -- Base trace file path
-- Step 1: Create a new trace
EXEC sp_trace_create
@traceid = @TraceID OUTPUT,
@options = 2, -- File rollover enabled
@tracefile = @TraceFilePath,
@maxfilesize = @MaxFileSize,
@stoptime = NULL; -- No automatic stop
-- Step 2: Set events to capture
-- Add events to monitor SQL statements and related information
EXEC sp_trace_setevent @TraceID, 16, 1, 1; -- EventClass (SP:Attention) TextData
EXEC sp_trace_setevent @TraceID, 16, 4, 1; -- EventClass (SP:Attention) TransactionId
EXEC sp_trace_setevent @TraceID, 16, 9, 1; -- EventClass (SP:Attention) ClientProcessID
EXEC sp_trace_setevent @TraceID, 16, 10, 1; -- EventClass (SP:Attention) ApplicationName
EXEC sp_trace_setevent @TraceID, 16, 11, 1; -- EventClass (SP:Attention) LoginName
EXEC sp_trace_setevent @TraceID, 16, 12, 1; -- EventClass (SP:Attention) SPID
EXEC sp_trace_setevent @TraceID, 16, 13, 1; -- EventClass (SP:Attention) ClientProcessID
EXEC sp_trace_setevent @TraceID, 16, 14, 1; -- EventClass (SP:Attention) StartTime
EXEC sp_trace_setevent @TraceID, 16, 15, 1; -- EventClass (SP:Attention) EndTime
EXEC sp_trace_setevent @TraceID, 16, 20, 1; -- EventClass (SP:Attention) Severity
EXEC sp_trace_setevent @TraceID, 16, 23, 1; -- EventClass (SP:Attention) Success
EXEC sp_trace_setevent @TraceID, 16, 25, 1; -- EventClass (SP:Attention) IntegerData
EXEC sp_trace_setevent @TraceID, 16, 27, 1; -- EventClass (SP:Attention) EventClass
EXEC sp_trace_setevent @TraceID, 16, 31, 1; -- EventClass (SP:Attention) Error Number
EXEC sp_trace_setevent @TraceID, 16, 34, 1; -- EventClass (SP:Attention) ObjectName
EXEC sp_trace_setevent @TraceID, 16, 49, 1; -- EventClass (SP:Attention) RequestID
EXEC sp_trace_setevent @TraceID, 16, 55, 1; -- EventClass (SP:Attention) IntegerData2
EXEC sp_trace_setevent @TraceID, 40, 1, 1; -- EventClass (SP:StmtStarting) TextData
EXEC sp_trace_setevent @TraceID, 40, 4, 1; -- EventClass (SP:StmtStarting) TransactionId
EXEC sp_trace_setevent @TraceID, 40, 9, 1; -- EventClass (SP:StmtStarting) ClientProcessID
EXEC sp_trace_setevent @TraceID, 40, 10, 1; -- EventClass (SP:StmtStarting) ApplicationName
EXEC sp_trace_setevent @TraceID, 40, 11, 1; -- EventClass (SP:StmtStarting) LoginName
EXEC sp_trace_setevent @TraceID, 40, 12, 1; -- EventClass (SP:StmtStarting) SPID
EXEC sp_trace_setevent @TraceID, 40, 13, 1; -- EventClass (SP:StmtStarting) ClientProcessID
EXEC sp_trace_setevent @TraceID, 40, 14, 1; -- EventClass (SP:StmtStarting) StartTime
EXEC sp_trace_setevent @TraceID, 40, 15, 1; -- EventClass (SP:StmtStarting) EndTime
EXEC sp_trace_setevent @TraceID, 40, 20, 1; -- EventClass (SP:StmtStarting) Severity
EXEC sp_trace_setevent @TraceID, 40, 23, 1; -- EventClass (SP:StmtStarting) Success
EXEC sp_trace_setevent @TraceID, 40, 25, 1; -- EventClass (SP:StmtStarting) IntegerData
EXEC sp_trace_setevent @TraceID, 40, 27, 1; -- EventClass (SP:StmtStarting) EventClass
EXEC sp_trace_setevent @TraceID, 40, 31, 1; -- EventClass (SP:StmtStarting) Error Number
EXEC sp_trace_setevent @TraceID, 40, 34, 1; -- EventClass (SP:StmtStarting) ObjectName
EXEC sp_trace_setevent @TraceID, 40, 49, 1; -- EventClass (SP:StmtStarting) RequestID
EXEC sp_trace_setevent @TraceID, 40, 55, 1; -- EventClass (SP:StmtStarting) IntegerData2
EXEC sp_trace_setevent @TraceID, 41, 1, 1; -- EventClass (SP:StmtCompleted) TextData
EXEC sp_trace_setevent @TraceID, 41, 4, 1; -- EventClass (SP:StmtCompleted) TransactionId
EXEC sp_trace_setevent @TraceID, 41, 9, 1; -- EventClass (SP:StmtCompleted) ClientProcessID
EXEC sp_trace_setevent @TraceID, 41, 10, 1; -- EventClass (SP:StmtCompleted) ApplicationName
EXEC sp_trace_setevent @TraceID, 41, 11, 1; -- EventClass (SP:StmtCompleted) LoginName
EXEC sp_trace_setevent @TraceID, 41, 12, 1; -- EventClass (SP:StmtCompleted) SPID
EXEC sp_trace_setevent @TraceID, 41, 13, 1; -- EventClass (SP:StmtCompleted) ClientProcessID
EXEC sp_trace_setevent @TraceID, 41, 14, 1; -- EventClass (SP:StmtCompleted) StartTime
EXEC sp_trace_setevent @TraceID, 41, 15, 1; -- EventClass (SP:StmtCompleted) EndTime
EXEC sp_trace_setevent @TraceID, 41, 20, 1; -- EventClass (SP:StmtCompleted) Severity
EXEC sp_trace_setevent @TraceID, 41, 23, 1; -- EventClass (SP:StmtCompleted) Success
EXEC sp_trace_setevent @TraceID, 41, 25, 1; -- EventClass (SP:StmtCompleted) IntegerData
EXEC sp_trace_setevent @TraceID, 41, 27, 1; -- EventClass (SP:StmtCompleted) EventClass
EXEC sp_trace_setevent @TraceID, 41, 31, 1; -- EventClass (SP:StmtCompleted) Error Number
EXEC sp_trace_setevent @TraceID, 41, 34, 1; -- EventClass (SP:StmtCompleted) ObjectName
EXEC sp_trace_setevent @TraceID, 41, 49, 1; -- EventClass (SP:StmtCompleted) RequestID
EXEC sp_trace_setevent @TraceID, 41, 55, 1; -- EventClass (SP:StmtCompleted) IntegerData2
-- Step 3: Set a filter for the table CommandOrderParts
-- exec sp_trace_setfilter @TraceID, 34, 0, 6, N'%VehicleOrderPart%'; -- ObjectName filter
exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%VehicleOrderPart%'; -- TextData filter
-- Optional: Output TraceID for reference
SELECT @TraceID AS TraceID;
-- Optional: Add a filter for ApplicationName to capture specific applications
-- EXEC sp_trace_setfilter @TraceID, 10, 0, 6, N'XClarys.Server'; -- Filter for XClarys.Server
-- EXEC sp_trace_setfilter @TraceID, 10, 1, 6, N'Cerastore.JBT'; -- Filter for Cerastore.JBT
-- Step 4: Start the trace
EXEC sp_trace_setstatus @TraceID, 1; -- Start the trace
-- Step 5: Stop the trace
-- EXEC sp_trace_setstatus @TraceID, 0; -- Stop the trace
-- EXEC sp_trace_setstatus @TraceID, 2; -- Stop the trace
`
The results look as follows:

SELECT @@VERSION
on that database is Microsoft SQL Server 2017 (RTM-CU31-GDR) (KB5029376) - 14.0.3465.1 (X64) Jul 30 2023 15:31:58 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)
.
I am looking at the *.trc
file on my computer, where the result of SELECT @@VERSION
equals Microsoft SQL Server 2022 (RTM-GDR) (KB5046861) - 16.0.1135.2 (X64) Oct 18 2024 15:31:58 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 19045: ) (Hypervisor)
.
The question is rather obvious: where are my starting and finishing statements???
Asked by Dominique
(609 rep)
Dec 9, 2024, 08:42 AM
Last activity: Jan 11, 2025, 03:17 AM
Last activity: Jan 11, 2025, 03:17 AM