KB (11/8/2007)
But when I try to implement the trigger provided by Solomon on table IndexCapture ( I changed the table name in the trigger), the trigger gets created successfully but immediately after that an error pops up in SQL Profiler 'Failed to save trace data to table' and the trace is stopped.
Hello KB. I think I found the problem. In SQL Server 2005, the "EventInfo" field returned by the DBCC INPUTBUFFER command has increased in size from 255 to 4000. So, the CREATE TABLE #DBCCInfo line should look as follows:
CREATE TABLE #DBCCInfo (EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(4000))
After adding the trigger, if the trace stops for any reason, here is a way to test the trigger:
INSERT INTO dbo.TraceTable (SPID) VALUES (@@SPID)
I tried this and got an error stating that data would be truncated. That led me to look at the temp table definition and then to Books Online to see the values that DBCC INPUTBUFFER was reporting.
ALSO, it appears that I was a bit hasty in posting this the first time so I am editing now to add this paragraph. It seems that even after increasing the EventInfo field to 4000 it still gave the Failed to save trace data to table. error and stops the trace. So after more investigating I was able to work around the problem. For some reason, adding the trigger to the table while the trace is running causes the problem. I am not exactly sure why but that is definitely the problem. So, the trick to fix it is to follow these steps:
1) Start the trace (this creates the table)
2) Pause the trace
3) Run the script to create the trigger
4) UN-Pause the trace
I hope this helps.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR