SQL Server Performance Forum – Threads Archive

Deleting a table

Hi,
I have a table which has 300,000 records, and is there anyway where I can delete the rows of the table upto a particular date. i checked booksonline i couldn’t find it. I would really appreciate for any help regarding this.
Thanks in advance
Rajesh

Is there a column in the table that contains said date? If so, then something like DELETE from yourtable
WHERE thedate < ‘somedate’ Be careful about other tables that might have relationships to the deleted records, though. Also, it’s easy to make an error, so I generally test with a SELECT before using DELETE with the exact same criteria.

Hi Merrill,
Thanks for the reply, Iam trying to delete the backupset table in msdb which has FK constraint. So the above ssolution doesn’t work.

Then you need to delete the child records first
Otherwise Look for On Delete Cascade in BOL
Madhivanan Failing to plan is Planning to fail

Are you saying, your backupset system table in the MSDB contains 300,000 rows? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

You’re risking your system by querying system tables directly, you can take help of sp_purge_jobhistory in order to delete those rows and never attempt to delete directly. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks Madhivanan, Frank, and Satya for replying,
I tried sp_purge_jobhistory but it doesn’t remove any of the history records, I also tried giving this with the @job_name, it executes successfully saying "0 history entries purged"
Thanks again all for replying
Rajesh

Use sp_delete_backuphistory sp_delete_backuphistory [ @oldest_date = ] ‘oldest_date’
from BOL:
Deletes the entries in the backup and restore history tables for backup sets older than oldest_date. Because additional rows are added to the backup and restore history tables when a backup or restore operation is performed, sp_delete_backuphistory can be used to reduce the size of the history tables in the msdb database.

]]>