Want to know what process revert a change of a value in a column

Question

Want to know what process revert a change of a value in a column

imran-k

Valued Member

Points: 52
More actions
December 26, 2017 at 3:53 am

#409895

[Version : SQL Server 20123 Sp3]

I have a weird situation , spent days trying to figure out what process changes a value in a column of a typical/normal/user sql server table.
The column specifically is a date column where user changes the value in the front end client application but after a while (not know exact time but seems its takes several) the date in the column goes back to the original date.

Have checked the client application and nothing really suggests the cause of this behavior, have enabled CDC and it shows the change.
This problem is only in one table and in one column which is the date column which I have already mentioned. I have checked the custom stored procedures as well which do read this table but they do not involve in modifying or inserting in the table, it is a complete mystery!!
What I want to know is , is there any way I could know what process is reverting the date back to original date? CDC does not suggest that and it also does not give exact date and time of the change.
Please advise.
Thank you.
December 26, 2017 at 11:09 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

The column's value won't revert on its own, which either means someone is performing a restore on the database (which would revert everything) or someone or something is running an UPDATE, or DELETE and INSERT, statement that sets the value to a value that is the same as it was before.

There's a few options you could consider, such as setting up a trigger, an extended event or a trace. Do you also perform any ETL processes on the table? Perhaps that process is overwriting the value.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

LinksUp SSCertifiable Points: 6526 More actions · Answer 2

Thom A - Tuesday, December 26, 2017 5:58 AM
The column's value won't revert on its own, which either means someone is performing a restore on the database (which would revert everything) or someone or something is running an UPDATE, or DELETE and INSERT, statement that sets the value to a value that is the same as it was before.

This could be a transaction that is hitting a rollback instead of a commit. If a trigger is set up, it might be helpful to add an @TRANCOUNT to see if a transaction is active.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

imran-k Valued Member Points: 52 More actions · Answer 3

This table is read by other processes but not get modified only by front end application. The issue is only that date column has this issue and the auto reverting happens hours later each time, not exactly sure how many hours. If it was instant change then it would have been easier to pick up the cause probably. What should I write in the trigger to capture the process?

Thom A SSC Guru Points: 99130 More actions · Answer 4

LinksUp - Tuesday, December 26, 2017 6:03 AM
Thom A - Tuesday, December 26, 2017 5:58 AM
The column's value won't revert on its own, which either means someone is performing a restore on the database (which would revert everything) or someone or something is running an UPDATE, or DELETE and INSERT, statement that sets the value to a value that is the same as it was before.
This could be a transaction that is hitting a rollback instead of a commit. If a trigger is set up, it might be helpful to add an @TRANCOUNT to see if a transaction is active.

I'd be surprised it's an uncommitted transaction, the OP says it takes quite a while, and if there was an uncommitted transaction the table would be unavailable until it it commited or rolled back.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 5

imran-k - Tuesday, December 26, 2017 6:09 AM
This table is read by other processes but not get modified only by front end application. The issue is only that date column has this issue and the auto reverting happens hours later each time, not exactly sure how many hours. If it was instant change then it would have been easier to pick up the cause probably. What should I write in the trigger to capture the process?

Capture logins (including but not limited Original_Login()), capture host and other machine names and, possibly, capture what's running using some of the system DMVs. Sp_WhoIsActive (by Adam Machanic is helpful there if you don't want to build your own).

--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)

imran-k Valued Member Points: 52 More actions · Answer 6

Thanks all for the inputs. The date field is a datetime field. Ok what i had done is created an update trigger which captures suser_sname and app_name and i can confirm that it is not the front end application that is making or reverting the change, the user came out to be as EUMI and app name is Sql server , any ideas what EUMI user is??

No any other physical user is making the change as no one using application due to holidays.

Thanks

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 7

imran-k - Wednesday, December 27, 2017 7:14 PM
Thanks all for the inputs. The date field is a datetime field. Ok what i had done is created an update trigger which captures suser_sname and app_name and i can confirm that it is not the front end application that is making or reverting the change, the user came out to be as EUMI and app name is Sql server , any ideas what EUMI user is?? No any other physical user is making the change as no one using application due to holidays.Thanks

Try adding ORIGINAL_LOGIN() to the mix in the trigger to see if someone is doing some sort of "impersonation". Also check to see if there's an "Active Directory" user in the Windows Domain Controller by that name. I know of no such name that would come out of SQL Server itself and a quick search on Yabingoolehoo turns up nothing even as an abbreviation except for some very obscure references either for a Korean name of some local government department names.

--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)