March 21, 2007 at 5:49 pm
Hi,
I have inherited a database with a large number of records, which many can be deleted. I have a list of records found in the main table that I want to delete (mainly by date). There is a key this table that is used as a foreign key into about 20 other tables, and each of these tables have a few thousand records each for one key. I would like to delete all this old data from my database but want to make sure I don't lock the database or bring the database server to a crawl. What's the best way to accomplish this? Unfortunately we don't have a dba on site, and these databases have not been maintained very well.
We tried two mini-tests: One test where we queried the main table for the old records and create a temporary table. And from there we then iterate through the keys to delete the associated records from the various other tables. The second test used a cursor to iterate through the main table, which then deleted the associated records from the other tables. The cursor option ran faster, so that is the option we went with.
Then we ran the script to delete a subset of the records in our database (about 2500 in the main table, but many thousand associated records). This took about 2 1/2 hours, and seemed to lock our database (meaning I tried a query on the same database and it seemed to hang). So I think we need to do some improvements to the script, or find a better way to accomplish the same thing.
We are also concerned about running out of space (by filling up transaction logs, and the tempdb). We still have about 18000 records out of our main table to delete.
Any tips or articles would be greatly appreciated.
Thanks,
Beth
March 21, 2007 at 7:03 pm
If you had a table that had all of the keys in it, you could iterate through that table, delete all of the records based on that key, then delete the record. You run it for so many records, stop back up your log. You can restart it at any time. Also you can change the counter so that you either delete more or less at a time.
declare @key int
declare @count int
set @count = 0
Select top 1 @key = MyKey from KeyTable
while (@count < 100 and @key is not null)
begin
delete Table1 where MyKey = @key
delete Table2 where MyKey = @key
delete Table3 where MyKey = @key
insert into KeyTableArchive values (@key)
delete KeyTable where MyKey = @key
set @count = @count + 1
set @key = null
select top 1 @key = MyKey from KeyTable
end
Russel Loski, MCSE Business Intelligence, Data Platform
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply