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 Saturday, November 20, 2010 2:06 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
Comments posted to this topic are about the item Reading SQL Server's Transaction Log
Post #1023967
Posted Sunday, November 21, 2010 6:23 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, March 02, 2014 4:34 PM
Points: 724, Visits: 1,001
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
Post #1024111
Posted Sunday, November 21, 2010 6:55 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
Do the tables in question have any clustered indexes? If not you'd be dealing with "Heaps" and your only recourse would be to run a whole lot of DBCC Page commands or explore using %%phyloc%%. [url=http://sqlfool.com/2009/09/undocumented-function-in-sql-2008/][/url]

Also dealing with updates is a lot more complex than dealing with inserts.
Post #1024118
Posted Monday, November 22, 2010 4:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 08, 2012 4:45 AM
Points: 26, Visits: 46
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,
Post #1024230
Posted Monday, November 22, 2010 5:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 12,744, Visits: 31,069
I 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.
faboudib (11/22/2010)
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,


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 #1024255
Posted Monday, November 22, 2010 6:58 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'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.
Post #1024304
Posted Monday, November 22, 2010 7:38 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 2:46 PM
Points: 79, Visits: 377
Nice Article.

Thank you for taking the time to lay this out.


Francis
-----------------
SQLRanger.com
Post #1024333
Posted Monday, November 22, 2010 7:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 01, 2011 9:38 AM
Points: 1, Visits: 9
Very nice article ! Waiting the next one now ;)
Post #1024335
Posted Monday, November 22, 2010 8:10 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
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.


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.
Post #1024374
Posted Monday, November 22, 2010 8:17 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
The 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?
Post #1024380
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse