SQL Server Used Transaction Log Space Not Decreasing After Long UPDATE Finishes

Question

In SQL Server, I have a table (dbo.MYTABLE) containing 12 million rows and 5.6 GB storage.

I need to update a varchar(150) field for each record.

I perform the UPDATE operation in WHILE loop, updating 50K rows in each iteration.

Transaction log seems not to free after each iteration and keeps growing. Even, after all the UPDATE process finished, Transaction Log space is not returned.

My question is that, why used transaction log space never decreases even UPDATE is complete. Code is below:

DECLARE @MAX_COUNT INT;
DECLARE @COUNT INT;
DECLARE @INC INT;

SET @COUNT = 0;
SET @INC = 50000;

SELECT @MAX_COUNT=MAX(ID) FROM  dbo.MYTABLE(NOLOCK)
WHILE @COUNT <= @MAX_COUNT
BEGIN

    UPDATE dbo.MYTABLE
    SET NEW_NAME = REPLACE(NAME,' X','Y'))
    WHERE ID > @COUNT AND ID<=( @COUNT + @INC)

    SET @COUNT = (@COUNT + @INC)
END

There is no Begin Transaction... Commit wrapped around this, so this is still being considered one big transaction and will be logged. — SS_DBA, Oct 25 '16 at 12:09
In Full Recovery Mode, the Log will not get truncated until you perform a Transaction Log Backup and Truncate the log to free up space. — SS_DBA, Oct 25 '16 at 12:40

score 0 · Answer 1 · answered Oct 25 '16 at 13:48

0

if you don't do Transaction Log Backups, switch to simple:

USE [master]
GO
ALTER DATABASE [YOUR_DB] SET RECOVERY SIMPLE WITH NO_WAIT
GO

answered Oct 25 '16 at 13:48

Christoph Staudinger

75
1
9

SQL Server Used Transaction Log Space Not Decreasing After Long UPDATE Finishes

1 Answers1