Fault-finding unexpected data changes

Question

Post reply

Fault-finding unexpected data changes

zoggling

SSCrazy

Points: 2243
More actions
November 16, 2022 at 12:56 pm

#4113711

We have an established ASP.NET application and SQL Server database in production, and have observed a "status" (integer ID value) in a particular user table changing value, then reverting back, before changing to the original value again, all within a few milliseconds.
There are triggers on the table, and a good amount of code around this area. But there is no obvious culprit.
What I am trying to establish is, "what is causing the status to oscillate in this way?", as looking at the code it shouldn't be doing so.
We have considered writing @@NESTLEVEL to a table in particular objects, but this doesn't really give us insight as to what is causing the oscillation in status.
How else might we best fault-find this?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Cascading triggers?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

zoggling SSCrazy Points: 2243 More actions · Answer 2

Could well be...

I have created an Extended Event session to try to capture what's going on.

-- Create new Extended Event session.
CREATE EVENT SESSION [StatusChanges] ON SERVER 
ADD EVENT sqlserver.sp_statement_completed 
(
SET collect_object_name = (1), collect_statement = (1)
    ACTION (sqlserver.database_name, sqlserver.server_principal_name, sqlserver.sql_text)
    WHERE 
(
[sqlserver].[equal_i_sql_unicode_string] ([sqlserver].[database_name], N'MyDatabase') AND 
[sqlserver].[like_i_sql_unicode_string] ([statement], N'%UPDATE%P%SET%Status%=%')
)
)
ADD TARGET package0.event_file (SET filename = N'C:\Temp\StatusChanges.xel', max_file_size=(100))
WITH 
(
MAX_MEMORY = 4096 KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 30 SECONDS,
MAX_EVENT_SIZE = 0 KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = ON,
STARTUP_STATE = ON
);
GO

This reply was modified 2 years ago by zoggling.