Link to home
Start Free TrialLog in
Avatar of wiggy353
wiggy353

asked on

Partition switching/log purge problem

The senior sql people at my work are not here today and I need to figure out why this job keeps failing. It has to do with partition switching and I am not very familiar with that.

Here is the code from the stored procedure that fails:

--all tables created

Declare 
	@ArchiveCount as bigint
	, @MoveArchive as bigint
	, @totalCount as bigint
Declare 
	@AfterArchiveCount as bigint
	, @AfterMoveArchive as bigint
	, @AftertotalCount as bigint
Declare 
	@dt as int
	, @Purge as tinyint
	, @ct as bigint
	, @reprocess as int
Declare 
	@DaystoKeep as int

Set @DaystoKeep = 15

Set @reprocess = 0;

-- check to see if table is not empty
Set @MoveArchive = (select count(*) from Purge.OrderAllocatorLog)

if (@MoveArchive>0)
	begin
	Set @reprocess = 1;
	goto ProcessData
	end

FinishProcess:

Set @Purge = (Select datepart(day,getdate()))
Set @Purge = @Purge - 1

If (@Purge = 0)
	Set @Purge = 31;
	
Set @ct = (select count(*) from dbo.OrderAllocatorLog where Purge = @Purge)
	
While (@ct = 0)
	Begin
	Set @Purge = @Purge - 1
	Set @ct = (select count(*) from dbo.OrderAllocatorLog where Purge = @Purge)
	
	if (@Purge = 27)
		Begin
		Set @ct = -1
		Set @Purge = 99		
		End 
		
	If 	((@ct = 0) and (@Purge =0))
		Goto EndNow

	End

if ((@Purge > 0) and (@Purge < 32))
	Begin

	set @dt = (SELECT $PARTITION.OrderAllocatorLogPF1 (@Purge))

	--select @dt

	Truncate table Purge.OrderAllocatorLog

	ALTER TABLE dbo.OrderAllocatorLog SWITCH PARTITION @dt TO Purge.OrderAllocatorLog

ProcessData:

	Set @ArchiveCount = (select count(*) from dbo.OrderAllocatorLogArchive)

	Set @MoveArchive = (select count(*) from Purge.OrderAllocatorLog)

	Set @totalCount = @ArchiveCount + @MoveArchive

	if (@MoveArchive > 0)
			Begin
	
			INSERT INTO [CMOP].[dbo].[OrderAllocatorLogArchive]
				([ID],[DateAndTime],[MessageText])
			Select [ID],[DateAndTime],[MessageText]
				from Purge.OrderAllocatorLog
           
			Set @AfterArchiveCount = (select count(*) from dbo.OrderAllocatorLogArchive)
	
			If (@AfterArchiveCount <> @totalCount)             
				Begin
				INSERT INTO [CMOP].[dbo].[OrderAllocatorLogArchive]
					([ID],[DateAndTime],[MessageText])				
				Select oal.[ID],oal.[DateAndTime],oal.[MessageText]
					from Purge.OrderAllocatorLog oal 
						left join [dbo].[OrderAllocatorLogArchive] oala
						on oal.ID = oala.ID
						and oal.DateAndTime = oala.DateAndTime
					where oala.ID is null
				End 
			Else IF (@AfterArchiveCount = @totalCount)             
				Begin
				Truncate table Purge.OrderAllocatorLog
				End 
           
			End
	end

if (@reprocess = 1)	
	Begin
	set @reprocess = 0;
	goto FinishProcess
	End

EndNow:
	
Delete from	[dbo].[OrderAllocatorLogArchive]
where DateAndTime < getdate()- @DaystoKeep	

Open in new window


When this runs, it gives an error that the scalar variable @dt must be declared. But from what I can see it is being declared. The exact point that the job fails at is line 82, when it tries to ALTER TABLE and switch the partition.

Can someone help me understand what the problem is? We have this same job running on other servers and it works fine.

Thanks
Avatar of lcohan
lcohan
Flag of Canada image

My guess is due to some issue with logic and this particular server data to be purged - check out the ProcessData: label is in a BEGIN/END and called right at the begining by code below but @dt is SET = (SELECT $PARTITION.OrderAllocatorLogPF1 (@Purge))
above the ProcessData: label.

if (@MoveArchive>0)
      begin
      Set @reprocess = 1;
      goto ProcessData
      end
ASKER CERTIFIED SOLUTION
Avatar of wiggy353
wiggy353

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wiggy353
wiggy353

ASKER

I found the solution myself.