Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Truncate MSSQL data that is from 2011 and prior

Posted on 2012-08-21
Medium Priority
Last Modified: 2012-08-21
I am running a MSSQL 2005 database on a 2003R2 server that is getting too large for the server. I have been asked to truncate all data that is older than December of 2011.

I have not heard of doing this and have not been able to find out how to accomplish the task.

Any suggestions
Question by:jimmylew52
  • 2
  • 2
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38316386
you need to know/understand the table schema to know how to do this.
some (transaction) tables will have datetime columns which indicate this, and you can then delete those rows, for example:
DELETE yourtable WHERE datetimecol <= CONVERT(datetime, '2012-01-01', 120)

Expert Comment

ID: 38316478
If the table doesn't have that sort of field, you can try to find a reasonable number of records to purge and purge them. Then, alter the table so that it has a "date modified" field and attach a trigger on inserts and updates to update that "date modified" field. Once you do that, you will have a reliable way of tracking when a record was last touched and you can routinely purge old records.

Author Comment

ID: 38316498
Not all of the tables have a date/time column. Can this not be done then? Is there some way of splitting off the older data? The size of the data base is becoming a problem.
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38316551
"older data" only makes sense if you DO have a column that indicates the date/age.
without that, impossible to do this (unless of course someone can look at the data and knows what could be deleted)

but then again, if this is a third-party application, you shall NEVER start deleting in the db directly. you shall contact the third-party to help you.

Author Comment

ID: 38316596
Thank You. Looks like this is not possible.

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question