deleteing data from all the tables on a continues basis

What are my options if I want to delete the data from all the tables in a database on a continues basis and keep the data just for the last 3 months?
DB has over 1000 tables,  DateEntered column is being populated when the data being inserted daily.

Metadata?
any recommendations?

Regards,
Fay ADBAAsked:
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.

lcohanDatabase AnalystCommented:
Assuming that "DateEntered" column exists and is populated on ALL the tables from that database you should be able to use  the undocummented sp_MSforeachtable SQL stored proc to run a DELETE statement with WHERE clause in a daily SQL Job.
Caution though  if you have large volumes of data to delete and obviously test, test, and test again before running such thing against a productions database + make sure backups are kept safe and long enough just in case.
Vitor has an example here for the TRUNCATE : https://www.sqlshack.com/an-introduction-to-sp_msforeachtable-run-commands-iteratively-through-all-tables-in-a-database/
0
Aneesh RetnakaranDatabase AdministratorCommented:
-- Disable all constraints in the database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

DECLARE @schema NVARCHAR(250);
DECLARE @tbl NVARCHAR(250);
DECLARE i CURSOR LOCAL FAST_FORWARD FOR
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME != 'sysdiagrams'
  AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME = t.TABLE_NAME AND c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.COLUMN_NAME = 'DateEntered' )

OPEN i;
FETCH NEXT FROM i INTO @schema, @tbl;
WHILE @@FETCH_STATUS = 0
BEGIN
      
    DECLARE @sql NVARCHAR(MAX) = N'DELETE FROM ['+@schema +']' +'.'+'['+ @tbl + '] WHERE DateEntered < dateadd(month, -3, getdate() );'
    PRINT @sql -- Make sure these are the commands you want to execute, and then uncomment the 'EXECUTE' statement

    /* Best to try just printing first */
    -- EXECUTE sp_executesql @sql

    FETCH NEXT FROM i INTO @schema, @tbl;
END

CLOSE i;
DEALLOCATE i;

-- Re-enable all constraints again
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
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
Fay ADBAAuthor Commented:
Thank you, Is it possible to control this in a metadata? For example, I wanna run this during the day for few hours etc. from 2pm to 4pm. Once the job stops at 4pm, next day it picksup where it was left off from the previous day.
0
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Aneesh RetnakaranDatabase AdministratorCommented:
how much data are you talking about, in 2 hrs it can delete a lot of data. if you run this on a daily basis, the no of records to eliminate will be lower.
0
Fay ADBAAuthor Commented:
We insert 1000's of rows into all the tables in this database everyday. The plan is to just keep 3 months of current data at a time and delete the rest on a ongoing basis. So, I was wondering to control this in a metadata, when the job starts it should know where to start and which row to execute based on the "DateEntered" column.
0
Aneesh RetnakaranDatabase AdministratorCommented:
Test that script and see how much time its taking.  Are you doing this in the production database ?

Another option is to Partition all the tables and purge the older data, this means you have to alter all the tables in your database.
0
Fay ADBAAuthor Commented:
It took an hour without the execute, it is production database about 1200 tables but the data itself is not that critical. We looked into partitioning but that is not a viable option since it uses dynamic sql stuff to update the db.
0
Fay ADBAAuthor Commented:
I ran it in a dev environment that gets updated everyday.
0
Aneesh RetnakaranDatabase AdministratorCommented:
>It took an hour without the execute,
Most of this time is used to Enable /disable the constraints. You can comment these and try again. Its better to try it on another copy of the database and see how much time it takes. Ensure that the database is in 'Simple' recovery.
0
Fay ADBAAuthor Commented:
less then a second :)

the db is in simple mode.
0
Dung DinhDBA and Business Intelligence DeveloperCommented:
I suggest you the plan as below
1- Initial phase: all data is existing in tall tables more than 3 months
CASE 1 -  data volume is small, I suggest that number of rows is less than 1million rows
DELETE TABLE .... WHERE DateEntered < CurrentDate - 3 months
CASE 2 - data volume is large
CREATE TABLE <temp table> for each table with structure as same as original table
INSERT INTO <temp table> SELECT .. FROM <original table> WHERE DateEntered >= CurrentDate - 3 months
TRUNCATE TABLE <original table>
INSERT INTO  <original table> SELECT ... FROM <temp table>

Apply this flow to all tables by building a dynamic TSQL script

2- Daily phase (after initial phase)
Create SQL Agent Job that will execute a script to delete data DELETE TABLE .... WHERE DateEntered < CurrentDate - 3 months
OR you should archive the data to static tables instead of deleting data permanently.

3- Maintenance plan job
Because you delete data, it can cause fragmentation table so we need to create a SQL Agent Jo to rebuild indexes and update statistic for those tables.
0
Fay ADBAAuthor Commented:
Also, is there a way I can run this in a small batch since there is so much data in these tables. I don't want the tranlog to get too big or if I have to stop then it rollback and locks the tables because there is always new data getting inserted in these tables.

So, If I have to stop and start again it picks up where it was left off.
0
Aneesh RetnakaranDatabase AdministratorCommented:
you can delete the rows in small sizes and / or you can include a "Checkpoint" statement after this statement "EXECUTE sp_executesql @sql"
0
Dung DinhDBA and Business Intelligence DeveloperCommented:
You can choose when you start the initial phase because it will run one time, the process will take much time at initial phase because you will need to delete more data but then you will delete data for one day at daily process.

In case you want to track what data is deleted, you can create a tracking table, when you delete data successful or failed for any date, you write a log record to this table. You will make sure that this process should be run after new data getting

So I suggest my idea
- Create a store procedure with a parameter for table
CREATE PROCEDURE DeleteHistoricalDate
 @TableName nvarchar(30)
AS
BEGIN
Building a dynamic script with @TableName
Check if new data getting
IF YES
       BEGIN TRANSACTION
       Delete data WHERE DateEntered <  CurrentDate - 3 months
       COMMIT;
       IF Delete OK
             Write log with Successful status
       ELSE
           Write log with Failed status
ELSE
      Do Nothing
END

--
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
Databases

From novice to tech pro — start learning today.