URGENT DELETE QUESTION - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

URGENT DELETE QUESTION

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2007-03-02 : 06:29:40

HI,
I need to delete 10 Million records from a table that were imported into our SQL server database by accident. We have run out of disk space and need to do a delete urgently.
I have written this delete statement:
set rowcount 5000

/* 1) Delete first 10,000 Where Event ID is NULL on the Sign_Ons Table */
delete from sign_ons where K_EVENT_ID is NULL

/*If delete takes place than loop each time deleting 5000 recs, loop until there's no record left to delete*/
while @@rowcount > 0
begin
delete from sign_ons where K_EVENT_ID is NULL

end

The problem is know the transaction log will grow and we have no space while we do the delete. Should I force a checkpoint with the checkpoint command after each delete in the loop. Or will this slow it down. the table in question is critical to our websites and is constantly being accessed?
The recovery Model is simple.
The table is currently 10 Gig In Size: The resrved Space by Sp_SpaceUSed is showing 10 Gig. So I'm assuming the transaction log will approx grow by that amount
Can someone please advise ASAP
Many thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 07:14:46

Try changing recovery mode to bolk_logged or simple first.

Peter Larsson
Helsingborg, Sweden