Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unexpected data rollbacks _ Tool needed Expand / Collapse
Author
Message
Posted Monday, December 9, 2013 7:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 12:00 PM
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?
Post #1521067
Posted Monday, December 9, 2013 7:44 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 5:55 AM
Points: 1,483, Visits: 8,545
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/


BrainDonor
Linkedin
Blog Site
Post #1521091
Posted Monday, December 9, 2013 7:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1521092
Posted Monday, December 9, 2013 7:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 12:00 PM
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?
Post #1521098
Posted Monday, December 9, 2013 7:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:24 AM
Points: 310, Visits: 310

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
Post #1521100
Posted Monday, December 9, 2013 7:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 12:00 PM
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?
Post #1521102
Posted Monday, December 9, 2013 8:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1521105
Posted Monday, December 9, 2013 8:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 12:00 PM
Points: 4, Visits: 4
I'll give it a try. There's not much that's automated. Just the strangest thing.
Post #1521157
Posted Monday, December 9, 2013 9:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:24 AM
Points: 310, Visits: 310
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
Post #1521181
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse