Troubleshooting System.OutOfMemoryException during comparison

Category: Troubleshooting & error messages
Date: 16 Jul 2009
Product: SQL Data Compare
When comparing the data in certain databases, SQL Data Compare may stop with this error message:
System.OutOfMemoryException

This is not a software bug per se, but simply an indication that there is not enough storage available for the data comparison.

SQL Data Compare has been tested on extremely large databases (up terrabytes in size), and is very scalable. There are two situations where an out of memory situation can occur:

- There is not enough available temporary storage
====================================
SQL Data Compare make heavy use of caching to disk, by storing data in the %TMP% folder. This folder location is configurable, but is by default in your personal Windows profile folder, for instance "c:\documents and settings\username\temp". The %TMP% environment variable can be changed in the "My computer" area of your system, but we recommend setting a temporary file location specific to Red Gate SQL Tools by setting the %RGTEMP% environment variable in the current session.

For instance, if your system drive is low on space, you can use a different folder on a larger disk, in this example, by creating a folder called RGTEMP on drive D:

- Create the folder d:\rgtemp
- Open a command prompt
- Type "SET RGTEMP=d:\RGTEMP"
- Type the command to start Data Compare: "c:\program files\red gate\sql data compare 8\RedGate.SQLDataCompare.UI.exe"
- Run a comparison, note that files are appearing in d:\rgtemp

- 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 synchronize them manually.

To detect all BLOB columns in a database, these queries are useful:

/ SQL 2005, 2008, Express /
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

/ SQL 2000 /
SELECT o.[name] AS [Table Name],c.[name] AS [Column Name],
t.[name] AS [Column Type]
FROM dbo.syscolumns c
INNER JOIN dbo.sysobjects o
ON c.id = o.id
INNER JOIN dbo.systypes t
ON c.xtype = t.xtype
WHERE c.xtype IN (35, 165, 99, 34, 173)
AND o.[name] NOT LIKE 'sys%'
AND o.[name] <> 'dtproperties'
AND o.xtype = 'U'
GO