4

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?

artofsql
  • 613
  • 4
  • 11
  • I can see you've done this as a "self answer" so you can add something to stackoverflow. But there is no actual question asked here. And your "question" would likely be closed as too broad anyway. Self answer does not exempt you from the normal question standards. – JK. Dec 09 '14 at 00:39
  • 1
    Hi JK, Thank you for pointing out that I had forgotten the question at the end. A copy and paste error it seems. To your second point: the question may seem broad but troubleshooting third party applications can be just that difficult and ambiguous so sometimes this is as specific as it can get and only after finding the resolution can you better see what the perfect question would have been. It seemed the best approach would be to structure the question based on what I new back then, the same questions other dealing with this issue would have and search for to help them reach the solution. – artofsql Dec 09 '14 at 02:07

1 Answers1

5

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.

forfiles -p “C:\Users\<your username>\AppData\Local\Temp\Red Gate” /s /m *.* /d -1 /c “cmd /c del @path”

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/

artofsql
  • 613
  • 4
  • 11
  • SQL Source Control 3.8.4+ and SQL Data Compare 11.1.6.9+ should do a much better job of cleaning up temporary files created in the session. They are available on the [frequent updates](http://documentation.red-gate.com/display/SOC3/Turning+on+Frequent+Updates) channel. SQL Source Control will also now attempt to clean up files left by previous sessions. Source: I work for Red Gate, here are the [release notes](http://documentation.red-gate.com/display/SOC3/SQL+Source+Control+3.8+release+notes). – Graham Mar 11 '15 at 18:40
  • Interesting. Red Gate support told me they were going to notify me when this issue had been addressed but hadn't yet. – artofsql Mar 24 '15 at 14:23
  • This was my attempt to inform you (and any other interested parties). In hindsight I should have emailed you in the original support ticket as well. Apologies for any confusion, this is why I usually stick to writing code. – Graham Apr 14 '15 at 22:40
  • @Graham - No worries. The team I work with and I do appreciate your following up on this! We are working with the updated version and will follow up with support if we have further issues. – artofsql Apr 16 '15 at 21:13