Code to Archive Data

Question

Code to Archive Data

Viewing 11 posts - 31 through 41 (of 41 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

BTW... the above isn't meant to discourage anyone from partitioning. It's meant to inform so that you can actually develop a great plan for partitioning. The same method that I used in the presentation and demo therein is exactly what I used on my now nearly 400GB telephone log table. Once implemented, my backups dropped from hours to minutes, my "get back in business" restore time did likewise as did my index maintenance plans. I don't have to touch a thing at the end of every month, either. It just works and it works every time. It's truly a "set it and forget it" system when implemented correctly as in the presentation.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Jeff,

That was a great presentation that you shared with me.

Until I can get a solid and tested plan I have elected to write a script to archive and delete the data from a table.

The way I do it is I create a control table that stores the Batch size, etc .and it includes an Active Flag.

If the Active Flag is set to 1 it executes the WHILE Loop. If I change it to 0 then the loop is not executed.

I was told that I do not need that I can simply stop the job or script.

If I do not gracefully Stop the procedure and use the Active Flag to determine when the process could it not result in a rollback or und

undesired results (corruption) .

Thank you for your input.

🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I prefer to have a control table where you lookup the batch size, several other other field to include an active flag.

If the flag is set to 1 it will execute the loop.

If it is set to 0 it will not execute the loop and stop processing.

I was told to not add that logic that all I needed to stop the script or eventually moved to job.

That worries me. I do not want to rollback or have data corruption.

I want to stop the job at the beginning of the WHILE loop to avoid recovery or a corrupt database and stop the process gracefully.

Is my logic correct?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Post removed.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

I added the table Name to the code so that it could be used to archive more that 1 table.

The logic is based on a variation of Lynn's code.

It does not archive any records.

What is wrong with the WHILE Loop?

DECLARE @TableName VARCHAR (50)

DECLARE @BatchSize int

DECLARE @ArchiveYear Date

DECLARE @RowCount int

SET @TableName = 'tblCall'

SET @BatchSize = (SELECT BatchSize FROM PrismDataArchive.dbo.ArchiveDriver WHERE TableName = 'tblCall')

SET @ArchiveYear = (SELECT ArchiveYear FROM PrismDataArchive.dbo.ArchiveDriver WHERE TableName = 'tblCall')

DECLARE @Call_Date Date

SET @Call_Date = (SELECT TOP 1 Call_date

FROM [PrismDataSource].[dbo].[tblCall] tc)

--SELECT * FROM PrismDataArchive.dbo.ArchiveDriver

--WHERE TableName = 'tblCall'

--SELECT @BatchSize, @TableName -- AND (SELECT Active_Indicator FROM PrismDataArchive.dbo.ArchiveDriver) = 1

--WHERE @BatchSize > 0

--SELECT * FROM PrismDataArchive.dbo.ArchiveDriver

DECLARE @Active_Indicator bit

SET @Active_Indicator =

(SELECT @Active_Indicator FROM PrismDataArchive.dbo.ArchiveDriver

WHERE Active_Indicator = 1 AND TableName = @TableName)

WHILE @BatchSize > 0 AND @TableName = ('tblCall') AND @Active_Indicator = 1

--WHILE @BatchSize > 0 AND @TableName = ('tblCall') AND (SELECT Active_Indicator FROM PrismDataArchive.dbo.ArchiveDriver) = 1

BEGIN

BEGIN TRY

BEGIN TRANSACTION;

IF @Call_Date IS NULL

BEGIN

DELETE TOP (@BatchSize) FROM tc

OUTPUT DELETED.* INTO PrismDataArchive.dbo.tblCallArchive

FROM [PrismDataSource].[dbo].[tblCall] tc

WHERE tc.Call_Date IS NULL

END

ELSE

BEGIN

DELETE TOP (@BatchSize) FROM tc

OUTPUT DELETED.* INTO PrismDataArchive.dbo.tblCallArchive

FROM [PrismDataSource].[dbo].[tblCall] tc

WHERE tc.Call_Date >= DATEADD(yy, DATEDIFF(yy,0,@ArchiveYear), 0) --jan 1 of the year of the date param passed.

AND tc.Call_Date < DATEADD(yy,1,DATEADD(yy, DATEDIFF(yy,0,@ArchiveYear), 0)) --add one year:

END

SET @BatchSize = @@ROWCOUNT; -- capture number of rows affected

IF @BatchSize > 0

BEGIN

INSERT INTO ArchiveLog (TableName,ArchiveYear, BatchSize)

VALUES (@TableName,@ArchiveYear,@BatchSize)

END

--COMMIT TRANSACTION;

END TRY

BEGIN CATCH

--ROLLBACK TRANSACTION;

END CATCH

END

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

The problem is that the @Active_Indicator IS NULL.

SET @Active_Indicator =

(SELECT @Active_Indicator FROM PrismDataArchive.dbo.ArchiveDriver

WHERE Active_Indicator = 1 AND TableName = @TableName)

The above code does not work.

The variable @Active_Indicator IS NULL. That is the issue.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

The @Active_Indicator Variable is being set to 1 now but it does not execute the WHILE Loop.

That does not make sense?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

x SSC-Insane Points: 23660 More actions · Answer 8

You should probably repost the code you're currently trying to get to work! Also, you could post sample ddl that would help folks play with the code and especially duplicate the failure you're getting.

Just a suggestion!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

Thanks.

I changed this:

WHILE @BatchSize > 0 AND @TableName = ('tblCall') AND @Active_Indicator = 1

To this. It works.

WHILE @BatchSize > 0 AND @TableName = 'tblCall' AND @Active_Indicator = 1

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

I could the see the problem from the git go:

DECLARE @TableName VARCHAR (50)

DECLARE @BatchSize int

DECLARE @ArchiveYear Date

DECLARE @RowCount int

SET @TableName = 'tblCall'

SET @BatchSize = (SELECT BatchSize FROM PrismDataArchive.dbo.ArchiveDriver WHERE TableName = 'tblCall')

SET @ArchiveYear = (SELECT ArchiveYear FROM PrismDataArchive.dbo.ArchiveDriver WHERE TableName = 'tblCall')

DECLARE @Call_Date Date

SET @Call_Date = (SELECT TOP 1 Call_date

FROM [PrismDataSource].[dbo].[tblCall] tc)

--SELECT * FROM PrismDataArchive.dbo.ArchiveDriver

--WHERE TableName = 'tblCall'

--SELECT @BatchSize, @TableName -- AND (SELECT Active_Indicator FROM PrismDataArchive.dbo.ArchiveDriver) = 1

--WHERE @BatchSize > 0

--SELECT * FROM PrismDataArchive.dbo.ArchiveDriver

DECLARE @Active_Indicator bit

/*

Your problem is here. You are selecting a constant null value (@Active_Indicator is undefined at this point).

SET @Active_Indicator =

(SELECT @Active_Indicator FROM PrismDataArchive.dbo.ArchiveDriver

WHERE Active_Indicator = 1 AND TableName = @TableName)

*/

-- Try this instead:

SELECT @Active_Indicator = cast(Active_Indicator as bit) FROM PrismDataArchive.dbo.ArchiveDriver WHERE Active_Indicator = 1 AND TableName = @TableName;

WHILE @BatchSize > 0 AND @TableName = ('tblCall') AND @Active_Indicator = 1

--WHILE @BatchSize > 0 AND @TableName = ('tblCall') AND (SELECT Active_Indicator FROM PrismDataArchive.dbo.ArchiveDriver) = 1

BEGIN

BEGIN TRY

BEGIN TRANSACTION;

IF @Call_Date IS NULL

BEGIN

DELETE TOP (@BatchSize) FROM tc

OUTPUT DELETED.* INTO PrismDataArchive.dbo.tblCallArchive

FROM [PrismDataSource].[dbo].[tblCall] tc

WHERE tc.Call_Date IS NULL

END

ELSE

BEGIN

DELETE TOP (@BatchSize) FROM tc

OUTPUT DELETED.* INTO PrismDataArchive.dbo.tblCallArchive

FROM [PrismDataSource].[dbo].[tblCall] tc

WHERE tc.Call_Date >= DATEADD(yy, DATEDIFF(yy,0,@ArchiveYear), 0) --jan 1 of the year of the date param passed.

AND tc.Call_Date < DATEADD(yy,1,DATEADD(yy, DATEDIFF(yy,0,@ArchiveYear), 0)) --add one year:

END

SET @BatchSize = @@ROWCOUNT; -- capture number of rows affected

IF @BatchSize > 0

BEGIN

INSERT INTO ArchiveLog (TableName,ArchiveYear, BatchSize)

VALUES (@TableName,@ArchiveYear,@BatchSize)

END

--COMMIT TRANSACTION;

END TRY

BEGIN CATCH

--ROLLBACK TRANSACTION;

END CATCH

END

Even with that change, I'm not sure is this going to do what you think it is going to do. I haven't tried to decipher the logic in the code.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

Lynn Pettis (8/19/2015)
I could the see the problem from the git go:
DECLARE @TableName VARCHAR (50)
DECLARE @BatchSize int
DECLARE @ArchiveYear Date
DECLARE @RowCount int
SET @TableName = 'tblCall'
SET @BatchSize = (SELECT BatchSize FROM PrismDataArchive.dbo.ArchiveDriver WHERE TableName = 'tblCall')
SET @ArchiveYear = (SELECT ArchiveYear FROM PrismDataArchive.dbo.ArchiveDriver WHERE TableName = 'tblCall')
DECLARE @Call_Date Date
SET @Call_Date = (SELECT TOP 1 Call_date
FROM [PrismDataSource].[dbo].[tblCall] tc)
--SELECT * FROM PrismDataArchive.dbo.ArchiveDriver
--WHERE TableName = 'tblCall'
--SELECT @BatchSize, @TableName -- AND (SELECT Active_Indicator FROM PrismDataArchive.dbo.ArchiveDriver) = 1
--WHERE @BatchSize > 0
--SELECT * FROM PrismDataArchive.dbo.ArchiveDriver
DECLARE @Active_Indicator bit
/*
Your problem is here. You are selecting a constant null value (@Active_Indicator is undefined at this point).
SET @Active_Indicator =
(SELECT @Active_Indicator FROM PrismDataArchive.dbo.ArchiveDriver
WHERE Active_Indicator = 1 AND TableName = @TableName)
*/
-- Try this instead:
SELECT @Active_Indicator = Active_Indicator FROM PrismDataArchive.dbo.ArchiveDriver WHERE Active_Indicator = 1 AND TableName = @TableName;
WHILE @BatchSize > 0 AND @TableName = ('tblCall') AND @Active_Indicator = 1
--WHILE @BatchSize > 0 AND @TableName = ('tblCall') AND (SELECT Active_Indicator FROM PrismDataArchive.dbo.ArchiveDriver) = 1
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
IF @Call_Date IS NULL
BEGIN
DELETE TOP (@BatchSize) FROM tc
OUTPUT DELETED.* INTO PrismDataArchive.dbo.tblCallArchive
FROM [PrismDataSource].[dbo].[tblCall] tc
WHERE tc.Call_Date IS NULL
END
ELSE
BEGIN
DELETE TOP (@BatchSize) FROM tc
OUTPUT DELETED.* INTO PrismDataArchive.dbo.tblCallArchive
FROM [PrismDataSource].[dbo].[tblCall] tc
WHERE tc.Call_Date >= DATEADD(yy, DATEDIFF(yy,0,@ArchiveYear), 0) --jan 1 of the year of the date param passed.
AND tc.Call_Date < DATEADD(yy,1,DATEADD(yy, DATEDIFF(yy,0,@ArchiveYear), 0)) --add one year:
END
SET @BatchSize = @@ROWCOUNT; -- capture number of rows affected
IF @BatchSize > 0
BEGIN
INSERT INTO ArchiveLog (TableName,ArchiveYear, BatchSize)
VALUES (@TableName,@ArchiveYear,@BatchSize)
END
--COMMIT TRANSACTION;
END TRY
BEGIN CATCH
--ROLLBACK TRANSACTION;
END CATCH
END
Even with that change, I'm not sure is this going to do what you think it is going to do. I haven't tried to decipher the logic in the code.

I did test it and I will perform additional testing going forward.

Thant you for the code you posted it was a great help.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/