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 Wednesday, October 24, 2012 8:16 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 8:53 PM
Points: 33,204, Visits: 15,353
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.







Follow me on Twitter: @way0utwest

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


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, August 22, 2014 11:54 AM
Points: 134, Visits: 282
+1 for including a log reader tool in SQL Server!
Post #1376515
Posted Wednesday, October 24, 2012 9:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:05 PM
Points: 1,334, Visits: 3,068
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.



"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #1376537
Posted Wednesday, October 24, 2012 9:17 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:47 PM
Points: 479, Visits: 788
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
Post #1376544
Posted Wednesday, October 24, 2012 9:31 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:36 AM
Points: 24, Visits: 102
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.

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?)
Post #1376552
Posted Wednesday, October 24, 2012 11:07 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 26, 2014 11:33 AM
Points: 510, Visits: 545
IowaTechBear (10/24/2012)
Just look at the TV commercials

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



Post #1376600
Posted Wednesday, October 24, 2012 12:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 11:46 AM
Points: 5, Visits: 77
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.
Post #1376632
Posted Wednesday, October 24, 2012 2:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:44 AM
Points: 12,910, Visits: 32,015
Jet-Ski (10/24/2012)
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.


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.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1376653
Posted Wednesday, October 24, 2012 2:33 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:27 AM
Points: 464, Visits: 314
Jet-Ski (10/24/2012)
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.


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.


___________________________________
I love you but you're standing on my foot.
Post #1376673
Posted Wednesday, October 24, 2012 2:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 1, 2013 2:48 PM
Points: 34, Visits: 104
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.
Post #1376677
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse