0

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
  • Do you have defaults set for the fields in the tables that you want set to NULL? – Jason B. Jul 21 '16 at 19:38
  • @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 – Emilio Gort Jul 21 '16 at 19:42
  • 1
  • 1
    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). – Andriy M Jul 22 '16 at 6:18
  • There are tables that have more than a million rows – Emilio Gort Jul 22 '16 at 12:10
2

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
  • thanks very much, I'm using full recovery, I'm going to do it a try in the morning. – Emilio Gort Jul 21 '16 at 21:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.