복제::머지복제에러

FIX: Error message when you try to insert data on a subscriber of a merge replication in SQL Server 2005: "Msg 548, Level 16, State 2, Line 1. The insert failed"

 

 

Bug #: 50002854 (SQL Hotfix)

Microsoft distributes Microsoft SQL Server 2005 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release.

On This Page

SYMPTOMS

Consider the following scenario. In SQL Server 2005, you configure a merge publication. You add a table that contains an identity column to the merge publication. Then, you insert data into the table on the publisher. You synchronize the data between the subscriber and the publisher, and then you try to insert additional data on the publisher. In this scenario, you receive the following error message on the publisher:

Msg 548, Level 16, State 2, Line 1
The insert failed. It conflicted with an identity range check constraint in database 'DatabaseName', replicated table 'Schema.TableName', column 'ColumnName'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.

If you try to run the sp_adjustpublisheridentityrange stored procedure on the publisher as mentioned in the error message, you still cannot resolve this problem.

This problem occurs when multiple Merge Agents synchronize data at the same time for the same merge publication. This problem can be exacerbated if you have many subscribers to the merge publication.

Back to the top

CAUSE

This problem occurs because the current identity value of the table on the publisher is not within the identity range of the identity range check constraint. This behavior occurs when multiple Merge Agents try to increase the identity range at the same time by creating the next identity range on the publisher.

Back to the top

RESOLUTION

The fix for this issue was first released in Cumulative Update 8. For more information about how to obtain this cumulative update package for SQL Server 2005 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:

951217 (http://kbalertz.com/Feedback.aspx?kbNumber=951217/) Cumulative update package 8 for SQL Server 2005 Service Pack 2

Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

937137 (http://kbalertz.com/Feedback.aspx?kbNumber=937137/) The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2 was released

Microsoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 2 hotfix to an installation of SQL Server 2005 Service Pack 2. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

Back to the top

WORKAROUND

To work around this problem, you must prevent multiple concurrent merge synchronizations. To do this, set the max_concurrent_merge property on the merge publication by running the following statement:

sp_changemergepublication '<PublicationName>', 'max_concurrent_merge', 1

Note After you use this workaround, performance may decrease if you have many subscribers for the publication. This behavior occurs because only one subscriber can synchronize data at a time.

Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Back to the top

MORE INFORMATION

How to determine whether you are experiencing this problem

To determine whether you are experiencing this problem, follow these steps:

1.	Verify that the current identity value is smaller than the lower bound of the first identity range of the identity range check constraint. To obtain the current identity value, run the following statement: SELECT IDENT_CURRENT ('<TableName>') To obtain the identity ranges of the identity range check constraint, run either of the following statements: Statement 1 sp_helpconstraint '<TableName>' Statement 2 select * from MSmerge_identity_range where is_pub_range <>1 AND artid IN (select artid from sysmergearticles where name='<TableName>') AND subid in (select subid from sysmergesubscriptions MS join sysmergepublications MP on MS.subscriber_server=MP.publisher AND MS.db_name = MP.publisher_db WHERE name='<PublicationName>' )
2.	Use a SQL Server Profiler trace to determine whether interleaved executions of the sp_MSsetup_publisher_idrange stored procedure are started by separate Merge Agent sessions for the same publication. However, interleaved executions of the sp_MSsetup_publisher_idrange stored procedure do not always indicate that you are experiencing this problem. This is because SQL Server Profiler was not running when the original occurance of the merge synchronization generated the first error message. However, interleaved executions of the sp_MSsetup_publisher_idrange stored procedure do increase the possibility of experiencing this problem.
3.	You can find overlapping merge synchronizations that occur when you receive the "548" error message occurs. To do this, you can review the merge history in the distribution database. To do this, run the following statements: Use distribution GO select session_id, agent_id, B.publication, min(time) as StartTime, max(time) as EndTime into #sessiontimes from dbo.MSmerge_history A join dbo.msmerge_agents B on A.agent_id = B.id group by session_id, agent_id, publication order by 3 desc GO -- The left side result is the original session. The right side result is the overlapping session. select A.*, B.* from #sessiontimes A Join #sessiontimes B On B.StartTime >= A.StartTime AND B.StartTime <= A.EndTime AND A.session_id <> B.session_id And A.publication=B.publication Order By A.StartTime asc GO drop table #sessiontimes

Back to the top

How to correct existing damaged identity ranges for a problematic table

After you install the cumulative update or after you use the method that is described in the workaround section, an existing damaged identity range in a table is not corrected. You will still receive the "548" error message if you try to insert data into the table and synchronize data on a subscriber. Therefore, you must manually correct the damaged identity ranges for the table. To do this, follow these steps.

Note These steps involve manually overriding the current identity seed value for the table to correctly reseed the identity value at the publisher. In the damaged state, the current identity value is smaller than the first identity range in the merge replication identity range check constraint. The steps manually raise the identity value to fall inside the identity range that is defined by the merge replication identity range check constraint. These steps assume that identities are configured in an ascending manner and that the identity increment is configured to increase by a value of 1.

1.	Confirm that the identity increment value is 1 and that the identity proceeds in ascending manner. You can obtain the identity increment value by running the following statement on the publisher: SELECT IDENT_INCR( '<TableName>')
2.	Run the following statement on the publisher to find the current identity value on the problematic identity column: DBCC CHECKIDENT ('<TableName>') After you receive the result, note the current identity value value for comparison in the later steps. Notice that the current column value valye may be either larger or smaller than the current identity value value. If the current column value value is larger than the current identity value value, the column value may have originated at other replications in the topology and merged successfully with the publisher replication. If the current column value value is smaller than the current identity value value, the values may have been inserted on the publisher at a earlier time by using the SET IDENTITY_INSERT ON statement before the merge replication configuration.
3.	Run the following statements on the publisher to determine the current identity ranges of the identity range check constraint for the problematic table: Use <PublishedDatabaseName> GO sp_helpconstraint '<TableName>' GO After you receive the result, note the value of the constraint_keys column of the record where the value of the constraint_name column is "repl_identity_range_GUID". The GUID value corresponds to the value of the artid column for the article in the sysmergearticles system table. To obtain the GUID, run the following statement: select artid from sysmergearticles where name = '<TableName>' The identity range check constraint spans two separate ranges. The two sets of ranges do not have to be contiguous. For example, the value of the constraint_keys column can be as follows: ([ColumnName]>(1001) AND [ColumnName]<=(2001) OR [ColumnName] > (9001) AND [ColumnName]<=(10001)) Note This article uses this example to present code in the rest of steps. In this example, the ranges each span 1,000 values. 1,000 is the default range size. However, you can change the identity range size by using one of the following methods: • Specify the @pub_identity_range parameter when you run the sp_addmergearticle stored procedure. • Change the Subscriber range size property for the article in the Article Properties dialog box.
4.	If you experience the problem that is described in the "Symptoms" section, the current identity value that you noted in step 2 should be smaller than the lower bound of the first identity range of the identity range check constraint that you noted in step 3. If the current identity value in step 2 is larger than the upper bound of the second identity range of the identity range check constraint, resolve the problem by using the method that is recommended in the error message. Therefore, you should run the sp_adjustpublisheridentityrange stored procedure on the publisher. For more information about the sp_adjustpublisheridentityrange stored procedure, visit the following Microsoft Developer Network (MSDN) Web site: http://msdn.microsoft.com/en-us/library/ms181527.aspx (http://msdn.microsoft.com/en-us/library/ms181527.aspx)
5.	Run the following statement to determine whether any rows are in the current identity ranges of the identity range check constraint: SELECT COUNT(*) FROM TableName WHERE ([ColumnName]>(1001) AND [ColumnName]<=(2001) OR [ColumnName]>(9001) AND [ColumnName]<=(10001)) Notes • If the statement returns 0, no rows are in the current identity ranges. In this case, go to step 6. • If the statement returns a value that is larger than 0, run the following statement to obtain the maximum identity value in the current identity ranges: SELECT MAX(ColumnName) as MaxValue FROM TableName WHERE ([ColumnName]>(1001) AND [ColumnName]<=(2001) OR [ColumnName]>(9001) AND [ColumnName]<=(10001)) Note the returned value, and then go to step 7.
6.	Manually reseed the current identity for the problematic table to fall inside a valid range. Reseed the current identity to the lowest value of the current identity ranges plus 1. For example, if the lowest value of the current identity ranges is 1001, the first possible in-range value is 1002 because the low end of the range of the identity range check constraint uses the greater than sign (>). To do this, run the following statement on the publisher, and then go to step 8: DBCC CHECKIDENT ('TableName', RESEED, 1002)
7.	Manually reseed the current identity for the problematic table to fall inside a valid range. Assume that the identity increment is 1. Reseed the current identity to the value that you noted in step 5, and then add 1. For example, if the value that you noted in step 5 is 1507, reseed the current identity to 1508. To do this, run the following statement on the publisher: DBCC CHECKIDENT ('TableName', RESEED, 1508)
8.	Perform a test to determine whether new rows can be inserted into the table in the publisher database without error 548 occurring.

Back to the top

For more information about what files are changed, and for information about any prerequisites to apply the cumulative update package that contains the hotfix that is described in this Microsoft Knowledge Base article, click the following article number to view the article in the Microsoft Knowledge Base:

951217 (http://kbalertz.com/Feedback.aspx?kbNumber=951217/) Cumulative update package 8 for SQL Server 2005 Service Pack 2

Back to the top

REFERENCES

For more information about the list of builds that are available after SQL Server Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:

937137 (http://kbalertz.com/Feedback.aspx?kbNumber=937137/) The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2 was released

For more information about the Incremental Servicing Model for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:

935897 (http://kbalertz.com/Feedback.aspx?kbNumber=935897/) An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems

For more information about how to obtain SQL Server 2005 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:

913089 (http://kbalertz.com/Feedback.aspx?kbNumber=913089/) How to obtain the latest service pack for SQL Server 2005

For more information about the new features and the improvements in SQL Server 2005 Service Pack 2, visit the following Microsoft Web site:

http://go.microsoft.com/fwlink/?LinkId=71711 (http://go.microsoft.com/fwlink/?LinkId=71711)

For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:

822499 (http://kbalertz.com/Feedback.aspx?kbNumber=822499/) New naming schema for Microsoft SQL Server software update packages

For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:

824684 (http://kbalertz.com/Feedback.aspx?kbNumber=824684/) Description of the standard terminology that is used to describe Microsoft software updates

이 글은 스프링노트에서 작성되었습니다.