cleanup of files after register for data compare
ccollins
Posts: 64
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?
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?
Comments
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!
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;
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.
-- Missing syscomments entry. Please check your database
Why does a data compare routine have schema items in the temp file(s)?
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
The Database.Dispose() most not be working properly.
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.
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.