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 Monday, November 22, 2010 2:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 4:01 PM
Points: 8, Visits: 242
Mike C (11/22/2010)
I'd like to stress a point the author mentioned in passing: this function is UNDOCUMENTED. What does that really mean? Well, to summarize:

1) Microsoft does not support the function. If you run into issues in production with this function, the first solution you will probably get is to remove the dependency on this function. Apart from that you're probably pretty much on your own.

2) It could have undesirable side effects. Blocking comes immediately to mind for this function.

3) It may not be available/accessible in the next release. That is, it can be pulled at any time, including major release, minor release, or even service pack. Sp_makewebtask anyone?

You mentioned Paul Randall, Kimberly Tripp and Isaac Kunen in your article, but I've never seen a recommendation from them to use this in production. Every time I've seen them use this it has been to demo one-off troubleshooting. You may want to clarify this before people start asking how they can use this to do things like create their own transaction log monitoring or home-baked custom replication systems in production environments. Nobody wants to waste money on a CSS call only to find out the code they've written using undocumented functionality is not supported.


TRACEY-320982

Mike and Phil's points about these commands being "Undocumented" and unsupported cannot be stressed enough, obviously. But a very popular blogger once said "It's a physical-record locator function! Undocumented and unsupported (obviously), but hey, some of the best features are " -Paul S. Randal [url=http://sqlkpi.com/BLOGS/PAUL/category/Undocumented-commands.aspx][/url]

fn_dblog has changed over the different versions of SQL Server, but it also has survived all of the versions to date as well. It also is the basis for many of the third party applications available, so you be the judge.

I've given you some tools that you can use for your exploration, what you do with it is only limited to your knowledge and imagination. If you've got the wherewithal to build a replication system or some other mechanism using it, then by all means do so. But do keep in mind there are risks to such an appoach that can't be ignored.

When questioned about his work to come up with first light bulb, Thomas Edision replied. “I haven't failed, I've found 10,000 ways that don't work”.
Post #1024726
Posted Monday, November 22, 2010 3:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 04, 2014 2:20 PM
Points: 2, Visits: 113
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.
Post #1024742
Posted Monday, November 22, 2010 3:41 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
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.


You go too far, sir. This article is not "useless". Knowing how to read this function's output can help when troubleshooting specific issues. If you need enterprise-class monitoring or you need a regular "pull" of data then yes, you need a third-party tool. If you're trying to troubleshoot one specific issue or just poking around trying to learn a bit about SQL Server internals on the side, this article just saved you $1300.

I wouldn't recommend building functionality on top of this function, for the reasons previously stated. Basically it is undocumented and therefore unsupported. It can also be dropped or its behavior/parameters/output changed without warning.
Post #1024752
Posted Tuesday, November 23, 2010 12:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 26, 2014 9:20 PM
Points: 6, Visits: 142
Oh, for that matter, it works with 2008 r2 and Denali CTP1.

dbcc page may be undocumented; but it is there for now--maybe not in the future, but today it works.
.
This article is helpful and the general idea is that it could keep your caboose from falling off the tracks. (scil. it has saved my *** before.)
Post #1024883
Posted Tuesday, November 23, 2010 4:54 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:49 PM
Points: 109, Visits: 585
Kraig Kerr (11/22/2010)
Looks like TDWI took down that link. Here is another that points to the same article. http://findarticles.com/p/articles/mi_m0EIN/is_2000_Nov_8/ai_66696495/

The current flavor of this works with SQL 2008, but with minor changes will work with SQL 2005. I also have a version for SQL 2000. I should have specified this in the article, thanks for the catch.


After reading this, how did you make the jump to reading the transaction log as documented in your article? Are you trying to use the transaction log for some sort of transactional-replication-like data movement tool?
Post #1025033
Posted Tuesday, November 23, 2010 5:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
I was wondering the same thing when I saw the throughput -- 1,000 recs/min.
Post #1025077
Posted Tuesday, November 23, 2010 6:39 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
It's definitely not useless data. It's very useful. It just needs some caveats, which it has. It's a good article and obviously took a lot of work to put together.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1025116
Posted Tuesday, November 23, 2010 6:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:06 PM
Points: 312, Visits: 1,026
I don't believe it is useless and really appreciate the amount of work it has taken. But there are two caveats when it comes to using things like this, one the average production dba does not have time to research issues in such detail, which is why there are tools to do it (or calls to CSS), and two lot of people cannot and do not run scripts found on websites (no matter how well researched or well written) on production without some kind of trust or prior association with the author.No offence to this particular author but there are lot of scripts and lot of authors who write very custom scripts, scripts that will work in some situation/some environment very well but not so in others. So the statement that it saved someone $1300 or any amount of money may not necessarily be true either.
Post #1025132
Posted Tuesday, November 23, 2010 7:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
dma-669038 (11/23/2010)
No offence to this particular author but there are lot of scripts and lot of authors who write very custom scripts, scripts that will work in some situation/some environment very well but not so in others. So the statement that it saved someone $1300 or any amount of money may not necessarily be true either.


As I very clearly stated (and will probably do so again and again, I'm sure), if you are trying to (1) troubleshoot a one-off problem or (2) simply poking around SQL Server's internals trying to learn something you can (A) spend the $1300 big wallet mentioned for a tool you might use a few times or (B) you can poke around with fn_dblog for free. If you decide to poke around with fn_dblog for free, then you just saved big wallet's $1300.

Your argument about authors who write scripts that work in some situations/environments very well but not so in others doesn't seem to add anything to the argument. It goes without saying that the best way to protect yourself against malevolent scripts is to understand what the script is doing before you run it. The fact that the author is educating his readers about the functions he's using here provides a little more insight into what all those scripts you see out there are doing.

I'm not sure I understand your assertions, unless you were just responding to a post without bothering to follow the thread to get a little bit of context.
Post #1025149
Posted Tuesday, November 23, 2010 7:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:06 PM
Points: 312, Visits: 1,026
The author himself providing clarifications on where the script works and where it doesn't is not enough for his/her scripts to be considered trustworthy to be run on a production environment (atleast where i work my boss would not take that for an answer). I do not mean that as anything personal against the author but such trust is built over time from blogging repeatedly (one way), and prior experience perhaps from using something same author did.

The situation 1 you describe is exactly what I was referring to, a one off emergency may happen that may need use of such scripts. The average dba cannot just predict such situations happening and when they happen cannot just use a script he/she finds. We need third party supported tools exactly for this reason. Thanks.
Post #1025158
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse