August 26, 2010 at 3:08 pm
I created a legacy DTS package in 2005 that does 2 fairly simple archiving steps:
1) Copy 31,000,000 records from ServerA.DatabaseA to ServerB.DatabaseB, where date < getdate - 365.
Its batch size is 25,000 records, to keep logs & memory manageable.
2) After Step 1 completes, delete the records from ServerA where date < getdate - 365.
It was running for 2+ days (as expected) and I could see the counts and date increasing at the target server (ServerB) as the records got copied over 25,000 at a time.
I just got a Completed Successfully message for the job, but it only copied 1/2 the records, about 16,000,000. I confirmed the original records are still on the source server (ServerA).
Since nothing failed, how can I troubleshoot this oddity ?
August 26, 2010 at 11:45 pm
run your sql script/ SP manually on SSMS . Additionally if you see some badly design approach then you can use batch wise insertion/truncation of records.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 27, 2010 at 8:34 am
It ran for quite a while with no issues. Perhaps the "order by" in step 1 adds to the load on TEMPDB. I did find that TEMPDB is huge today, with almost no disk space, so that might be related.
The job itself is pretty simple:
Step 1: Data transformation from A to B, batch size of 25,000
select * from DatabaseA..TableA with (NOLOCK)
-- remove time portion
where start < cast(FLOOR(cast(getdate() -367 as float)) as datetime)
order by start -- order to help see how much has processed.
"start" is datetime and has an index.
Step 2: Delete records from ServerA, 1 at a time to prevent blocking
declare @primarykey-2 uniqueidentifier
declare @DelDate datetime
-- Get most recent date archived minus 2
set @DelDate = (select max(start) from ServerB.DatabaseB.dbo.TableB) - 2
-- strip time off @DelDate for even data break
select @DelDate = cast(FLOOR(cast(@DelDate AS float)) as datetime)
NextRecord:
set @primarykey-2 = (select top 1 ID
from ServerA.dbo.TableA with (nolock, INDEX(IX_CallLog_Start))
where start <= @DelDate)
delete from ServerA.dbo.TableA --
where ID = @primarykey-2 AND start <= @DelDate
if @@rowcount = 0 goto EndJob -- exit if no more to delete
Goto NextRecord
EndJob:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply