SQLServerCentral / Editorials / SQLServerCentral.com / A SQL Server Log Reader / Latest Posts

RE: A SQL Server Log Reader

Steve Jones - SSC Editor — Fri, 09 Nov 2012 10:05:52 GMT

[quote][b]George Rypysc (11/3/2012)[/b][hr]See also Oracle's [url=http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#i1005606]LogMiner[/url] - built in by default.[/quote]Maybe we'll get a feature match at some point.

RE: A SQL Server Log Reader

George Rypysc — Sat, 03 Nov 2012 17:24:36 GMT

[quote][b]geoffrey.sturdy (10/25/2012)[/b][hr]Dave "To my knowledge no vendor provides this, and I doubt any ever will. "Burroughs/Unisys provided the "printaudit" program to do just that for their DMSII database , and Jade in New Zealand provides a similar tool for its OO database so your comment is incorrect....[/quote]See also Oracle's [url=http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#i1005606]LogMiner[/url] - built in by default.

RE: A SQL Server Log Reader

George M Parker — Tue, 30 Oct 2012 15:04:58 GMT

Back in the days of SQL 2000, Lumigent Log Explorer came in handy on multiple occasions. Once it was a developer who had access to the database through a front end cold fusion page and performed a delete without a where clause. On another occasion, it was a DBA who accidentally ran only part of a query which left out both the BEGIN TRAN and the WHERE clause. In both cases, I was able to use Lumigent Log Explorer to restore the data by reversing the transactions. Restoring the databases to pull the data out would have taken significantly more time. It was also very handy at answering the "Whodunnit" questions.I just recently had a situation in production where data was changed and we wanted to find out who made the change. A tool that could read the log would have been very handy to have.

RE: A SQL Server Log Reader

cfradenburg — Thu, 25 Oct 2012 07:28:14 GMT

[quote][b]paul.knibbs (10/25/2012)[/b][hr]OK, OK, I admit it: there are probably situations where this would be useful. I suppose I was thinking more about the "delete without WHERE" option, which to me would likely be such a massive data mess that you'd be better off doing a point-in-time restore, rather than smaller mistakes that are perhaps more suited to this approach.[/quote]Even in those cases. At my last job we had a tech set all patient notes to contain the exact same data. The fix was to restore the database to another server, copy that table over, and then do an update. During that time inaccurate data was in the patient's chart. Granted, the users knew so they could adjust but that's still not a good thing. Due to the size of the database it would have been quicker to just roll back the transaction so we didn't have to take time restoring.

RE: A SQL Server Log Reader

paul.knibbs — Thu, 25 Oct 2012 01:36:42 GMT

OK, OK, I admit it: there are probably situations where this would be useful. I suppose I was thinking more about the "delete without WHERE" option, which to me would likely be such a massive data mess that you'd be better off doing a point-in-time restore, rather than smaller mistakes that are perhaps more suited to this approach.

RE: A SQL Server Log Reader

geoffrey.sturdy — Thu, 25 Oct 2012 01:29:33 GMT

Dave "To my knowledge no vendor provides this, and I doubt any ever will. "Burroughs/Unisys provided the "printaudit" program to do just that for their DMSII database , and Jade in New Zealand provides a similar tool for its OO database so your comment is incorrect.As for the risk of Lawsuits - I'm sure that a corporate giant like Microsoft has a skilled enough legal team to draft a disclaimer that would enable them to doge that bullet

RE: A SQL Server Log Reader

Josh Turley-300197 — Wed, 24 Oct 2012 14:41:12 GMT

We could have done that, in fact we've done just that in the past when they didn't tell us for a few days. The DB in question is larger than most of our DBs, but not obscene (30GB). Would have taken me more time to go through all that trouble of doing the restore, then scripting each affected table than it would for the collective people to just re-enter 30 minutes of data. I've used SQL Data Compare before as well, nifty little tool. Works well in this situation, other than you still need to restore a dummy backup for it to compare against. I could be wrong, but that's what I remember from it.I could use Red Gates virtual backup deal, but I haven't looked at that thoroughly. Like Steve said, this is a pretty rare occurrence.

RE: A SQL Server Log Reader

AlreadyPicked — Wed, 24 Oct 2012 14:33:11 GMT

[quote][b]Jet-Ski (10/24/2012)[/b][hr]We actually had this just yesterday. Someone deleted 100 records from our CRM, told us about it 30 minutes later. We did a point in time restore, but lost 30 minutes of customer service cases and contact logs. It's less work adding those than it would have been to re-add the 100 contacts, but if we could have isolated just that transaction, it would have been nice.Had they come to us the next day, they would have been SOL and would have had to re-add those contacts on their own.[/quote]In those cases, we use Red Gate's SQL Data Compare. It's fantastic ;)+1 for a log reader. A ex-Oracle dba here told me that Oracle's log is actually readable. No need for a tool.

RE: A SQL Server Log Reader

Lowell — Wed, 24 Oct 2012 14:02:24 GMT

[quote][b]Jet-Ski (10/24/2012)[/b][hr]We actually had this just yesterday. Someone deleted 100 records from our CRM, told us about it 30 minutes later. We did a point in time restore, but lost 30 minutes of customer service cases and contact logs. It's less work adding those than it would have been to re-add the 100 contacts, but if we could have isolated just that transaction, it would have been nice.Had they come to us the next day, they would have been SOL and would have had to re-add those contacts on their own.[/quote]couldn't you have done a point in time restore on As a new database, and simply scripted the 100 deleted contacts out as a SQL INSERT statements to run on production?then the 30 minutes of data and the rework would not have been needed at all;if your database is huge, it might be hard to do, what with trying to come up with space for another monster db, but that's a better method, i think.

RE: A SQL Server Log Reader

Jet-Ski — Wed, 24 Oct 2012 12:46:04 GMT

We actually had this just yesterday. Someone deleted 100 records from our CRM, told us about it 30 minutes later. We did a point in time restore, but lost 30 minutes of customer service cases and contact logs. It's less work adding those than it would have been to re-add the 100 contacts, but if we could have isolated just that transaction, it would have been nice.Had they come to us the next day, they would have been SOL and would have had to re-add those contacts on their own.

RE: A SQL Server Log Reader

Randy Rabin — Wed, 24 Oct 2012 11:07:19 GMT

[quote][b]IowaTechBear (10/24/2012)[/b][hr]Just look at the TV commercials[/quote]I'd like to think that the commercials are an indication that lawsuits are down and the lawyers are hurting for business, but then I take off my rosy glasses and don't believe it for a second.Where we're running Enterprise (or Developer) Edition, something that helps me sleep at night is to have relatively-recent snapshots of ultra-critical databases. Yes, they create some I/O overhead and use up an unpredictable amount of disk space (so tread carefully before trying this) but they provide a really easy way to recover data back from a fat-finger or application "oops". Moreover you can have multiple snapshots, representing data at different points in time (last hour, yesterday, start of month, etc) It's not quite the same as single-transaction rollback, and not as point-in-time precise, but when they can be used for recovery they're so much faster than restoring from full backup.@Scott A: +1 on the hockey, but at least you have some baseball

RE: A SQL Server Log Reader

IowaTechBear — Wed, 24 Oct 2012 09:31:20 GMT

[quote][b]djackson 22568 (10/24/2012)[/b][hr]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.[/quote]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.

RE: A SQL Server Log Reader

djackson 22568 — Wed, 24 Oct 2012 09:17:37 GMT

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.

RE: A SQL Server Log Reader

TravisDBA — Wed, 24 Oct 2012 09:07:12 GMT

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

RE: A SQL Server Log Reader

bsclyde — Wed, 24 Oct 2012 08:28:31 GMT

+1 for including a log reader tool in SQL Server!

RE: A SQL Server Log Reader

Steve Jones - SSC Editor — Wed, 24 Oct 2012 08:16:26 GMT

[quote][b]paul.knibbs (10/24/2012)[/b][hr]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![/quote]Restoring isn't always an option. If I whack the current partition of orders for a 2TB database, I don't want to restore.

RE: A SQL Server Log Reader

Scott Arendt — Wed, 24 Oct 2012 07:42:40 GMT

[quote]BTW: @Scott A. Nice logo. Go Wings![/quote]Right now, just plain go NHL Owners/NHLPA. I just want to see hockey.

RE: A SQL Server Log Reader

Your Name Here — Wed, 24 Oct 2012 07:29:21 GMT

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!

RE: A SQL Server Log Reader

Your Name Here — Wed, 24 Oct 2012 07:26:37 GMT

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>

RE: A SQL Server Log Reader

Dizzy Desi — Wed, 24 Oct 2012 07:04:52 GMT

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:

RE: A SQL Server Log Reader

Scott Arendt — Wed, 24 Oct 2012 06:40:58 GMT

[quote]It could be the DBA running a delete without a WHERE clause[/quote]I've never done that, at least not this week .... :hehe:

RE: A SQL Server Log Reader

Your Name Here — Wed, 24 Oct 2012 06:31:30 GMT

@paul.knibbsI 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.

RE: A SQL Server Log Reader

Abrar Ahmad_ — Wed, 24 Oct 2012 03:34:07 GMT

[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]

RE: A SQL Server Log Reader

paul.knibbs — Wed, 24 Oct 2012 02:21:56 GMT

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!

RE: A SQL Server Log Reader

SQLPhil — Wed, 24 Oct 2012 01:50:54 GMT

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

A SQL Server Log Reader

Steve Jones - SSC Editor — Tue, 23 Oct 2012 21:38:30 GMT

Comments posted to this topic are about the item [B]A SQL Server Log Reader[/B]