Delete old data whilst new data being added

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 old data whilst new data being added

Author

Topic

Kristen
Test

22859 Posts

Posted - 2004-07-29 : 05:58:45

I need to delete about 10 million rows from a table whilst it is being added to (its a web log).

I'm thinking about deleting 250,000 rows at a time (which takes about 10 seconds).

I was going to do this in a loop using a WAITFOR DELAY of 10 seconds.

Will the resources be freed in the interim to allow other users to INSERT / UPDATE this table?

Here's my proposed code


DECLARE	@intRowCount	int,
	@intErrNo	int

SET ROWCOUNT 250000

SELECT	@intRowCount = 1, -- Force first loop iteration
	@intErrNo = 0

WHILE @intRowCount > 0 AND @intErrNo = 0
BEGIN
	DELETE MyLogTable
	WHERE  MyDate <= DATEADD(Month, -1, GetDate())
	SELECT	@intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
	WAITFOR DELAY '000:00:10'
END

SET ROWCOUNT 0

Thanks

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2004-07-29 : 06:04:32

As long as it's not run in a transaction it will commit each batch and release the locks.

==========================================
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.

Kristen
Test

22859 Posts

Posted - 2004-07-29 : 06:48:18

Cheers Nigel.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources