Hekp - Deleting Table/Records
-
Some how a process I wrote that logs errors to a SQL Server 2000 Table got stuck in a loop over the weekend and wrote oh almost 400 million records to a SQL Table on me. How can I kill this? Trying to delete the table or using a T-SQL Delete Command seem to just hang because the table is too big. Help!
-
You can use a truncate statement to make the table empty. But this will delete all your data.
If you need part of the data, you can read the data required into a temp table, and then rename the table to the original table.
-
In addition to sa24's advice, you can also use this delete-batch.It will take a while, but since transaction are kept small, interferance should be minimal, but first decide how you'll handle the transactionlog. Maybe even switch to simple recovery.
SET NOCOUNT ON
SET ROWCOUNT 10000
WHILE 1 = 1
BEGIN
DELETE TableName WHERE <Your Condition Here>
IF @@ROWCOUNT = 0 BREAK
-- CHECKPOINT -- if you wish
END
SET ROWCOUNT 0
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks guys!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply