Identity Management - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Replication (2008)

Identity Management

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2014-09-29 : 08:42:37

Hi All,

we are using merge replication on sql server 2008 R2.

Background:
our database is having around 500 tables, replicated. we are getting frequent application crashes because of Identity range completed and allocating new identity ranges (at subscriber database).

we have decided to increase the ranges which are sufficient for at least for an year.

Requirement:
could you help me with a query to get all the tables with current identity ranges (example like 10000 or 20000 etc), and how frequently they are getting new ranges? etc.

basically, what I'm looking is, if a table is having 5000 range, and it has allocated new ranges every week, it means, for an year, 53*5000 is the number which I'm going to assign for the table. to get like this for each table, it will take some good time.

could you please help me with a query for this.

Thanks in Advance

Arnav
Even you learn 1%, Learn it with 100% confidence.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-09-29 : 09:37:19

You can change the where clause to look for whatever you need. > < between etc.

SELECT TABLE_NAME,COLUMN_NAME, I.*
FROM INFORMATION_SCHEMA.COLUMNS
CROSS APPLY (SELECT IDENT_CURRENT( TABLE_NAME ) IdentityNext) I
WHERE COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
AND I.IdentityNext BETWEEN 10000 AND 20000
ORDER BY TABLE_NAME

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2014-09-29 : 10:22:32

Hi Michael,
thank you so much for the reply.

I'm actually need like below.

Example1:

Table 1 - Primary range 1000 - 2000, secondary range - 3000 - 4000.

based on generations, it has generated new identities twice in a week. so, to calculate for an year,
53*2 (twice in a week) times, it has generated. so for an year, the range should be like this

1000*53*2 = 106000
now my new range should be 1000 to 53000 as primary and 53001 to 106000 as secondary

Example 2:

Table 2 - Primary range 1 - 1500, secondary range - 3500 - 5000.

this table has generated new ranges 4 times in a week.
1500*53*4=318000
now my new ranges should be 1-159000 as my primary range, and secondary range is 159001 to 318000

it is not easy to caliculate for more than 500 tables. so I'm looking for a query to get the list for all the tables, so that
i can caliculate for all of them and implement.

please help.

Arnav
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2014-09-29 : 10:33:07

Hi All,
I some how managed upto this.
SELECT
article,
time_of_allocation,
publication,
subscriber,
subscriber_db,
range_begin,
range_end,
next_range_begin,
next_range_end,
max_used
from
MSmerge_identity_range_allocations WHERE subscriber='DD' AND publication LIKE 'prod_%' ORDER BY article, time_of_allocation desc
if I give article in the where clause, then i'll get for that table. I need it for all the tables in the database. please help.

Arnav
Even you learn 1%, Learn it with 100% confidence.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources