Update empty values with null
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:


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.
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