Situation:
We use local SQL Server instances on our laptops and tie these to our SVN repositories using Red-Gate’s SQL Source Control. Initially when I was issued this laptop performing a “get latest” and “commit” synchronizations went relatively fast (< 2 minutes for 1/4 through 4/4). Then after a few weeks it became very apparent that the process had slowed dramatically and now the process takes around 20 minutes for a single synchronization.
At this point I had begun trying every basic troubleshooting tactic for this problem with SQL Source Control doing everything from uninstall to reinstall; to upgrading the version to the latest and even stepping down to various older versions. I tested SSC with a local repository to rule out network, and with a “working folder”, and even used the “Just evaluating” lite repository. They all were very slow; as slow as every other option taking at least 20 minutes to perform a single sync.
When that didn’t rectify the issue I contacted Red Gate through our support contract. Long story short, this went nowhere fast. After months of going through many different scenarios we didn't seem to be any closer to a resolution.
Eventually I discovered that suddenly I could sync a database much faster (around 5 minutes) if there was no static data linked to the repository. But the problem was that the data had to be linked (either SSIS configuration data or RI static data) so this was not a viable option but it does help pinpoint the real issue a bit more.
Now the time to sync has crept up to around 2 hours for a single sync. There are several other developers also dealing with this issue one of which has to wait up to 6 hours to complete a get latest.
Other information:
• No other applications run slow on this laptop
• The drive is an encrypted SSD that has been configured to use 1GB of RAM for caching
• We have tested with the anti-virus / defense software disabled and it made no difference
What can be the cause of this?
The issue is primarily with Red-Gate’s SQL Compare \ Data Compare.
Symptom: SQL Source Control calls SC & SDC when synchronizing data to a repository and this is the process that takes a very long time to complete.
Root Cause: SC and SDC create a plethora (literally thousands per second) of temp files in the “%USERPROFILE%\AppData\Local\Temp\Red Gate” folder during the comparison of the transient and working base folders and for whatever reason these applications do not always remove old files. In time the number of orphaned files accumulates and the result is a fragmented folder that is very slow to access. After working with Red Gate for months on this issue they were finally able to reproduce this issue in their lab and it has been formally accepted as a bug under SC-7647.
In my case I had discovered over 100,000 files in this temp folder. Once the files were cleared the time required to synchronize to SVN went back down to around 2 minutes even with static data.
Until Red-Gate releases a fix for this issue the work-around is to use a process (scheduled task or other) to clean up files older than a period of time. The reason for that is I did manage to mess up a linked database by killing some current files in the temp folder so an arbitrary delete isn't recommended.
The following command line statement will attempt to remove any files older than 1 day.
For more information check out my blog post on this subject at http://artofsql.net/guides/improving-sql-source-control-and-sql-data-compare-performance/