cleanup of files after register for data compare

ccollins

If I perform the following code, the temp files are removed:

releasedb.Register(new ConnectionProperties(sqlsvrname, releaseschemaname), m_dbOptions);
updatedb.Register(new ConnectionProperties(sqlsvrname, databasetoupdate), m_dbOptions);
TableMappings tablemappings = new TableMappings();
releasedb.Dispose();
updatedb.Dispose();


If I perform the following code, not all of the temp files are removed. The original size database registration files remain and I cannot delete them until I terminate the application:
releasedb.Register(new ConnectionProperties(sqlsvrname, releaseschemaname), m_dbOptions);
updatedb.Register(new ConnectionProperties(sqlsvrname, databasetoupdate), m_dbOptions);
TableMappings tablemappings = new TableMappings();
releasedb.RegisterForDataCompare(releasecon, m_dbOptions);
updatedb.RegisterForDataCompare(updatecon, m_dbOptions);
releasedb.Dispose();
updatedb.Dispose();


What changes do I need to implement in order for all of the temp files to be removed?

Brian Donahue

Hi,

Thanks a lot for addressing this here in the forum.

I'm assuming this isn't all of your code here -- the class that will potentially use the most storage is the ComparisonSession class. If you don't dispose any instances of ComparisonSession, you may have this problem. The Database objects contain your database schemas, so they probably don't use a lot of space.

Hopefully this helps!

ccollins

I perform the schema compare in another section of the code. The schema compare and synchronize section cleans up all of the temporary files.

If needed, the data compare and synchronize section is called. When it is the following code is called.

If I bypass the PerformUpdateSchemaData function and attempt to clean up, not all of the files are removed. If I allow the call to PerformUpdateSchemaData, the same files are left over.

SchemaGenUpdateEventArgs syncevent = new SchemaGenUpdateEventArgs();
syncevent.ModuleName = "SynchronizeData";
syncevent.ModuleProgress = 0;
syncevent.ModuleProgressText = "Initializing...";
OnUpdateProgressed(syncevent);
updatedatascript = string.Concat(workingdir, "\\", databasetoupdate, "_updatedata.sql");
// connect to the release database, then the tobeupdated database
RedGate.SQLCompare.Engine.Database releasedb = new RedGate.SQLCompare.Engine.Database();
RedGate.SQLCompare.Engine.Database updatedb = new RedGate.SQLCompare.Engine.Database();
releasedb.Register(new ConnectionProperties(sqlsvrname, releaseschemaname), m_dbOptions);
updatedb.Register(new ConnectionProperties(sqlsvrname, databasetoupdate), m_dbOptions);
syncevent.ModuleProgress = 45;
syncevent.ModuleProgressText = string.Concat("Comparing...", releaseschemaname, "-to-", databasetoupdate);
OnUpdateProgressed(syncevent);
// perform data update if schema update is successful
TableMappings tablemappings = new TableMappings();
ConnectionProperties releasecon = new ConnectionProperties(sqlsvrname, releaseschemaname);
ConnectionProperties updatecon = new ConnectionProperties(sqlsvrname, databasetoupdate);
releasedb.RegisterForDataCompare(releasecon, m_dbOptions);
updatedb.RegisterForDataCompare(updatecon, m_dbOptions);
tablemappings.CreateMappings(releasedb.Tables, updatedb.Tables);
syncevent.ModuleProgress = 50;
syncevent.ModuleProgressText = string.Concat("Updating production data...", databasetoupdate);
OnUpdateProgressed(syncevent);
returnSynchronizeDataval = PerformUpdateSchemaData(releasedb, updatedb, tablemappings, databasetoupdate, updatedatascript, sqlsvrname);
// close and release schema compare objects
tablemappings = null;
releasedb.Dispose();
updatedb.Dispose();
releasedb = null;
updatedb = null;
return returnSynchronizeDataval;

ccollins

When I attempt to manually remove the files, I receive an access denied error. I allow the code to completely dispose and clean up, returning to the main routine when all of the red-gate objects should have been released. It is not until the entire application terminates that I can remove the remaining temp files.

ccollins

After all of the Red Gate objects have been disposed and before the applications returns/terminates, these files remain:

Directory of C:\Documents and Settings\ccollins\Local Settings\Temp\Red Gate6/04/2007 08:51 AM 0 BE2a027e17-b6f6-49f1-8cb6-ff27e14f0ab9.TF
06/04/2007 08:51 AM 0 BE5857e4a3-8ee7-44dd-99e0-b35df4135b69.LF
06/04/2007 08:51 AM 0 BEd5c75386-9f11-4031-ba68-f0782c410d1e.BF
06/04/2007 08:49 AM 0 SC_BS326b1ea7-45a7-45af-a016-fab57a032ed4.tmp
06/04/2007 08:50 AM 0 SC_BS7541f91a-3a83-44b3-af35-181d14ffab83.tmp
06/04/2007 08:51 AM 1,786,263 SC_BSca7b2277-65b7-4ca3-9aa4-edaf09dd9c85.tmp
06/04/2007 08:50 AM 0 SC_BSef544ce6-71a5-4cbe-b9fe-ddf423f27379.tmp
06/04/2007 08:51 AM 1,867,575 SC_BSf98b87d0-e265-4b71-89c5-721bdec12d5c.tmp
06/04/2007 08:49 AM 0 SC_BSfa49ef2c-5c24-4354-88fb-62a1276ffd84.tmp


After the manual removal attempt and before the applications returns/terminates, these files remain:
Directory of C:\Documents and Settings\ccollins\Local Settings\Temp\Red Gate
06/04/2007 08:51 AM 0 BE2a027e17-b6f6-49f1-8cb6-ff27e14f0ab9.TF
06/04/2007 08:51 AM 0 BE5857e4a3-8ee7-44dd-99e0-b35df4135b69.LF
06/04/2007 08:51 AM 0 BEd5c75386-9f11-4031-ba68-f0782c410d1e.BF

The files are removed upon the application's termination.

If this is a Microsoft .Net issue, should I create a static thread or class to work around this?

When synchronizing one database or database type at a time, there are few disk space issues. When synchronizing an entire system there are disk space issues, when I end up with 8-20GB of temp files depending on the size of data tables.

Brian Donahue

Have you tried opening one of these using Notepad to see what's actually in there?

ccollins

The two SC_ temporary files have stored procedure code in them. The last line of each contains this:
-- Missing syscomments entry. Please check your database

Why does a data compare routine have schema items in the temp file(s)?

ccollins

The last test I ran, left the following files, even after the application terminated:

06/04/2007 10:25 AM 0 BE784d44cd-3424-4e26-82ea-556b487a09f8.BF
06/04/2007 10:25 AM 0 BE8cf1c8a7-4dc3-4e75-9c77-fde8d094a0f1.LF
06/04/2007 10:26 AM 1,867,587 SC_BS184dfea8-48bb-4e02-b262-b690cc99d5c2.tmp
06/04/2007 10:24 AM 0 SC_BS269f1ed0-9829-4a23-8dba-635186873f8c.tmp
06/04/2007 10:26 AM 1,786,263 SC_BS9081a2e8-2cf2-4fca-b1c1-ecc8f06487b9.tmp
06/04/2007 10:25 AM 0 SC_BSa2950edb-6225-4509-8b3a-f64f1d264962.tmp
06/04/2007 10:25 AM 0 SC_BSb88c8dd3-162c-4819-ae46-22f6af895425.tmp
06/04/2007 10:24 AM 0 SC_BSc839c9f0-1ba8-4173-8a95-5b14782f2f3a.tmp

Brian Donahue

RegisterForDataCompare is going to store some schema information, which would explain the stored procedure code.

The Database.Dispose() most not be working properly.

ccollins

Yes, after Database.Register and before Database.RegisterForDataCompare, the Database.Dispose() cleans up all temporary files.

After Database.Register and after Database.RegisterForDataCompare, the Database.Dispose() does not clean up all of the temporary files. There are files that are left over and still locked by a process.

This is a huge problem in production, where we update the same type of database and the tables being rebuilt and/or updated have 2GB or more of data. Disk space is being used up in a week or so, if we do not manually remove the red-gate temp files.

ccollins

Is it possible to use the tempfiles class to assist in the removal of the temp files after the classes are disposed?

Brian Donahue

Can you simply use two sets of database objects: one for SQL Compare and one for Data Compare?

ccollins

Using seperate objects for the schema and data synchronization is a big help. I went a step further and created separate routines for each. This allows each routine to dispose and release resources before returning to the calling party.

I took another step of creating an arraylist of temporary files RGDC-*.* and Red Gate\*.* after a single synchronization is completed. This allowed me to remove any files that remained after .Dispose() was complete and the red gate objects freed.


It appears that reusing the Database object for data compare after a schema compare prevents the .Dispose() from working completely.

Brian Donahue

Thanks a lot for pointing this out -- I'll make sure it gets flagged to be fixed in a future Toolkit release.