I bet you tried to run the whole delete in one transaction. Correct?
Once the transaction is complete, the log space can be reclaimed. Since the transaction never completed, the log file grew until it crashed the server.
Check out my blog entry on How to Delete Large Data.
http://craftydba.com/?p=3079
The key to the solution is the following, SIMPLE recover mode, DELETE in small batches, take FULL backup at end of purge. Select the recovery model that you want at the end.
Here is some sample code to help you on your way.
--
-- Delete in batches in SIMPLE recovery mode
--
-- Select correct db
USE [MATH]
GO
-- Set to simple mode
ALTER DATABASE [MATH] SET RECOVERY SIMPLE;
GO
-- Get count of records
SELECT COUNT(*) AS Total FROM [MATH].[dbo].[TBL_PRIMES];
GO
-- Delete in batches
DECLARE @VAR_ROWS INT = 1;
WHILE (@VAR_ROWS > 0)
BEGIN
DELETE TOP (10000) FROM [MATH].[dbo].[TBL_PRIMES];
SET @VAR_ROWS = @@ROWCOUNT;
CHECKPOINT;
END;
GO
-- Set to full mode
ALTER DATABASE [MATH] SET RECOVERY FULL;
GO
Last but not least, if the amount of remaining data after the delete is real small, it might be quicker to do the following.
1 - SELECT * INTO [Temp Table] WHERE (clause = small data).
2 - DROP [Original Table].
3 - Rename [Temp Table] to [Original Table].
4 - Add any constraints or missing objects.
The DROP table action does not LOG all the data being removed.
Sincerely,
John
