SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
imran-k
imran-k
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 21
[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.
Thom A
Thom A
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74127 Visits: 19144
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
LinksUp
LinksUp
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5111 Visits: 5416
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
imran-k
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 21
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
Thom A
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74127 Visits: 19144
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (815K reputation)SSC Guru (815K reputation)SSC Guru (815K reputation)SSC Guru (815K reputation)SSC Guru (815K reputation)SSC Guru (815K reputation)SSC Guru (815K reputation)SSC Guru (815K reputation)

Group: General Forum Members
Points: 815071 Visits: 46210
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3220 Visits: 531
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?

Please clarify whether the date field is an epoch or date-time field ? If it is epoch field, check the related Stored Proc update statement in the back end, (dateadd function), Otherwise check the front end application web services update operation onto this epoch / date-time field ? ( how this db table date field getting populated when front end value date field modified by the users)

You'd as well mentioned this table has been read by other processes, then check anyone from your team / shared services team had been updating the db table manually ( with excel / csv dump ) post the front end application committed the date value in db table ?

imran-k
imran-k
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 21
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
Jeff Moden
SSC Guru
SSC Guru (815K reputation)SSC Guru (815K reputation)SSC Guru (815K reputation)SSC Guru (815K reputation)SSC Guru (815K reputation)SSC Guru (815K reputation)SSC Guru (815K reputation)SSC Guru (815K reputation)

Group: General Forum Members
Points: 815071 Visits: 46210
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search