Truncate MSSQL data that is from 2011 and prior

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
Who is Participating?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
"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.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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)
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.
jimmylew52Author Commented:
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.
jimmylew52Author Commented:
Thank You. Looks like this is not possible.
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.

All Courses

From novice to tech pro — start learning today.