Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unexpected data rollbacks _ Tool needed


Unexpected data rollbacks _ Tool needed

Author
Message
fldbryan
fldbryan
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 4
Let me start by saying I am not a full fledged DBA. the problem I am having is that I am having several tables that every couple of days are rolling back to their values back in November. The only thing I can think of is some type of rogue rollback. Is there any tool I can use to see what's going on insode my database that could be doing this? Has any DBA ever heard of this problem?

I am stuck on this one?
BrainDonor
BrainDonor
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2052 Visits: 11203
That isn't a rollback in the normal DB sense - something is running that modifies that table.

Your best bet is probably a server-side trace, have a look at this series of articles: http://www.sqlservercentral.com/stairway/72363/

Steve Hall
Linkedin
Blog Site
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47273 Visits: 44392
A rollback could not cause that unless you had a transaction open since November (and if you had, you'd have known by now due to the blocking and the tran log growth)

Sounds like something is logging in every couple of days and changing the values. Check jobs, make sure there's nothing odd. Otherwise maybe a server-side trace for logins and updates to that table.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


fldbryan
fldbryan
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 4
I admit it doesn't seem normal but that table keeps getting restored. I put an audit trigger on there so maybe it will help. is there any tool out there that can show me what is currently running in my database server?
Greg A Goss
Greg  A Goss
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 402
That could be any number of things, so I'll start out with a general answer. I'd start by opening Profiler and creating a trace to see what is actually happening against the tables you're having issues with. I'd also check SQL logs to make sure there aren't any restores occurring that you aren't aware of.

If there were transactions being held open for a couple of days, you'd likely not even see the data when you select from these tables as once data is committed it doesn't get rolled back and if it hasn't been committed yet, then basic select statements wouldn't return the data. Also, if an insert or update transaction was running that long, you'd run into a lot more issues with people not being able to get to data. Try to rule out things as well because figuring out what the problem is not is a great step on the path towards figuring it out what it is.

-Greg Goss
fldbryan
fldbryan
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 4
Here is another oddity. On one table it is only one field for a select set of people that keeps getting reset.

If I backed up the database, destroyed it and then restored it, would it basically start with a clean state?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47273 Visits: 44392
I stand by what I said earlier. Sounds like some process is logging in every couple of days and updating the table to old values. Check logs, set up a server-side trace, find out exactly what is happening because that's not a rollback in the transaction sense.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


fldbryan
fldbryan
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 4
I'll give it a try. There's not much that's automated. Just the strangest thing.
Greg A Goss
Greg  A Goss
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 402
If there's one thing I've learned as a DBA:

Love SQL Profiler and SQL Logs...just because you aren't expecting it to happen or that no one knows that it's happening, doesn't mean it isn't happening! "Strange" is just part of the job :-)

-Greg Goss
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