Unexpected data rollbacks _ Tool needed

  • 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?

  • 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/

  • 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
  • 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?

  • 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

  • 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?

  • 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
  • I'll give it a try. There's not much that's automated. Just the strangest thing.

  • 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

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

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