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


A SQL Server Log Reader


A SQL Server Log Reader

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (251K reputation)SSC Guru (251K reputation)SSC Guru (251K reputation)SSC Guru (251K reputation)SSC Guru (251K reputation)SSC Guru (251K reputation)SSC Guru (251K reputation)SSC Guru (251K reputation)

Group: Administrators
Points: 251096 Visits: 19816
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
My Blog: www.voiceofthedba.com
SQLPhil
SQLPhil
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2386 Visits: 740
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
paul.knibbs
paul.knibbs
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6418 Visits: 6240
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!
Abrar Ahmad_
Abrar Ahmad_
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1490 Visits: 1312
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.

Your Name Here
Your Name Here
SSC Eights!
SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)

Group: General Forum Members
Points: 807 Visits: 857
@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.
Scott Arendt
Scott Arendt
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: 5132 Visits: 1733
It could be the DBA running a delete without a WHERE clause


I've never done that, at least not this week .... Hehe
Dizzy Desi
Dizzy Desi
Old Hand
Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 Visits: 687
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... Cool
Your Name Here
Your Name Here
SSC Eights!
SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)

Group: General Forum Members
Points: 807 Visits: 857
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>
Your Name Here
Your Name Here
SSC Eights!
SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)

Group: General Forum Members
Points: 807 Visits: 857
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!
Scott Arendt
Scott Arendt
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: 5132 Visits: 1733
BTW: @Scott A. Nice logo. Go Wings!


Right now, just plain go NHL Owners/NHLPA. I just want to see hockey.
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