InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 23:30:40 GMT20http://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxNice article.Mon, 24 Dec 2012 12:15:58 GMTNeha05http://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxVery nice article! Very informative and well written.Would use that trick in production? Probably not, but that doesn't subtract value from a nice piece of work.Fri, 09 Nov 2012 11:35:59 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxnice topic i hsd never seen,,,very rich in sence of matterFri, 09 Nov 2012 05:14:41 GMTsatheesshhttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxHi Kraig,I find your article very intriguing and useful.We have a replication process that at some point during a week; well, either does not insert a record or it is partial. And we would like to see what's going on.Do you have a piece that strictly deals with reading the errors rather than what was inserted?Or, perhaps you can put me on the right direction by means of modifying your existing code, that's as if I have understood that completely ;-), or if you happen to have that piece.. either case I appreciate your assistance as I am stuck between a rock and a hard place.thx in advance and please keep posting the good stuff.. loving it..Tue, 03 May 2011 16:15:54 GMTJohn Esraelo-498130http://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspx[quote][b]Lowell (11/23/2010)[/b][hr]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 [b]without a WHERE [/b]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.[/quote]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.Sat, 18 Dec 2010 23:42:42 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspx[quote][b]fat wallet (11/22/2010)[/b][hr]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.[/quote]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.Fri, 17 Dec 2010 14:13:21 GMTKraig Kerrhttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxNicely 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,RudyFri, 03 Dec 2010 08:56:08 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxWell, 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.Tue, 23 Nov 2010 11:26:15 GMTdma-669038http://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspx[quote][b]Phil Brammer (11/23/2010)[/b][hr]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.[/quote]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.Tue, 23 Nov 2010 11:02:35 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxIf 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.Tue, 23 Nov 2010 10:46:05 GMTPhil Brammerhttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxa 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 [b]without a WHERE [/b]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.Tue, 23 Nov 2010 10:37:15 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxSeeing 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.Tue, 23 Nov 2010 08:27:51 GMTdma-669038http://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspx[quote]Fat WalletFrom 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. [/quote]I would hope that you climb down off your perch to the level of many other [b]Preofessional DBA's[/b] 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.Tue, 23 Nov 2010 08:24:23 GMTsjimmohttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxAlso 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).Tue, 23 Nov 2010 07:35:37 GMTdma-669038http://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxThe 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.Tue, 23 Nov 2010 07:27:14 GMTdma-669038http://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspx[quote][b]dma-669038 (11/23/2010)[/b][hr]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. [b]So the statement that it saved someone $1300 or any amount of money may not necessarily be true either.[/b][/quote]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 [i]big wallet[/i] 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 [i]big wallet's[/i] $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.Tue, 23 Nov 2010 07:17:10 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxI 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. Tue, 23 Nov 2010 06:57:57 GMTdma-669038http://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxIt'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.Tue, 23 Nov 2010 06:39:34 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxI was wondering the same thing when I saw the throughput -- 1,000 recs/min.Tue, 23 Nov 2010 05:52:24 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspx[quote][b]Kraig Kerr (11/22/2010)[/b][hr]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.[/quote]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?Tue, 23 Nov 2010 04:54:00 GMTPhil Brammerhttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxOh, 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.)Tue, 23 Nov 2010 00:41:10 GMTmareedshttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspx[quote][b]fat wallet (11/22/2010)[/b][hr]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.[/quote]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.Mon, 22 Nov 2010 15:41:58 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxFrom 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.Mon, 22 Nov 2010 15:26:33 GMTfat wallethttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspx[quote][b]Mike C (11/22/2010)[/b][hr]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.[/quote]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 [u]tools[/u] 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”.Mon, 22 Nov 2010 14:48:32 GMTKraig Kerrhttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxfn_dblog changed slightly between SQL 2005 and SQL 2008. This was, of course, without notification or documentation. If I remember correctly, it changed from 2000 to 2005 as well. I don't know if it's changed from 2008 to 2008 R2, but it could have. Also haven't checked Denali (SQL v 11) yet.I've used it to test for whether a log backup was needed on databases that have intermittently high activity in long periods of no activity, comparing LUNs in the log to data in the backup history in msdb. Even then, I wouldn't use it in anything where an error message mattered much. Too much chance of it changing again.Mon, 22 Nov 2010 13:58:01 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspx[quote][b]TRACEY-320982 (11/21/2010)[/b][hr]With this information would you be able to replicate the data to another database. Most of the software we use does not have primary keys so SQL Replication is not an option.Mirroring, snapshots not an option as the reporting database needs to be up 24 /7 and not down whilst snapshots run or log transactions are built on the reporting database.Just curious[/quote]Not a good idea to build production functionality on undocumented features.Mon, 22 Nov 2010 13:06:56 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxHi KraigThank you very much for very useful article regarding transaction log reading. I have a question about update and delete commands - how we can handle these operations?ThanksMon, 22 Nov 2010 12:19:26 GMTg.korothttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxNot sure. It was folded into IBM and I haven't seen any other information about their log reading tech.Mon, 22 Nov 2010 11:52:18 GMTKraig Kerrhttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxThought so. They still doing data warehousing or just the identity stuff?Mon, 22 Nov 2010 11:46:56 GMTrbasketthttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxSystems Research & Development (SRD) founded by Jeff Jonas.Mon, 22 Nov 2010 11:44:32 GMTKraig Kerrhttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxLooks 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.Mon, 22 Nov 2010 11:42:57 GMTKraig Kerrhttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxSRD?Mon, 22 Nov 2010 11:03:43 GMTrbasketthttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxYes that was my thought too, lot of very detailed research and very well written, but what practical use would you put this to?Mon, 22 Nov 2010 08:29:27 GMTdma-669038http://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxDBCC PAGE is undocumented as well. Use at your own risk.Mon, 22 Nov 2010 08:21:23 GMTPhil Brammerhttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxThe link to the MGM Mirage article does not work. I'm still trying to decifer what your reasons for reading the log are. Also, what version of SQL are you using for this demo?Mon, 22 Nov 2010 08:17:28 GMTPhil Brammerhttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspx[quote]However, if our table had been a HEAP this method works nicely because the data remains with the [Page ID] and [Slot ID] for the life of the HEAP.[/quote]That's not quite correct. Data will move in heaps and hence change page numbers. Updates to rows that cause the row to no longer fit on the page will cause it to move. When this happens a forwarding pointer is left in the original page, pointing to the new page. ALTER TABLE ... REBUILD will also renumber your pages and allocation_unit_ids.Mon, 22 Nov 2010 08:10:42 GMTPhil Brammerhttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxVery nice article ! Waiting the next one now ;)Mon, 22 Nov 2010 07:40:28 GMTknoopixhttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxNice Article.Thank you for taking the time to lay this out.Mon, 22 Nov 2010 07:38:59 GMTFrancis Apelhttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxI'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.Mon, 22 Nov 2010 06:58:09 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic1023967-2843-1.aspxI believe the target schema is 2008; it worked just fine for me...the object id of my table was actually exactly the same as the article.in 2005 Express, i get a couple of minor syntax issues, but after i look deeper, i'm sure it'll be obvious what to tweak.Thanks for the great article.[quote][b]faboudib (11/22/2010)[/b][hr]Nice article,However, would you mind specifying on which versions of Sql Server this works? If this works only on Sql Server 2000, and that is your target platform, you might want to look into RedGate's free product SQL Log Rescue (http://www.red-gate.com/products/SQL_Log_Rescue/index.htm) for similar functioanlity. Otherwise great work...Regards,[/quote]Mon, 22 Nov 2010 05:51:02 GMTLowell