After noticing 100's of sort_warning errors in a recent analysis of the default trace output, I am trying get the text of the queries causing those warnings.
SQL Server Profiler has an event called Sort Warnings, but it doesn't have the TextData column.
Using Extended Events is not possible for 2008 R2, because the sort_warning event isn't available in Extended Events for 2008 R2.
I thought about placing a trigger on the table I'm using to store profiler's results to capture the output from DBCC INPUTBUFFER.
-- table to store the results of dbcc inputbuffer CREATE TABLE Trace_Results(EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(255)) CREATE TRIGGER [dbo].[Trace_Trigger] ON [dbo].Trace_Table AFTER INSERT AS BEGIN SET NOCOUNT ON; DECLARE @SQL VARCHAR(MAX) SELECT @SQL = 'DBCC INPUTBUFFER(' + CONVERT(VARCHAR(10), ins.SPID) + ')' FROM inserted ins --PRINT @SQL INSERT INTO Trace_Results (EventType, Parameters, EventInfo) EXEC(@SQL) END
I can start the trace from profiler without any errors, but when I run a query that causes a sort warning, I get the following error:
Which causes the trace to stop.
I also just tried inserting directly into the Trace_Table table with the following command
INSERT INTO trace_table (eventclass) values(1)
, but nothing shows up in the Trace_Results table.
If I place the same trigger on any other user table in my database, the results from DBCC INPUTBUFFER are stored as expected in Trace_Results.
What would prevent a trigger on a table created in SQL Server Profiler from inserting data into another table?
Trying @Kin's solution, but still getting NULL in the SQLText columns
Query used to cause sort warnings
-- Declare table variable DECLARE @T1 TABLE (Col1 int, Col2 varchar(8000)) -- Insert data to table variable INSERT INTO @T1 SELECT * FROM dbo.TestTable -- Return data sorted on Col2 column SELECT * FROM @T1 ORDER BY Col2 ASC
Screen shot of sort_warnings_log table