dbcc traceon (8207,-1)
DBCC TRACESTATUS(-1)
On This Page
SUMMARY
If you update any column that is part of a unique or clustered index, SQL Server...
Back to the top
MORE INFORMATION
You can enable trace flag 8207 on the publishing server by using either of the f...
- Run the following code from the SQL Server Query Analyzer:
NOTE: You must repeat this step every time you stop and restart SQL Server.
DBCC TRACEON (8207, -1)
-or-
- If you want to have the 8207 trace flag set ON permanently, then:
- Stop the MSSQLServer service.
- Add -T8207 to the startup parameters. To add -T8207, from SQL Enterprise Manager:
- Right-click the server name, and then click Properties.
- In the Properties dialog box, click the General tab, and then clickStartup Parameters.
- Add the trace flag.
- Start the MSSQLServer service.
Important: Typically, you use trace flag 8207 with read-only transactional replication. Do not use trace flag 8207 with updatable subscriptions if:
- A primary key update can occur at the subscriber.
- An update to a column that is included in a unique constraint can occur at the subscriber.
- An update to a column that is included in a unique index can occur at the subscriber.
REFERENCES
For more information, refer to the "Trace Flags" and the "Using Startup Options" topics in SQL Server Books Online. Keywords: | kbbug kbfix kbinfo kbsqlserv2000sp1fix KB302341 |
-
Wednesday, October 10, 2007 10:20 AM
Hi all,
I had transactional replication model relying on SQL Server 2000 , and when i had had missing records on subscriber i just did UPATE on primary key on that table and that transactions moved from publisher to subscriber again, because update to primary key in SQL 2000 generates DELETE command and then INSERT command
,anyway,
Now I am using SQL Server 2005 and that hint doesn't work, i think that some king of optimization has been made to Log reader agent in SQL 2005, i don't know .
Can Someone tell me how to repeat transactions in the same or similar way as SQL 2000.
Answers
-
Monday, January 07, 2008 4:28 PMHilary Cotter
Hilary Cotter
MVP
Recent AchievementsForums Replies V Proposed Answerer I Forums Answerer IV(MVP)26,945 Points
That is correct, in SQL 2005 the update will not be deferred (ie split into a delete/insert pair).
All Replies
-
Friday, October 12, 2007 6:29 PMHilary Cotter
Hilary Cotter
MVP
Recent AchievementsForums Replies V Proposed Answerer I Forums Answerer IV(MVP)26,945 Pointsuse trace flag 8202
-- here is a repro
dbcc traceon(8202,-1)
create database testupdatepk
GO
create database testupdatepkSub
GO
use testupdatepk
GO
sp_replicationdboption 'testupdatepk','publish','true'
GO
create table updatepk(pk int not null primary key, charcol char(20))
GO
sp_addpublication 'testupdatepk',@status='active'
GO
sp_addpublication_snapshot 'testupdatepk'
GO
sp_addarticle 'testupdatepk','updatepk','updatepk'
GO
sp_addsubscription 'testupdatepk','updatepk',@@servername,'testupdatepksub'
GO
exec sys.sp_startpublication_snapshot @publication = 'testupdatepk'
GO
select *From testupdatepksub.dbo.updatepk
--its there, add a row
insert into updatepk values(1, getdate())
--its there, add a row
select *From testupdatepksub.dbo.updatepk
update updatepk set pk=2
--commands issued are below - captured via profiler
--exec [sp_MSdel_dboupdatepk] 1
--exec [sp_MSins_dboupdatepk] 2,'Oct 12 2007 2:22PM '
-
We are currently using SQL Server 2000 transactional replication but the db will be migrated to 2005 in the near future.
Is it correct that an update on a primary key in a SQL Server 2000 table being replicated (transactional) results in a Delete/Insert pair of transactions? But an update on a primary key in a SQL Server 2005 table being replicated (transactional) is simply an Update transaction?
Thank you.
-
Monday, January 07, 2008 4:28 PMHilary Cotter
Hilary Cotter
MVP
Recent AchievementsForums Replies V Proposed Answerer I Forums Answerer IV(MVP)26,945 Points
That is correct, in SQL 2005 the update will not be deferred (ie split into a delete/insert pair).
-
I think I misunderstood the answer earlier. I thought what was being said is that in SQL Server 2005 deferred updates were not possible.
I've now read that in SQL Server 2005 a deferred update is made into a singleton update by turning on the trace flag, using the DBCC traceon (8201, -1) command on the publication database.
That indicates that in 2005 you may make a decision to either turn the deferred update into a singleton OR leave as deferred update.
dbcc traceon(8202,-1) is for deferred updates
dbcc traceon(8201,-1) is for singleton updates
Is that correct?If set for deferred updates, would all updates (either to PK column or any other columns) be paired insert/deletes or only updates to PK column?
Any knowledge or information on this topic, is appreciated.
-
Friday, January 25, 2008 6:04 PM972 Pointsgopal ashok
gopal ashok
Recent AchievementsNew Blog Commentator New Blogger First Marked AnswerIn SQL Server 2005, the default behavior is deferred updates will be send as updates except in couple of cases where it will be send as del\ins pair and there is no way to force this into singleton updates.
1: Update PK of a single from a join
2: Update PK of more than one row in a single statement
In SQL Server 2005, 8201 really do not have any impact, 8202 on the other hand will replicate ALL updates as deferred updates.
-
Friday, January 25, 2008 6:07 PM972 Pointsgopal ashok
gopal ashok
Recent AchievementsNew Blog Commentator New Blogger First Marked AnswerMeant to say...
In SQL Server 2005, 8201 really do not have any impact, 8202 on the other hand will replicate ALL updates as del\ins pairs -
I think I understand now.
In SQL 2000, updates to columns that do not participate in PK are treated as singleton (update) unless trace changed to 8202 after which they are treated as deferred (paired ins/delete), while updates to columns that participate in PK are ALWAYS treated as deferred (paired ins/del).
In SQL 2005, its either all updates are treated as defered (paired ins/del) or all are treated as singleton (update) regardless of if column(s) being updated participate in PK or not.
-
Friday, January 25, 2008 7:02 PM972 Pointsgopal ashok
gopal ashok
Recent AchievementsNew Blog Commentator New Blogger First Marked AnswerIn SQL 2000, updates to columns that do not participate in PK are treated as singleton (update) unless trace changed to 8202 after which they are treated as deferred (paired ins/delete), while updates to columns that participate in PK are ALWAYS treated as deferred (paired ins/del).
-- Correct
In SQL 2005, its either all updates are treated as defered (paired ins/del)
-- only if you use 8202
or all are treated as singleton (update) regardless of if column(s) being updated participate in PK or not.
-- yes, this is the default behavior except for the cases i mentioned above.
-
Thursday, January 31, 2008 9:58 PM
Hello!
I'm running two kind of replications one that is peer-to-peer merge replication and the latter 's subscribers are as well publishers via transactional replication to a central database.
The transaction replication uses XCALL facility (which is overriden to provide particular information for the datawarehouse)
However, the log reader is applying del/ins to some tables (8 tables out of 150). updates to primary keys are occuring but they are singleton updates.
Anyone has a clue of why this weird (unwanted) behavior is taking place?
I've tried:
-dbcc opentran to check for open logs and did not find any.
-dbcc traceon(8207,-1) which is a solution for SQL 2000 and as stated above SQL 2005 default behavior allows for singleton updates.
- "enforce for replication" on integrity relationship is disabled.
- no text or any other LOB data type is being replicated.
what is it that i'm missing?
publishers/distributors are running on SQL 2005, some with SP1
subscriber is running SQL 2005 Enterprise Edition with SP2 installed in a cluster environment
note: merge publishers drop existing objects then replicates thru transactional replication!
具体参考：
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q302/3/41.ASP&NoWebContent=1
http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/7d1b8e01-f372-4d67-b213-5576345e71ec
扫一扫
389
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
