DELETE - Maintenance - 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)

DELETE - Maintenance

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-15 : 08:28:24

raasoori writes "I have to perform DELETE on a large table whats the best way to clean up the log space created by this huge delete command??"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-15 : 08:30:26

You can:

1. SET ROWCOUNT to a manageable number of rows, like 1,000,000 or less, and perform multiple DELETE operations with this setting until all rows are gone. Between each DELETE, run BACKUP LOG myDatabase WITH NO_LOG to truncate the log entries.

2. bcp out all the data you want to keep, then run TRUNCATE TABLE myTable, then bcp the data back in. This is not an option if your table is referenced by another table's foreign key constraint(s), or if you currently use log backups. You can of course disable the foreign keys and set the database to allow bulk-copy operations, do the work, then set them back, but you'd have to perform a full database backup immediately afterwards.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-10-15 : 09:09:28

The best way to perform this delete is to have enough hardware resources available to handle the dml while maintaining transactional integrity. The term "clean up" implies that the log is trash.

Jay White
{0}

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-15 : 11:25:14

Look at the shrink database command.
You can also do it from enterprise manager.
You will need to have truncated r backed up the log first but I guess you are in simple recovery mode to do this.

You can also detach the database, delete (or rename) the log file and then sp_attach_single_file but take a backup first.
And take a backup before and after the delete.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-15 : 14:35:04

Here is an example:


SET NOCOUNT ON

DECLARE @Count INT 
DECLARE @Error INT 

SELECT @Count = COUNT(*)
FROM SomeTable
WHERE Something < 60

SET ROWCOUNT 10000

WHILE @Count > 0
BEGIN
	BEGIN TRAN

	DELETE FROM SomeTable
	WHERE Something < 60

	SELECT @Error = @@ERROR

	IF @@ERROR = 0
		COMMIT TRAN
	ELSE
		ROLLBACK TRAN		

	CHECKPOINT 

	SELECT @Count = COUNT(*)
	FROM SomeTable
	WHERE Something < 60

END

SET ROWCOUNT 0
SET NOCOUNT OFF

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources