SQL 2005 transaction repl verbose logging location
in [Replication]
|
From: Chuck Lathrope on 15 Feb 2007 16:48 On Thu, 15 Feb 2007 11:02:07 -0800, Raymond Mak [MSFT] wrote: > From: "Raymond Mak [MSFT]" <rmak(a)online.microsoft.com> > References: <150t3cmvxgxb5$.1qai2jtvugwco.dlg(a)40tude.net> > Subject: Re: SQL 2005 transaction repl verbose logging location > Date: Thu, 15 Feb 2007 11:02:07 -0800 > Lines: 22 > X-Priority: 3 > X-MSMail-Priority: Normal > X-Newsreader: Microsoft Outlook Express 6.00.2900.3028 > X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 > X-RFC2646: Format=Flowed; Original > Message-ID: <#CvRKPTUHHA.4668(a)TK2MSFTNGP04.phx.gbl> > Newsgroups: microsoft.public.sqlserver.replication > NNTP-Posting-Host: tide532.microsoft.com 131.107.0.102 > Path: TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl > Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.replication:78774 > > Chuck, I've got a couple of questions for you: > > 1) Did you get the uniqueness violation during the initial bulk-load at the > subscriber? (Sounds like it but I want to make sure) > 2) Did you start off with an empty subscriber database or did you use "drop" > as the pre-creation command for your articles? > > -Raymond > > "Chuck Lathrope" <computerguy_chuck(a)hotmail.com> wrote in message > news:150t3cmvxgxb5$.1qai2jtvugwco.dlg(a)40tude.net... >>I have primary key duplication issues on brand new snapshot of data, where >> subscriber is just a read-only reporting server. I am trying to turn on >> verbose history agent profile, but don't know specifics of doing this. Do >> I >> do it on the distribution agent or at the subscriber? Also, where does it >> store the information? >> >> Thanks, >> Chuck The violation happened after the initial bulk-load after the indexes were created. The subscriber database has other content in it, and I didn't manually verify tables were empty as typically it does a drop table when applying the snapshot. For those steps, I am just using the GUI. I will try deleting subscription again and verify all tables are dropped myself to see if that helps. -Chuck
From: Raymond Mak [MSFT] on 15 Feb 2007 17:51 Do you have any triggers at the subcriber not marked "NOT FOR REPLICATION" by any chance? -Raymond "Chuck Lathrope" <computerguy_chuck(a)hotmail.com> wrote in message news:wwd5upo8vn5o.3gui3z0792zb.dlg(a)40tude.net... > On Thu, 15 Feb 2007 11:02:07 -0800, Raymond Mak [MSFT] wrote: > >> From: "Raymond Mak [MSFT]" <rmak(a)online.microsoft.com> >> References: <150t3cmvxgxb5$.1qai2jtvugwco.dlg(a)40tude.net> >> Subject: Re: SQL 2005 transaction repl verbose logging location >> Date: Thu, 15 Feb 2007 11:02:07 -0800 >> Lines: 22 >> X-Priority: 3 >> X-MSMail-Priority: Normal >> X-Newsreader: Microsoft Outlook Express 6.00.2900.3028 >> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 >> X-RFC2646: Format=Flowed; Original >> Message-ID: <#CvRKPTUHHA.4668(a)TK2MSFTNGP04.phx.gbl> >> Newsgroups: microsoft.public.sqlserver.replication >> NNTP-Posting-Host: tide532.microsoft.com 131.107.0.102 >> Path: TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl >> Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.replication:78774 >> >> Chuck, I've got a couple of questions for you: >> >> 1) Did you get the uniqueness violation during the initial bulk-load at >> the >> subscriber? (Sounds like it but I want to make sure) >> 2) Did you start off with an empty subscriber database or did you use >> "drop" >> as the pre-creation command for your articles? >> >> -Raymond >> >> "Chuck Lathrope" <computerguy_chuck(a)hotmail.com> wrote in message >> news:150t3cmvxgxb5$.1qai2jtvugwco.dlg(a)40tude.net... >>>I have primary key duplication issues on brand new snapshot of data, >>>where >>> subscriber is just a read-only reporting server. I am trying to turn on >>> verbose history agent profile, but don't know specifics of doing this. >>> Do >>> I >>> do it on the distribution agent or at the subscriber? Also, where does >>> it >>> store the information? >>> >>> Thanks, >>> Chuck > > The violation happened after the initial bulk-load after the indexes were > created. The subscriber database has other content in it, and I didn't > manually verify tables were empty as typically it does a drop table when > applying the snapshot. For those steps, I am just using the GUI. > > I will try deleting subscription again and verify all tables are dropped > myself to see if that helps. > > -Chuck
From: Chuck Lathrope on 15 Feb 2007 19:26 On Thu, 15 Feb 2007 14:51:39 -0800, Raymond Mak [MSFT] wrote: > Do you have any triggers at the subcriber not marked "NOT FOR REPLICATION" > by any chance? > > -Raymond > > "Chuck Lathrope" <computerguy_chuck(a)hotmail.com> wrote in message > news:wwd5upo8vn5o.3gui3z0792zb.dlg(a)40tude.net... >> On Thu, 15 Feb 2007 11:02:07 -0800, Raymond Mak [MSFT] wrote: >> >>> From: "Raymond Mak [MSFT]" <rmak(a)online.microsoft.com> >>> References: <150t3cmvxgxb5$.1qai2jtvugwco.dlg(a)40tude.net> >>> Subject: Re: SQL 2005 transaction repl verbose logging location >>> Date: Thu, 15 Feb 2007 11:02:07 -0800 >>> Lines: 22 >>> X-Priority: 3 >>> X-MSMail-Priority: Normal >>> X-Newsreader: Microsoft Outlook Express 6.00.2900.3028 >>> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 >>> X-RFC2646: Format=Flowed; Original >>> Message-ID: <#CvRKPTUHHA.4668(a)TK2MSFTNGP04.phx.gbl> >>> Newsgroups: microsoft.public.sqlserver.replication >>> NNTP-Posting-Host: tide532.microsoft.com 131.107.0.102 >>> Path: TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl >>> Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.replication:78774 >>> >>> Chuck, I've got a couple of questions for you: >>> >>> 1) Did you get the uniqueness violation during the initial bulk-load at >>> the >>> subscriber? (Sounds like it but I want to make sure) >>> 2) Did you start off with an empty subscriber database or did you use >>> "drop" >>> as the pre-creation command for your articles? >>> >>> -Raymond >>> >>> "Chuck Lathrope" <computerguy_chuck(a)hotmail.com> wrote in message >>> news:150t3cmvxgxb5$.1qai2jtvugwco.dlg(a)40tude.net... >>>>I have primary key duplication issues on brand new snapshot of data, >>>>where >>>> subscriber is just a read-only reporting server. I am trying to turn on >>>> verbose history agent profile, but don't know specifics of doing this. >>>> Do >>>> I >>>> do it on the distribution agent or at the subscriber? Also, where does >>>> it >>>> store the information? >>>> >>>> Thanks, >>>> Chuck >> >> The violation happened after the initial bulk-load after the indexes were >> created. The subscriber database has other content in it, and I didn't >> manually verify tables were empty as typically it does a drop table when >> applying the snapshot. For those steps, I am just using the GUI. >> >> I will try deleting subscription again and verify all tables are dropped >> myself to see if that helps. >> >> -Chuck I do have triggers that I create manually after replication snapshot is complete and are not marked "not for replication", but they only copy data into other logging tables, they don't modify or add to the replicated tables. Looking at dbcc inputbuffer, it is in process of creating non-clustered indexes and replication monitor's latest entries are: The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active. Delivering replicated transactions Delivered snapshot from the 'unc\ACCOUNTING\20070215142475\' sub-folder in 3361875 milliseconds All the create statements below for snapshot... -Chuck
From: Chuck Lathrope on 15 Feb 2007 23:14
On Thu, 15 Feb 2007 16:26:54 -0800, Chuck Lathrope wrote: > On Thu, 15 Feb 2007 14:51:39 -0800, Raymond Mak [MSFT] wrote: > >> Do you have any triggers at the subcriber not marked "NOT FOR REPLICATION" >> by any chance? >> >> -Raymond >> >> "Chuck Lathrope" <computerguy_chuck(a)hotmail.com> wrote in message >> news:wwd5upo8vn5o.3gui3z0792zb.dlg(a)40tude.net... >>> On Thu, 15 Feb 2007 11:02:07 -0800, Raymond Mak [MSFT] wrote: >>> >>>> From: "Raymond Mak [MSFT]" <rmak(a)online.microsoft.com> >>>> References: <150t3cmvxgxb5$.1qai2jtvugwco.dlg(a)40tude.net> >>>> Subject: Re: SQL 2005 transaction repl verbose logging location >>>> Date: Thu, 15 Feb 2007 11:02:07 -0800 >>>> Lines: 22 >>>> X-Priority: 3 >>>> X-MSMail-Priority: Normal >>>> X-Newsreader: Microsoft Outlook Express 6.00.2900.3028 >>>> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 >>>> X-RFC2646: Format=Flowed; Original >>>> Message-ID: <#CvRKPTUHHA.4668(a)TK2MSFTNGP04.phx.gbl> >>>> Newsgroups: microsoft.public.sqlserver.replication >>>> NNTP-Posting-Host: tide532.microsoft.com 131.107.0.102 >>>> Path: TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl >>>> Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.replication:78774 >>>> >>>> Chuck, I've got a couple of questions for you: >>>> >>>> 1) Did you get the uniqueness violation during the initial bulk-load at >>>> the >>>> subscriber? (Sounds like it but I want to make sure) >>>> 2) Did you start off with an empty subscriber database or did you use >>>> "drop" >>>> as the pre-creation command for your articles? >>>> >>>> -Raymond >>>> >>>> "Chuck Lathrope" <computerguy_chuck(a)hotmail.com> wrote in message >>>> news:150t3cmvxgxb5$.1qai2jtvugwco.dlg(a)40tude.net... >>>>>I have primary key duplication issues on brand new snapshot of data, >>>>>where >>>>> subscriber is just a read-only reporting server. I am trying to turn on >>>>> verbose history agent profile, but don't know specifics of doing this. >>>>> Do >>>>> I >>>>> do it on the distribution agent or at the subscriber? Also, where does >>>>> it >>>>> store the information? >>>>> >>>>> Thanks, >>>>> Chuck >>> >>> The violation happened after the initial bulk-load after the indexes were >>> created. The subscriber database has other content in it, and I didn't >>> manually verify tables were empty as typically it does a drop table when >>> applying the snapshot. For those steps, I am just using the GUI. >>> >>> I will try deleting subscription again and verify all tables are dropped >>> myself to see if that helps. >>> >>> -Chuck > > I do have triggers that I create manually after replication snapshot is > complete and are not marked "not for replication", but they only copy data > into other logging tables, they don't modify or add to the replicated > tables. > > Looking at dbcc inputbuffer, it is in process of creating non-clustered > indexes and replication monitor's latest entries are: > > The replication agent has not logged a progress message in 10 minutes. This > might indicate an unresponsive agent or high system activity. Verify that > records are being replicated to the destination and that connections to the > Subscriber, Publisher, and Distributor are still active. > > Delivering replicated transactions > > Delivered snapshot from the 'unc\ACCOUNTING\20070215142475\' sub-folder in > 3361875 milliseconds > > All the create statements below for snapshot... > > -Chuck Ok, I did find a trigger that was causing an issue on a remote table - we replicate data and have the other server log changes via triggers. Thanks, Chuck |