Which transaction log am I using?

• Dave-3000

  SSCertifiable

  Points: 6186

  More actions

  April 4, 2007 at 10:54 am

  #116836

  I am plowing through a data warehouse load script, and this statement is executed in a script on SQL Server 2000 #1:

  INSERT

  INTO LinkedServer2.WAREHOUSE.dbo.Invoice

  SELECT

  * FROM LinkedServer3.IDX_LIVE.dbo.TEMP_Invoice WHERE GROUP_ZZ IN (4, 24, 16, 18, 24)

  Where is the insert logged?  My guess would be on Server 1, since that's where it's run.  Any ideas?

  And Temp_Invoice is a 30 million row table.

  There is no "i" in team, but idiot has two.

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  April 4, 2007 at 12:14 pm

  #698626

  The INSERT operation will be logged in the transaction log for the database where the data is being INSERTED, in this case LinkedServer2.

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  April 4, 2007 at 3:10 pm

  #698658

  SELECT statements do not cause activity in the transaction log (it's not a transaction).

  INSERT statements do cause activity in the transaction log (it is a transaction).

  So, SERVER3 is being used for a SELECT - no tlog usage. SERVER2 is being used for an INSERT - its tlog is being used.

  -SQLBill

• Dave-3000

  SSCertifiable

  Points: 6186

  More actions

  April 5, 2007 at 9:45 am

  #698837

  OK, that makes sense.  Thanks.

  There is no "i" in team, but idiot has two.