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

• Jason B.: Do you have defaults set for the fields in the tables that you want set to NULL?
• Emilio Gort: @JasonB. We don't have NULL as default. That update is for data imported that come with many empty values, we notice in the application some irregular behavior due that in many places the app is checking for null. And we think is easy make an update in the database that look after for all the checking in a large app. we already change the SSIS for insert null when the field come with empty value
• Michael Green: Possible duplicate of Why Does the Transaction Log Keep Growing or Run Out of Space?
• Andriy M: How big can some of the tables be (in terms of the number of rows)? Updating, say, one million rows in one go might not be a good idea and so you might want to generate a more complex script for each table to update them in smaller chunks. Also, applying LTRIM(RTRIM(...)) to a column before comparing it to an empty string is probably redundant, WHERE columnname = '' is usually enough, because trailing spaces are ignored (thus, a string of spaces would match an empty string without explicit trimming).
• Emilio Gort: There are tables that have more than a million rows

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

• Emilio Gort: thanks very much, I'm using full recovery, I'm going to do it a try in the morning.