February 10, 2009 at 8:47 pm
I see the stuff you're talking about in there.
Does anyone know what all the crap is beneath that? I can't see what the views look like (no option to see what they consist of) that are below, but they are system views and I assume it all has to do with the updates that have yet to propogate to the publisher etc and stuff like that? And the versioning is some type of count showing who has newer data so they know who to update?
Not sure if much of that matters, but I definitely see what you're talking about on the = issue and not being >=.
But here's the question...I haven't played with triggers much, but if you have a multiple insert, is the trigger fired for every single row of that multi-row insert? If so, then the "=" is fine and you don't have to have ">=".
And, does the trigger run, and then determine if the insert can happen? Or does the insert happen and then the trigger run? I ask because if it's the latter, then you couldn't possibly be able to inser the data to get an identity >= the end of the index because it would fail on the constraint and not let you save it.
Now if the trigger happens before the row is inserted (and you can control if it goes in or not inside the trigger) and the trigger increments the identity, then sure the >= could potentially make sense in there.
Any clues on those? This is getting me interested in delving into here and seeing what's going on.
February 10, 2009 at 9:23 pm
The process as I understand it is:
The trigger runs after the insert transaction, whether that involves inserting one record or many - hence the problem with the '=' and inserting many.
The trigger has nothing to do with determining if the insert can happen and the automatic incrementing of the identity value is handled in the depths of SQL Server. The insert transaction is attempted and either succeeds or fails. If the batch is larger than the amount left in the range then the identity will increment past the end of the range and, if the second range isn't sequential, will fail on the identity range constraint. The entire transaction will then fail and be rolled back. So no records will have been inserted but the current identity value will be outside the range. When the trigger subsequantly runs it does a check for the current identity being equal to the range end and since it isn't (being greater than it) it doesn't update the ranges.
Not sure about the stuff below there. Had a brief look but don't have a heap of time myself but suspect it is all about populating the merge tables.
February 11, 2009 at 7:58 am
Ok, that makes sense.
The only way I can see to get around that is to have the "original" functionality where you have a value say 80% where if the first range is > 80% it switches over to the second range (if it isn't contigious). You will lose some ID's but at least it won't error out. This could be fixed in the "template" sql and it would work fine with all of the inserts.
I don't think this is happening on my side as all of our inserts are really single row inserts....well...most of the time. I'm going to have to look around and determine exactly how this is happening over here. Our replication agent runs so often (one even runs every minute) that I can't see the ranges being expired before a new one is retreived unless something else is going on (like above).
I don't see a huge issue with modifying the template file that the triggers are generated from, service packs aren't released that often, and it's enough of an issue that if the service pack does change it back to normal, you would see the issues arise and be able to re-edit the stored proc.
February 11, 2009 at 3:20 pm
Ok, now I really don't know what's going on.
So this afternoon I got the same error "The insert failed. It conflicted with an identity range check constraint in database X"
So IMMEDIATELY I went and looked at the identities in that table. I could see both of the ranges in there, they were as follows:
old: (I walked throughout the ranges and saw these)
164637-165636
160637-161636
I looked at the database and it had updated it's new ranges by the time I looked at the properties on the table:
new:
174636-175636
172636-173636
I then looked at my error I received from my program and saw it had an error inserting in a SINGLE insert.
The program inserts about 20 values in rapid succession from a stored procedure that does one insert at a time.
So my program loops 20 times and runs the stored procedure 20 times.
Now, I looked at the inserted values along with the dates I know they were inserted and saw only about 250 rows were inserted today. That means that my table had been working on the SECOND range for the past few days without updating to two new ranges.
The ONLY time the new ranges were obtained is when the last record of the last range was filled and the merge agent ran. My merge agent runs every 10 minutes for these set of tables, so it ran after about 50 rows were inserted into the second range and nothing was updated. Then 200 rows were inserted and the last ID of the second range was exhausted. I received about 5 errors as my loop of 20 inserts could no longer finish the last few inserts it had and the program errored out. Then I checked and the merge agent ran and FINALLY the two new ranges were updated.
So in my situation I'm not getting one new range at a time. It's waiting until both ranges are filled (even though the merge agent ran hundreds of times while both rows were inserted into) and then obtaining two new ranges (and for some reason they are not contegious either).
Any ideas on this? What is the call to set the bit to enable a new range to be brought down? Is it the DBCC CHECKIDENT RESEED thing?
February 11, 2009 at 3:25 pm
if IDENT_CURRENT('[dbo].[ActivityStats]') = @range_end
begin
DBCC CHECKIDENT ('[dbo].[ActivityStats]', RESEED, @next_range_begin) with no_infomsgs
end
else if IDENT_CURRENT('[dbo].[ActivityStats]') >= @next_range_end
begin
exec sys.sp_MSrefresh_publisher_idrange '[dbo].[ActivityStats]', 'BA9C5B3A-1A11-4962-85D3-017397DA581B', '162C150C-DFB8-47D0-8A83-63B7E93CD394', 2, 1
if @@error<>0 or @retcode<>0
goto FAILURE
end
ok guys, there it is. The RESEED (if it is what I think it is) only swaps the ranges so that you go onto the next range.
AND, if you're on the next_range_end (the second range) and the LAST ID, THEN IT WILL UPDATE!?
How does this make any sense? They have this code all fubar'd so it will only pull two new ranges once the last ID of the last range is used up.
Let me know if I'm not reading this right.
February 12, 2009 at 1:10 pm
cdooley - the DBCC CHECKIDENT RESEED resets the current identity from @range_end to @next_range_begin.
I would guess sys.sp_MSrefresh_publisher_idrange refreshes the actual ranges, though I haven't looked into how it does it.
I think that you've been mixing up the 1st and 2nd ranges, as what you say makes more sense the other way around.
Eg the ranges shown in your earlier post:
164637-165636
160637-161636
If by the 1st range you mean the 164637-165636, then this is actually the higher range - ie the 2nd range.
I'm not sure why your single row inserts eventually fail - at a guess maybe the range refreshing takes longer than those last 5 rows took to insert.
February 12, 2009 at 1:28 pm
I understand the higher numbers are the "next_range", but look at the code below:
if IDENT_CURRENT('[dbo].[ActivityStats]') = @range_end
begin
DBCC CHECKIDENT ('[dbo].[ActivityStats]', RESEED, @next_range_begin) with no_infomsgs
end
else if IDENT_CURRENT('[dbo].[ActivityStats]') >= @next_range_end
begin
exec sys.sp_MSrefresh_publisher_idrange '[dbo].[ActivityStats]', 'BA9C5B3A-1A11-4962-85D3-017397DA581B', '162C150C-DFB8-47D0-8A83-63B7E93CD394', 2, 1
if @@error<>0 or @retcode<>0
goto FAILURE
end
So, after the first range is exhausted (current ident = @range_end) the RESEED swaps the ranges to the "next_range_begin", which makes complete sense.
BUT, and the BIG BUT. the ONLY time the MSrefresh_publisher_idrange is ran is when the identity is >= @next_range_end!!
So in my scenario, this is exactly as I saw in the actual production environment. The range switches over fine with single inserts, but unless you have a merge agent run after the last row is inserted and you hit "next_range_end" you will not get 2 new ranges and it will fail until you receive those next 2 ranges.
February 13, 2009 at 8:28 am
cdooley (2/12/2009)
I understand the higher numbers are the "next_range", but look at the code below:
if IDENT_CURRENT('[dbo].[ActivityStats]') = @range_endbegin
DBCC CHECKIDENT ('[dbo].[ActivityStats]', RESEED, @next_range_begin) with no_infomsgs
end
else if IDENT_CURRENT('[dbo].[ActivityStats]') >= @next_range_end
begin
exec sys.sp_MSrefresh_publisher_idrange '[dbo].[ActivityStats]', 'BA9C5B3A-1A11-4962-85D3-017397DA581B', '162C150C-DFB8-47D0-8A83-63B7E93CD394', 2, 1
if @@error<>0 or @retcode<>0
goto FAILURE
end
So, after the first range is exhausted (current ident = @range_end) the RESEED swaps the ranges to the "next_range_begin", which makes complete sense.
BUT, and the BIG BUT. the ONLY time the MSrefresh_publisher_idrange is ran is when the identity is >= @next_range_end!!
So in my scenario, this is exactly as I saw in the actual production environment. The range switches over fine with single inserts, but unless you have a merge agent run after the last row is inserted and you hit "next_range_end" you will not get 2 new ranges and it will fail until you receive those next 2 ranges.
Well Said.
Q.E.D.!
that's why partitioning based on location is where merge shines.
* Noel
February 13, 2009 at 8:32 am
martin (2/10/2009)
If the identity makes it safely into the 2nd range (more likely if there is no gap), then the next time the merge agents runs, the ranges should get refreshed.If the merge agent doesn't run in time, then you are relying on the insert trigger to do this, which will only happen if you are inserting one row at a time, or if a multi-row insert happens to luckily use the last identity on for its last row.
Martin,
It seems you confirmed earlier what I just ran into with the trigger not refreshing the ranges until the second one is filled up. I have also confirmed (several times) that on my side, the merge agent doesn't always update the ranges when you're "into" the second range of values. I'm not sure why this happens, but it doesn't seem to pull an extra range down.
I looked at the range updating code:
exec sys.sp_MSrefresh_publisher_idrange
And you can see the var in the trigger on the table "2" is set to update both tables (which confirms what we both are talking about). If you look at the proc it can in-fact handle single range updates.
This means we should be able to change the model proc to make it update the ranges automatically after one range has been exhausted and not after both ranges have been exhausted.
Now what I haven't confirmed is when the "sys.sp_MSrefresh_publisher_idrange" actually refreshes the ranges. I'm wondering if it goes to the publisher immediately and pulls the new ranges, or set's a flag that tells the merge agent to pull down the ranges.
To me it seems as if it's probably a flag (I have no code to back this up right now), as I was into the 2nd range (for several days) and the merge agent ran several hundred times, and nothing was updated.
It was only when I hit the end of the second range that the next time the merge agent ran it pulled down 2 new ranges.
***********************UPDATE******************************
Ok, well I was looking at the publisher's stored procs as it was easier. I forgot I was on the publisher (duh!). So the above proc is ran on the publisher and the adjust merge range happens immediately. So on the publisher the two ranges really shouldn't impact much other than multi-row inserts as the merge agents running and inserting into the tables one row at a time will trigger the udpate and the ranges will successfully update.
The problems you will have in this code with the "gap" is when you are inserting rows DIRECTLY into the publisher and have a multi-row insert.
The only thing that's on the subscriber is the code to switch ranges from one to the other when the first range is filled up. Multi-row inserts here will fail as well, but single row inserts should have no problem.
I'm still at a loss for why my merge agent isn't triggering the refresh of the ranges when we run into the second range on the subscriber though, this is my BIG issue right now.
February 13, 2009 at 9:19 am
Now when I've tried it the past several times the range has updated (1 range at a time) on the subscriber if I'm into the second range.
It seems like it works sometimes and then others it has issues, really not sure what to think of this now.
Viewing 10 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply