October 23, 2012 at 9:38 pm
Comments posted to this topic are about the item A SQL Server Log Reader
October 24, 2012 at 1:50 am
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
October 24, 2012 at 2:21 am
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!
October 24, 2012 at 3:34 am
[font="Verdana"]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. [/font]
October 24, 2012 at 6:31 am
@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.
October 24, 2012 at 6:40 am
It could be the DBA running a delete without a WHERE clause
I've never done that, at least not this week .... :hehe:
October 24, 2012 at 7:04 am
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... 😎
October 24, 2012 at 7:26 am
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>
October 24, 2012 at 8:16 am
paul.knibbs (10/24/2012)
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!
Restoring isn't always an option. If I whack the current partition of orders for a 2TB database, I don't want to restore.
October 24, 2012 at 8:28 am
+1 for including a log reader tool in SQL Server!
October 24, 2012 at 9:07 am
I use select * from fn_dblog (null,null) when i need to look into the T-Log. A little cumbersome at first, but has got some good information in it and if you know what you are looking for it works.:-D
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
October 24, 2012 at 9:17 am
Not going to happen. Microsoft knows first hand that there is this thing we call a lawyer, and that entity just loves to sue Microsoft for issues real and perceived. Develop a tool to read a log, wait for unqualified users to destroy their data, and companies are going to be filing lawsuit after lawsuit blaming Microsoft. I can imagine the testimony "well we let interns build it".
To my knowledge no vendor provides this, and I doubt any ever will.
Dave
October 24, 2012 at 9:31 am
djackson 22568 (10/24/2012)
Not going to happen. Microsoft knows first hand that there is this thing we call a lawyer, and that entity just loves to sue Microsoft for issues real and perceived. Develop a tool to read a log, wait for unqualified users to destroy their data, and companies are going to be filing lawsuit after lawsuit blaming Microsoft. I can imagine the testimony "well we let interns build it".To my knowledge no vendor provides this, and I doubt any ever will.
I would love to believe that you are wrong, but it can be so true. Just look at the TV commercials looking for people who have experienced liver damage from taking too much acetaminophen (Tylenol) to use the advertiser's legal services for compensation. And then look at the decades of warnings that physicians, pharmacists and package labeling that warn about over user leading to liver damage. Yet people kept popping it as they do aspirin thinking that a couple of extra will not hurt.:crazy:
I would love to see such a log tool, and I would hate to see the threat of lawsuits the reason why it would not be developed or released to the public.
-----------------
Larry
(What color is your database?)
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply