I just got off with TAC: This has happen twice to us.
They made it really easy to fix. and you don't need to stop any services to fix the problem.
- Do the validation listed in the bug description above.
- If everything checks out, open SQL studio for Logger B.
- select your logger database.
- click new query:
- Run the following query:
DECLARE @tName VARCHAR(MAX);
DECLARE @db_cursor CURSOR;
DECLARE @dbName varchar (1000)
DECLARE @sqlcommand nvarchar(max)
DECLARE @results1 bigint
DECLARE @results2 bigint
DECLARE @results3 bigint;
set @dbName = (SELECT DB_NAME() AS DataBaseName)
if @dbName like '%side%'
print '--- Gathering data from the '+@dbName+' Database. ---'+char(13)
else
goto CompleteEarly
SET @db_cursor = CURSOR FOR
SELECT distinct(TableName)
From dbo.Recovery
ORDER BY TableName;
OPEN @db_cursor;
FETCH NEXT FROM @db_cursor INTO @tName;
WHILE @@FETCH_STATUS = 0
BEGIN
--print @tName
IF @tName = 't_Agent_Logout' or @tName = 't_Agent_Skill_Group_Logout'
BEGIN
set @sqlcommand = 'set @results1 = (select max(RecoveryKey) from '+ @tName +')'
execute sp_executesql @sqlcommand,N'@results1 bigint OUTPUT', @results1 output
set @sqlcommand = 'set @results2 = (select top(1) RecoveryKey from '+@tName+' where LogoutDateTime=(select MAX(LogoutDateTime) from '+@tName+') order by RecoveryKey desc)'
execute sp_executesql @sqlcommand,N'@results2 bigint OUTPUT', @results2 output
IF (@results1-@results2) > 10
BEGIN
set @results3 = @results2 + 100000
set @sqlcommand = 'delete from '+@tName+' where RecoveryKey > '+ str(@results3,13,0)+';'
print @sqlcommand
-- execute sp_executesql @sqlcommand /* uncomment for auto-delete high recoverykeys */
END
END
IF (
COL_LENGTH(@tName, 'DateTime') IS NOT NULL
)
BEGIN
set @sqlcommand = 'set @results1 = (select max(RecoveryKey) from '+ @tName +')'
execute sp_executesql @sqlcommand,N'@results1 bigint OUTPUT', @results1 output
set @sqlcommand = 'set @results2 = (select top(1) RecoveryKey from '+@tName+' where DateTime=(select MAX(DateTime) from '+@tName+') order by RecoveryKey desc)'
execute sp_executesql @sqlcommand,N'@results2 bigint OUTPUT', @results2 output
IF (@results1-@results2) > 10
BEGIN
set @results3 = @results2 + 100000
set @sqlcommand = 'delete from '+@tName+' where RecoveryKey > '+ str(@results3,13,0)+';'
print @sqlcommand
-- execute sp_executesql @sqlcommand /* uncomment for auto-delete high recoverykeys */
END
END
FETCH NEXT FROM @db_cursor INTO @tName;
END;
CLOSE @db_cursor
DEALLOCATE @db_cursor
print (CHAR(13)+ '--- Copy the above queries and run against Logger DB first and then against HDS DB ---' + CHAR(13))
CompleteEarly:
if @dbName not like '%side%'
print '*** This must be run against logger database. ***';
6. you'll get a result from the query that will look something like this:
7. Copy that query.
8. As stated in the Result from the initial query, run this delete query against the logger db first.
9. Then run it against all your B-Side HDS instances that are having the problem.
10. Open up the space used summary in ICMDBA on your HDS and it should start syncing. (Remember: this could take a while to sync. Go grab a beer and come back. it should be done by then)
*******Note: Try to do this before you logger starts purging data. It usually stores 14days of data in the logger db (assuming it was sized right) if you don't catch this error before data starts purging, Congratulations: you need to do a restore from the a-side HDS. that sucks and usually requires a maintenance window. depending how big the A-Side HDS is, it may not even be possible. you might just have to take an L on the data loss.
