Troubleshooting System.OutOfMemoryException during comparison
Published 05 June 2013
This error means there isn't enough storage available for the data comparison.
SQL Data Compare has been tested on extremely large databases (up to terabytes in size), and is very scalable. There are two situations where an out of memory situation can occur:
There isn't enough temporary storage
Redgate tools store temporary data in the %TMP% folder. By default, this folder is in your Windows profile folder (eg C:\Users\<username>\Temp). If you don't have much space on the hard drive, this folder may fill up, causing memory errors.
To fix this, you can have Redgate tools use a different location for temporary files on a different hard drive with more space. For example, to use the folder RGtemp on drive D:
- On the Start menu, right-click Computer and select Properties.
- In the Advanced tab, click Environment Variables.
- Click New.
- In the Variable name field, type: RGTEMP
- In the Variable value field, type: D:\RGtemp
- Click OK.
- Close Management Studio and standalone Redgate tools.
- Log out of Windows and log in again.
All Redgate tools will use the folder you specified to save temporary data. You can test this by running a comparison in Data Compare (or running an operation in another Redgate tool) and checking that files are added to the new location.
To undo this and have Redgate tools use the default folder, delete the RGTEMP variable from the Environment Variables list, and follow steps 7 and 8 again.
Very large BLOBs are being compared
If your database makes heavy use of Binary Large Objects such as TEXT and IMAGE fields, each individual field from both databases needs to be loaded into memory at once. If these fields are large, you may exhaust your physical memory when trying to compare them. For instance, if you have got an individual BLOB column that is 1GB in size in one database and 1GB in the other database, 2GB of memory is needed to compare them. Unfortunately your only courses of action are to use a machine with more memory (a 64-bit machine may be needed to address more than 2GB of memory in a single process) or ignore these columns and deploy them manually.
To detect all BLOB columns in a database, these queries are useful:
SELECT o.[name] AS [Table Name],c.[name] AS [Column Name], t.[name] AS [Column Type] FROM sys.all_columns c INNER JOIN sys.all_objects o ON c.object_id = o.object_id INNER JOIN sys.types t ON c.system_type_id = t.system_type_id WHERE c.system_type_id IN (35, 165, 99, 34, 173) AND o.[name] NOT LIKE 'sys%' AND o.[name] <> 'dtproperties' AND o.[type] = 'U' GO