JOb for truncating table and archiving data

I would like to create a job in SQL that does the following.

I would like to run stored proc.

It would create an archive table if it doesnt already exist using the same structure as the table I will be truncating from.

Then the table I am truncating would pull data from the last 3 months if there was 3 months worth of data and move it to the archive table.
It would truncate the archive table as I only want it to have 3 months worth of data.

Then it would truncate the table.

Then I think I would want to run some type of shrink and potentially something to fix fragmentation as well.


I am guessing this would just execute from a stored proc and run on a SQL agent...I guess every 3 months....since that would be the timing I am looking for.


Please help.  I would run this at night and am assuming once this was in place it would be a fairly quick job.
LVL 11
Robb HillSenior .Net DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Based upon your question, it seems you were looking for scripts to be executed once every 3 months to purge all records older than the original table. Since you don't want to store any data older than 3 months Archiving might not be necessary.

If you have Enterprise Edition of SQL Server, then we can create Quarterly Partitions on the table(if not already created) and then follow the below steps
1. Create a Staging/Archiving table with exact table structure as that of your original table.
2. SWITCH data from one of the quarterly partitions in your current table to the Staging/Archiving table.
3. Merge the empty partition in original table after Switching data out.
4. Create one more partition for future in your Original table.

Kindly let me know whether you have SQL Server Enterprise Edition or not. If you don't have Enterprise edition, then above might not be valid and you might need to simply delete records using DELETE query.
0
Robb HillSenior .Net DeveloperAuthor Commented:
I have customers with every version from 2008r2 standard and up....I will need a plan that will work with that being the lowest denominator.  we also are forced to run in compitibility 100 as we have legacy fox pro hitting our sql databases too.   So looking for a clean solution of just truncating.....and keeping the tables tidy.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> from 2008r2 standard and up....I will need a plan that will work with that being the lowest denominator

Ok, then we might need to write a purge script alone to suit both Standard and Enterprise customers.
Sample code might look like this and replace the table name and column name to get it work..
Also make sure you run it on a daily basis as purging records via query for 3 months on a single day would take lot of time depending upon the volume of data.

CREATE PROCEDURE [Purgetablename](
 @Retention_Days INT = 90,
 @Batchsize INT = 1000
)
AS
BEGIN
DECLARE @RetentionDays DATE
SET @RetentionDays = DATEADD(dd, -@Retention_Days, getdate())

While 1=1
Begin
	DELETE Top(@Batchsize) tablename
	WHERE datetime_column < @RetentionDays

	If @@rowcount = 0
    break
End
END

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Robb HillSenior .Net DeveloperAuthor Commented:
now by doing it this way....am I going to potential affect the log as this is a delete?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
If your database recovery model is set to FULL, I hope you would have Transactional Log backups configured every 15 minutes or 1 hour max in your DB Servers. If so, then it shouldn't be a problem as we are purging records in smaller batches.

If there are no frequent log backups, then it can possibly increase the Log file.
0
Robb HillSenior .Net DeveloperAuthor Commented:
I have 99 customers ...that I cannot always control how they maintain their sql.  I can say that not all do full and since they all "technically" own their database the variance of skill level is high to none at all.  

With that being said I dont want to go to the delete option.

I do agree that your solution could work but I think my situation is unique and a truncate solution works better as it eliminates the unknown factor that I cannot guarantee.   Does that make sense?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> I have 99 customers ...that I cannot always control how they maintain their sql.

I agree but I hope you will be able to guide them on the basic SQL Maintenance tasks.
if you can implement purge on a daily basis, then basically it should not bloat the Log file.

>> I do agree that your solution could work but I think my situation is unique and a truncate solution works better as it eliminates the unknown factor that I cannot guarantee.   Does that make sense?
Yes, it might provided everyone has Enterprise edition of SQL Server. If you can guarantee that, then we can focus on Partition Switching option based purge.

>> With that being said I dont want to go to the delete option.
If everyone can't have Enterprise edition, then that this might be the only option to Purge data from a table. And without doing so, it would increase the no. of records on the table leading to performance issues later on..
0
Robb HillSenior .Net DeveloperAuthor Commented:
truncate should not be an issue ..this has been around forever.....Yes if we went the partition route then that does make this more specific...again..not what I am looking for.  I need a solution that will work on any sql server edition above 2008r2...standard...it doesnt matter....I don't want to specify commands that force me into a situation that I cannot control.  And yes I cannot completely control these offices.  I can put a job on their server but in doing so I need to know my job will run without an issue.  This again is why I thought just building a job to do as I suggested in the original post would be sufficient....I may never look at these databases again or it could be a year.  I need a job that will run and not have the "potential" to mess up anything
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> truncate should not be an issue ..this has been around forever

Just to clarify on your original requirement, kindly confirm which one you prefer.
1, if they table has 6 month data, then you wish to move older 3 months data to Archive table and truncate/delete those records int eh current table.
2. If your requirement is to move all 6 months data from the current table to Archive table and then TRUNCATE then we can do it without any issues.

if you wish to go with Option#2, then we can implement it without any issues for any SQL Server edition without growing the Log file.
if it is option#1, then we can't implement TRUNCATE option without having slight growth on Log file..
Kindly clarify..
0
Robb HillSenior .Net DeveloperAuthor Commented:
The first time this process is ran there will be a bunch of data in these tables going well past 6 months.  So maybe there should be something ran the first time only to get the data cleaned up.



Once this job is running it would be as simple as moving the data from the systemlog table once it has 3 months worth of data and moving it to the archive.  The archive would be truncated before the move as we would only need the lasted 3 months worth of archived data.

Then truncating the systemlog table.  
Logs will begin to write as we truncate as they are on the milisecond.


Does this answer your concerns?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Thanks for the details. then we can go with the below approach and it should work for all your customers..
--Truncate Archive Table
TRUNCATE TABLE ArchiveTable;
--DROP Archive table 
DROP TABLE ArchiveTable;
--Move all records to Archive table from Original Table
SELECT *
INTO ArchiveTable
FROM OriginalTable;
--Truncate all records from Original Table
TRUNCATE TABLE OriginalTable;

Open in new window

0
Robb HillSenior .Net DeveloperAuthor Commented:
why are you dropping the table?  

Also I would need only 3 months of data in the table when its ran...not sure I see that.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Missed mentioning it above, We need to configure this script to run as a DB Job once every quarter and this will retain 3 months of data in Archivetable.
I was dropping the Archive table to clean it up and since we run this once every quarter you can be sure that this ArchiveTable contains only 3 months of data and this is the most simplified script that can be used out..
0
Robb HillSenior .Net DeveloperAuthor Commented:
ok some from this approach I would need the following...to restate what you said and ask a few more questions.

1)  We create the archivetable with the last 3 months of data...and we check if table exists or not...if it does then we truncate if it doesnt then we just create the table.

So here is my create on the table....I still need to modify this to check if it exists and run a truncate if it does.

USE [cadoc_system]
GO
 
/****** Object:  Table [dbo].[SystemLog_Archive]    Script Date: 11/1/2016 4:21:04 PM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[SystemLog_Archive](
       [nSystemLog] [int] IDENTITY(1,1) NOT NULL,
       [cApplication] [varchar](200) NOT NULL,
       [cCategory] [varchar](200) NOT NULL,
       [cLogData] [text] NOT NULL,
       [cLogType] [varchar](50) NOT NULL,
       [dCreated] [datetime] NOT NULL,
CONSTRAINT [PK_SystemLog_Archive] PRIMARY KEY CLUSTERED 
(
       [nSystemLog] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[SystemLog_Archive] ADD  CONSTRAINT [DF_SystemLog_Archive_dCreated]  DEFAULT (getdate()) FOR [dCreated]
GO

Open in new window


2)  We then do a select insert from the systemlog table into the systemlogarchive table for 3 months worth of data.  I think there is a better way to handle date here..I do not want to put a constant date in here.

Then I insert records into archive I may want to keep for history purposes:
INSERT INTO cadoc_system..SystemLog_Archive
   SELECT  [cApplication]
      ,[cCategory]
      ,[cLogData]
      ,[cLogType]
      ,[dCreated]
  FROM [cadoc_system].[dbo].[SystemLog]
 
  where dCreated > '2016-10-15'
  and cLogType = ('Error')


3)  Finally I do a truncate on the systemlog table so we do not grow the transaction logs from doing a delete.
 
  TRUNCATE TABLE cadoc_system..SystemLog
0
Robb HillSenior .Net DeveloperAuthor Commented:
So can I put all of this in one stored proc...and then setup the job to run every 3 months....we change the structure to manage the archive as the primary goal of this is two fold.

1)  just to free up space as this is one of the only tables I can clear out alot of data from...and some customers have 6 years of logs..lol...

2) this table is gets read and inserted at the milisecond by our web app and a windows app and any services we have on the machine so its hit pretty hard....and though its usually only read when you need to look at history...the deletets and inserts can get slower over time.


CAn you help me wrap this up?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes, you can make everything as a single stored procedure to handle it better.
If there are huge space released out of the delete/truncate operation, then it is better to do the database shrink during the off peak hours and hence I wouldn't recommend to do that along with this procedure.
0
Robb HillSenior .Net DeveloperAuthor Commented:
yea I will handle the shink another way..how would you put this proc together...I have you most of the innerds...just curious how you would put together....
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
This should help..
CREATE PROCEDURE SystemLog_Archive
AS
BEGIN
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'SystemLog_Archive' AND Type = 'U')
BEGIN
CREATE TABLE [dbo].[SystemLog_Archive](
       [nSystemLog] [int] IDENTITY(1,1) NOT NULL,
       [cApplication] [varchar](200) NOT NULL,
       [cCategory] [varchar](200) NOT NULL,
       [cLogData] [text] NOT NULL,
       [cLogType] [varchar](50) NOT NULL,
       [dCreated] [datetime] NOT NULL DEFAULT getdate(),
CONSTRAINT [PK_SystemLog_Archive] PRIMARY KEY CLUSTERED 
(
       [nSystemLog] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END

TRUNCATE TABLE [SystemLog_Archive];

INSERT INTO cadoc_system..SystemLog_Archive
SELECT  [cApplication]
    ,[cCategory]
    ,[cLogData]
    ,[cLogType]
    ,[dCreated]
FROM [cadoc_system].[dbo].[SystemLog]
where dCreated > cast(GETDATE()-90 as date)
and cLogType = ('Error')

TRUNCATE TABLE [SystemLog];
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Robb HillSenior .Net DeveloperAuthor Commented:
Thanks so much..this has been working perfectly!
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.