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 123»»»

A SQL Server Log Reader Expand / Collapse
Author
Message
Posted Tuesday, October 23, 2012 9:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:56 PM
Points: 33,182, Visits: 15,320
Comments posted to this topic are about the item A SQL Server Log Reader






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1376311
Posted Wednesday, October 24, 2012 1:50 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:43 AM
Points: 1,434, Visits: 721
Isn't it the case that most of this work is already there with the Log Reader Agent available for replication and change data capture. It really wouldn't take much effort to tweak to achieve the desire result
Post #1376343
Posted Wednesday, October 24, 2012 2:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:30 AM
Points: 1,631, Visits: 5,575
Surely, for the faulty transaction to still be in the log file, the database must be in full (or bulk-logged) recovery, so you ought to be able to do a point in time restore to before the oops moment? In simple recovery the transaction could well have already been overwritten in the logs when you come to look at it, and if you're in full recovery and not taking regular log backups...well, I don't think I need to elaborate on the problems there!
Post #1376352
Posted Wednesday, October 24, 2012 3:34 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 22, 2014 11:09 AM
Points: 211, Visits: 1,111
Yes, one another vote for such "disposable" tools, if build by official vendor.

I have experienced that with the advent of many software programming paradigms, tools and techies; the non standard mechanisms and nonofficial tools have come into market, which in-fact in long run hurting the software industry. Microsoft may have advantage that it develop almost all necessary official tools in-house, which makes it a bit more reliable other than patching the applications with a lot of third party tools, or whatever we call it.

Thank You.
Post #1376383
Posted Wednesday, October 24, 2012 6:31 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 1:27 PM
Points: 139, Visits: 617
@paul.knibbs

I ran into this early in my career and here's why you want to roll back individual transactions: if you restore and stop at a time before the 'oops' and you roll back [all] transactions when you had a few thousand concurrent connections manipulating data, entire departments can lose lots of man-hours of work. If at all possible, the trick is to roll back the one transaction that caused the problem. Sometimes that's not going to be possible but if you have the situation that benefits from fixing the data from that one transaction, you really need to try to do it that way.

I agree about Microsoft providing even a basic tool for individual transaction log recovery. This feature would make SQL Server stand above "the competition" (you all know who I mean). I'm sure there are cases where rolling back a transaction that changed every record in a large table while subsequent transactions yet again manipulated the data are just too complicated and disjointed, but if I "pulled the plug" right after the mistake and was able to roll back just that one transaction, I could "plug it back in" and continue my daily operation [without] a db restore or lost changes in other tables/modules of the app.

Additionally, as a troubleshooting/tracing tool, a transaction log reader would just rock and I'm all about the cool new toys.
Post #1376437
Posted Wednesday, October 24, 2012 6:40 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 3,465, Visits: 1,488
It could be the DBA running a delete without a WHERE clause


I've never done that, at least not this week ....
Post #1376441
Posted Wednesday, October 24, 2012 7:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 25, 2014 10:27 AM
Points: 71, Visits: 671
Absolutely agree with you, Steve.

Starting out as a new DBA, when my mentor happened to be on vacation, I ran across a database that had been hacked, and our cyber security team wanted me to go through the logs to see when the activity occurred and to see if we could pinpoint who. In situations such as that (especially being a newbie), there's no time to purchase software, so you either have to use the tools on-hand or see if you can find a free download. I actually ended up downloading RedGate's Log Rescue...
Post #1376451
Posted Wednesday, October 24, 2012 7:26 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 1:27 PM
Points: 139, Visits: 617
It happened several years ago but feels like it was just yesterday...

I was at this company maybe a month or two. We had a third party app with thousands of concurrent connections constantly. It turns out the vendor ported the database from something like MS Access and still used the old "max-ID-plus-one" strategy to generate sequence numbers. (We found out that with our volume of activity we had dozens of requestors for a new ID number and were getting dozens-minus-one losers. Every couple of seconds.) The vendor's support engineer was on the speaker phone in the cube where I had a keyboard at a console with a query window open to the server. There were possibly 8 or 10 individuals packed in the cube or the adjoining hallway, some engineers, some security, some client support, some management. The product expert on the phone gives me an update statement for the core "sequence" table. I repeat it as I'm typing. I finish typing and repeat it again: "UPDATE this SET this = that". "Are you sure?" "Yeah." he replies. [JUST] as I hit the Execute button he drops the "oh, I think you'll need a WHERE clause" bomb. I drop the "F" bomb, not caring who hears me. I immediately set the database offline, note the time, tell the engineers to shut down the app servers and tell the support managers to send an alert to the end users that the server is down. I tell the vendor support chimp that I'm done with him and hang up, proceeding to do a database point-in-time recovery. There's nothing "lucky" about saving the tail of the log to a file and using the transaction log backups done throughout the day. A lot of work was not lost and lemons to lemonade it proved my disaster recovery worked.

Lesson learned 1: You can get away with dropping the "F" bomb in rare cases, but don't push your luck.
Lesson learned 2: Be prepared for any flavor of disaster and practice, practice, practice.
Lesson learned 3: If the query doesn't smell right, follow your gut feel even if you have "product experts" on the phone. BEGIN TRAN with no COMMIT until you open a second query window and test the results. Totally my bad but in my defense I didn't know the database, data or structure so I had no idea what I was updating.

There I go ruining an apology with an excuse... <lol>
Post #1376462
Posted Wednesday, October 24, 2012 7:29 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 1:27 PM
Points: 139, Visits: 617
Sorry, I forgot to mention the point of my little adventure story: if I could have rolled that one transaction back, I wouldn't have had to restore the database.

BTW: @Scott A. Nice logo. Go Wings!
Post #1376463
Posted Wednesday, October 24, 2012 7:42 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 3,465, Visits: 1,488
BTW: @Scott A. Nice logo. Go Wings!


Right now, just plain go NHL Owners/NHLPA. I just want to see hockey.
Post #1376478
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse