Merge replication identity range crisis …

--Find the merge replication identity ranges/seed/crisis thresholds ...

SET NOCOUNT ON
declare @filterPct smallint
select @filterPct = 0
create
table #ranges
(objectId int, tableName sysname, columnName sysname, rangeMin int, rangeMax int, currentMax int NULL)
INSERT
INTO #ranges
-- find replication tables and columns which are identity columns
select
objectId , tableName , columnName
, convert(int, substring(minStr, 0, patindex('%and%', minStr))) minValue ,
convert(int, substring(maxStr, 0, len(maxstr))) maxValue ,
NULL from
(
select
o.id objectId ,
object_name(o.id) tableName
, o.name columnName
, o.colId
, com.text
, substring(com.text, patindex('%>%', com.text) + 1, 20) minStr
, substring(com.text, patindex('%<%', com.text) + 1, 20) maxStr
from
msrepl_identity_range r inner
join
syscolumns o on r.objid = o.Id inner
join
sysconstraints con on o.id = con.id and o.colid = con.colId inner
join
syscomments com on con.constId = com.id
where o.status = 0x80 ) t1
order by 2,3
DECLARE @objectId int, @sqlStr nvarchar(4000)
SELECT
TOP 1 @objectId = objectId from #ranges order
by objectId
WHILE (@@rowcount
> 0)
BEGIN
SELECT @sqlStr =
'update #ranges set currentMax = (select max('
+ columnName +
') from '
+ tableName +
'(nolock) where '
+ columnName +
' BETWEEN '
+
convert(varchar, rangeMin)
+
' AND '
+
convert(varchar, rangeMax)
+
') WHERE '
+
' objectId = '
+
convert(varchar, @objectId)
from #ranges where objectId = @objectId
EXEC
sp_executesql
@sqlStr
SELECT
TOP 1 @objectId = objectId from #ranges where objectId > @objectId order
by objectId
END
select
@@servername serverName
, tableName
, (rangeMax - rangeMin) rangeSize
, (rangeMax - currentMax) rangeAvailable
, (currentMax - rangeMin) rangeUsed
, (convert(decimal(10,2), (currentMax - rangeMin)) / (rangeMax - rangeMin)) * 100 rangeUtilisation
from
#ranges
where
(convert(decimal(10,2), (currentMax - rangeMin)) / (rangeMax - rangeMin)) * 100 > @filterPct
drop table #ranges