Update empty values with null

Question

I create a Job that runs the query below for all databases in a server in order to update empty values with null in all columns for all tables. The problem I'm facing is this query literally killed the sql server, was in a dev enviroment.

I notice the log for the first DataBase grew up from 6gb to 80gb and left the server without space.

Is there a better way to achieve update all empty columns with null in all Tables without crashing the server?

This query generate around 5,000 update statements for each database

I plane to run this kind of cleanup once a week.

SELECT 
ROW_NUMBER ( )   
    OVER (ORDER BY [object_id] DESC) AS iRow
,name,[object_id]
INTO #temp
FROM sys.Tables
WHERE 
name NOT LIKE 'old%'
AND name NOT LIKE '%old'
AND name NOT LIKE '%copy'
AND name NOT LIKE 'copy%'
AND name <> 'sysdiagrams'


--SELECT * FROM #temp 
DECLARE @intFlag INT,@endFlag INT

SELECT @intFlag= MIN(iRow) FROM  #temp 
SELECT @endFlag= MAX(iRow) FROM  #temp 

CREATE TABLE #temp2(name VARCHAR(250),tablename VARCHAR(250))

--SELECT * FROM #temp
WHILE (@intFlag<=@endFlag)
    BEGIN
        INSERT INTO #temp2
        SELECT c.name,(SELECT name FROM #temp WHERE iRow=@intFlag) AS TableName
        FROM syscolumns c
        INNER JOIN systypes t ON c.xtype=t.xtype 
        WHERE id =(SELECT [object_id] FROM #temp WHERE iRow=@intFlag)
        AND isnullable = 1 AND c.xtype IN(99,167,175,231,239)
        order by c.name
        SET @intFlag = @intFlag + 1;
    END

DECLARE @SQLquery AS VARCHAR(MAX) = ''

SELECT @SQLquery +='UPDATE ['+tablename+'] SET ['+ name+'] = NULL WHERE LTRIM(RTRIM(['+name+']))='''';'
FROM #temp2

--SELECT @SQLQuery
EXECUTE (@SQLquery) 

DROP TABLE #temp
DROP TABLE #temp2

Answer 1

I believe the problem is that you are executing all 5000 update statements as a single transaction. You need to break them up so each one is done in its own transaction. Then there are two options:

1. If you are in simple recovery mode (or can temporarily switch the database to simple recovery mode), then breaking them up should be enough for the database to automatically truncate the log after each transaction.
2. If you are in full recovery mode, you'll probably need to do a transaction log backup after each update statement to allow the log to be truncated.

So the last part of your query would look like this:

DECLARE @SQLquery AS VARCHAR(MAX)
DECLARE @name AS VARCHAR(250)
DECLARE @tablename AS VARCHAR(250)

DECLARE columns_cursor CURSOR FOR  
  SELECT name, tablename
  FROM #temp2
OPEN columns_cursor

FETCH NEXT FROM columns_cursor INTO @name, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
  @SQLquery ='UPDATE ['+@tablename+'] SET ['+ @name+'] = NULL WHERE LTRIM(RTRIM(['+@name+']))='''';'
  --SELECT @SQLQuery
  EXECUTE (@SQLquery)
  BACKUP LOG db_name TO DISK = 'backup_path' -- Not needed in simple recovery mode
  FETCH NEXT FROM columns_cursor INTO @name, @tablename
END

CLOSE columns_cursor
DEALLOCATE columns_cursor
DROP TABLE #temp
DROP TABLE #temp2