If you run out of space the Query will rollback. You need to use transactions and delete the rows in batches and depending on what type of recovery model the database is in you will need to do LOG backups or Checkpoints.
Here is a stored procedure i built to remove milions of rows from an AX database in a safe way. Note that if your database is using a DPM system or something like that you will have to figure out someway to handle the LOG backups. Feel free to use it and edit it as you please.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Daniel Björk
-- Create date: 2017-07-27
-- Description: Delete rows in batches
-- =============================================
CREATE PROCEDURE csp_DeleteINVENTSUMLOGTTS
-- Input parameters
@DeleteOlderThanDays INT,
@MaxDeletedRows INT = -1,
@MaxTimeMinutes INT = -1,
@BatchSize INT = 25000,
@LogLevel INT = 1 -- 0 = OFF, 1 = Only Total Values, 2 = Verbose
AS
BEGIN
SET DEADLOCK_PRIORITY LOW;
-- Logical Default Values
DECLARE @StartMessage AS NVARCHAR(MAX);
DECLARE @StartTime DATETIME = GETUTCDATE();
DECLARE @DeletedRows INT = 1;
DECLARE @TotalDeletedRows INT = 0;
DECLARE @Error int = 0
DECLARE @ErrorMessage NVARCHAR(max)
-- Print startup parameters
IF (@LogLevel >= 1)
BEGIN
SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTime,120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + (SELECT QUOTENAME(schemas.name) FROM sys.schemas schemas INNER JOIN sys.objects objects ON schemas.[schema_id] = objects.[schema_id] WHERE [object_id] = @@PROCID) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Parameters:' + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + '@DeleteOlderThanDays = ' + ISNULL(CAST(@DeleteOlderThanDays AS NVARCHAR(32)), 'NULL') + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + '@DataAreaIdParm = ' + ISNULL(CAST(@DataAreaIdParm AS NVARCHAR(32)), 'NULL') + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + '@MaxDeletedRows = ' + ISNULL(CAST(@MaxDeletedRows AS NVARCHAR(32)), 'NULL') + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + '@MaxTimeMinutes = ' + ISNULL(CAST(@MaxTimeMinutes AS NVARCHAR(32)), 'NULL') + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + '@BatchSize = ' + ISNULL(CAST(@BatchSize AS NVARCHAR(32)), 'NULL') + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + '@LogLevel = ' + ISNULL(CAST(@LogLevel AS NVARCHAR(32)), 'NULL') + CHAR(13) + CHAR(10)
PRINT @StartMessage
END
-- Validate input parameters
IF (@DeleteOlderThanDays < 0)
BEGIN
SET @ErrorMessage = 'Invalid value of @DeleteOlderThanDays' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF (@MaxDeletedRows < -1)
BEGIN
SET @ErrorMessage = 'Invalid value of @DeleteOlderThanDays' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF (@MaxTimeMinutes < -1)
BEGIN
SET @ErrorMessage = 'Invalid value of @MaxTimeMinutes' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF (@BatchSize <= 0)
BEGIN
SET @ErrorMessage = 'Invalid value of @BatchSize' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
-- Delete Logic
WHILE (@DeletedRows > 0
AND (DATEADD(Minute, @StartTime, @MaxTimeMinutes) > GETUTCDATE() OR @MaxTimeMinutes = -1)
AND (@MaxDeletedRows > @TotalDeletedRows OR @MaxDeletedRows = -1))
BEGIN
BEGIN TRANSACTION
-- Delete from InventSumLogTTS
DELETE TOP (@BatchSize)
FROM [dbo].InventSumLogTTS
WHERE
DATEDIFF(DAY, @StartTime, DATEADD(DAY,@DeleteOlderThanDays, DATESTATUS)) < 1
-- Compute totals
SET @DeletedRows = @@ROWCOUNT;
SET @TotalDeletedRows = @TotalDeletedRows + @DeletedRows
IF (@LogLevel >= 2)
BEGIN
PRINT 'Rows deleted so far: ' + CAST(@TotalDeletedRows AS NVARCHAR(32))
PRINT 'Total runtime in sec so far: ' + CAST(DATEDIFF(Second, @StartTime, GETUTCDATE()) AS NVARCHAR(32))
END
COMMIT TRANSACTION
-- Only needed if you are using Recovery Model Simple or you have a small log disk that might fill up
-- CHECKPOINT; -- if simple
-- BACKUP LOG ... -- if full
END
IF (@LogLevel >= 1)
BEGIN
PRINT 'Total rows deleted: ' + CAST(@TotalDeletedRows AS NVARCHAR(32))
PRINT 'Total time in sec: ' + CAST(DATEDIFF(Second, @StartTime, GETUTCDATE()) AS NVARCHAR(32))
END
END
GO
4gbopen space to90kb. Awesome. – blackandorangecat Aug 01 '17 at 00:48