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 «««12345»»

Reading SQL Server's Transaction Log Expand / Collapse
Author
Message
Posted Tuesday, November 23, 2010 7:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 19, 2014 9:26 AM
Points: 312, Visits: 1,028
Also I agree with you when you say that the best way to protect yourself against malevolent scripts is to understand what the script is doing before you run it. It takes time to figure out a script like this, and personally i would do it only if i had the time and interest and if it would really help me if/when i run into an issue(ok, issues i can predict). (I can think of atleast a 100 situations off hand that would have better use of my learning time).
Post #1025165
Posted Tuesday, November 23, 2010 8:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 8:17 AM
Points: 2,917, Visits: 2,537
Fat Wallet
From the perspective of a professional DBA and Dev, this is useless. First, it is troublesome. Second, it cannot guarantee pulling data correctly. Third, it does not give enough information for users.
Usually, we need a tool to check online logs or backup logs to find changes made by users for specific tables. Currently, ApexSQL log is the right tool for it. It is around $1300. It provides detailed information for all insert, delete and update operations including schema changes.


I would hope that you climb down off your perch to the level of many other Preofessional DBA's who do see a use for this. As for Apex's system, I believe that you would see that it is not truely a log reader as it places triggers on tables to trap transactions and saves them to a database as well as agents on the servers. RedGate had a system that worked reasonably well, reading the log files but they stopped support.

I would not use the information provided to try to undo a million row transaction, but it can come in handy for troubleshooting a problem or seeing of someone did something. But to make a global statement such as yours is rather pompous.


Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #1025208
Posted Tuesday, November 23, 2010 8:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 19, 2014 9:26 AM
Points: 312, Visits: 1,028
Seeing someone did something << Just want to clarify what MS CSS and several MVPs have said, the transaction log is not an auditing tool, it is never meant to see who did what. It is just meant to log activity to be used for recovery. There is nothing wrong at all in learning how it works in a lot of detail as much as one desires but this is typically what it should not be used for as much as possible.
Post #1025211
Posted Tuesday, November 23, 2010 10:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
a whole lot of knee jerk reactions about "but it's not supported!", but since it works on SQL 2008 ( and someone reported it works all the way thru Denali), and your server is going to be around a while, I don't see any advantage in poo-pooing the effort put in the script.

how about this typical example that gets about 10 posts a week on "how can i get my data back":

premise:
database is in FULL recovery, but noone has setup a backup strategy. No backups available anywhere.

someone updates the INVOICE table without a WHERE statement...say setting the INVOICEAMT to $100... or someone DELETES the table without a WHERE statement.

the upcoming articles on how to examine UPDATEs and DELETEs ( where this article covered INSERTS) would be the difference between being able to handle the situation and having to look for another position somewhere, as the company closes down due to data corruption, right?

now, if you can point out the recommended way to handle that specific situation without using undocumented features, and without changing the parameters of the issue (don't say "well there shoulda been backups) I'm listening.


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 #1025320
Posted Tuesday, November 23, 2010 10:46 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, December 11, 2014 8:11 AM
Points: 109, Visits: 608
If there is no backup strategy in place, and the database is in FULL recovery mode, the transaction log will likely be unreadable anyhow, as it will likely be massive in size due to no backups being taken.

If an administrator is capable enough to understand this script, there is no excuse not to have appropriate backups in place.

This script also assumes the database is in FULL recovery and not BULK_LOGGED, and I believe it would need to at least have one tran backup taken in FULL recovery in order to actually *be* in FULL recovery.

Post #1025330
Posted Tuesday, November 23, 2010 11:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
Phil Brammer (11/23/2010)
If there is no backup strategy in place, and the database is in FULL recovery mode, the transaction log will likely be unreadable anyhow, as it will likely be massive in size due to no backups being taken.

If an administrator is capable enough to understand this script, there is no excuse not to have appropriate backups in place.

This script also assumes the database is in FULL recovery and not BULK_LOGGED, and I believe it would need to at least have one tran backup taken in FULL recovery in order to actually *be* in FULL recovery.



naw, if you run the script from the article, you create a database on the fly and read the info from the log....no backup was created, but your reading the log anyway...because the model database is set to FULL by default.

the same situation i outlined above could occur if the existing backup strategy stopped working due to a password change, or the SQL Agent service not starting, or other situations....in that case, while the script may be a last resort, it shouldn't be excluded from someones toolbox of scripts just because it's using "unsupported features".

in your outline, if both a full and at least one tran log backup was in place, you could do a tailbackup and hopefully do a point in time restore...which is undoubtedly the better way to go.


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 #1025343
Posted Tuesday, November 23, 2010 11:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 19, 2014 9:26 AM
Points: 312, Visits: 1,028
Well, keep it if you think you need it, that is all. Backup failures should never be taken trivially, atleast not more than one, a responsible alert system has to be in place and as a DBA the first thing anyone should do should be to check if you had the previous backup run fine, be it dependant on sql agent or any other means. Granted some people have too many servers to handle, cannot afford alerting ( or that failed also), backup tapes get lost and the offsite location catches fire...on and on. To me there are just two simple questions and nothing to do with the unsupported nature of the command.

One If I am in a place that has a high chance of such stuff happening there is a deeper problem than just learning how to use this script,Two if that is not the case is the time and effort i would spend in understanding something like this is worth the probability of the situation happening and using it. That is every DBA's call. I don't think it is worth it and lot of DBAs i know would probably feel the same.
Post #1025365
Posted Friday, December 3, 2010 8:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:18 AM
Points: 318, Visits: 1,128
Nicely done article.

I've spent some time on transaction log reading and found that author Kevvie Fowler's book "SQL Server Forensic Analysis" is great. He explains it in great detail. But it's not a simple task to do.

Looking into the transaction log files is not something a DBA would do everyday but it's good to have an idea on how to do it. It is mainly done when performing forensic on a SQL server.

If are looking to into the transaction logs for diagnostics reasons I believe that a proper DB server analysis tool like Quest's Spotlight for SQL server and Redgate's SQL Monitor would be better.

Thanks,

Rudy




Post #1029933
Posted Friday, December 17, 2010 2:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:03 PM
Points: 8, Visits: 265
fat wallet (11/22/2010)
From the perspective of a professional DBA and Dev, this is useless. First, it is troublesome. Second, it cannot guarantee pulling data correctly. Third, it does not give enough information for users.
Usually, we need a tool to check online logs or backup logs to find changes made by users for specific tables. Currently, ApexSQL log is the right tool for it. It is around $1300. It provides detailed information for all insert, delete and update operations including schema changes. For example, for update operations, you will see each updated record's old and new data information together with user, time and so on. It is good enough for monitoring OLTP change and is very convenient to use.


fat wallet,

"Second, it cannot guarantee pully data correctly" this statement has me intrigued. If you know something about this then please enlighten the rest of us, we'd love to hear what you know on the subject.

Also how long have you worked for ApexSQL? Are you in their inside or outside sales department?

Just questions we'd all like the answers too.
Post #1036753
Posted Saturday, December 18, 2010 11:42 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
Lowell (11/23/2010)
a whole lot of knee jerk reactions about "but it's not supported!", but since it works on SQL 2008 ( and someone reported it works all the way thru Denali), and your server is going to be around a while, I don't see any advantage in poo-pooing the effort put in the script.

how about this typical example that gets about 10 posts a week on "how can i get my data back":

premise:
database is in FULL recovery, but noone has setup a backup strategy. No backups available anywhere.

someone updates the INVOICE table without a WHERE statement...say setting the INVOICEAMT to $100... or someone DELETES the table without a WHERE statement.

the upcoming articles on how to examine UPDATEs and DELETEs ( where this article covered INSERTS) would be the difference between being able to handle the situation and having to look for another position somewhere, as the company closes down due to data corruption, right?

now, if you can point out the recommended way to handle that specific situation without using undocumented features, and without changing the parameters of the issue (don't say "well there shoulda been backups) I'm listening.


Any DBA caught in this specific situation should put his head between his legs and pucker his lips to kiss his rear end goodbye. This is one of those avoidable situations they created unemployment insurance for.
Post #1036949
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse