Not really screwed, but I wasted some quality time that could have been better spent with the kids.
Here's what went down:
We are developing a distributed system with Sql Server 2000 on the back end. We have a dev database located in our offices. During development we own the schema. When we get ready to deploy a new version of the application we deploy to our customers test servers. The test servers 'server-topology' is a copy of the production servers. There are multiple database servers and IIS web servers. We have a limited account to the test environment so that we can follow the test deployment and initial testing. We do not have access to the production servers so we aim to catch as many integration problems as possible during deployment on the test servers. (On the production servers we use extensive conditionally configured logging to understand what went wrong when it does. Sometimes it does!)
After deploying the application, the web services and the databases we get an error when the application tries to query one of the test databases. Part of the deployment is running a script that will update the schema of the database on the test server to the latest version. After looking into the issue for some time i decided to compare the schema of the database that we have by us (we store snapshots of every release) with that on the test server. I fire up schema compare of the DataDude (VS DBPro) and what do you know, the result shows that the test database doesn't have the latest changes applied.
Problem solved I thought!
Not so fast. There's a problem: The test database has all changes applied!
I connect to the database and see that the changes are very much present, they just doesn't show up in Datadudes schema comparison. We just got our version of the Red-Gate Sql Bundle, but I haven't had the time to try it out. Well this seemed like a good time to do a test run. According to Red-Gate's Sql Compare, the schemas do match. (While I expressed this in one paragraph in 5 minutes, I spent a few hours before I decided to manually look at the schema in the test database. I did trust Datadude.
So what went wrong? According to this post the schema comparison of the Datadude might be incorrect if the logins used to do the comparisons don't have permissions to access the required resources on the either server. On our server I have admin access, but on the test server I don't. Anyhow, the Red-Gate tool didn't have any issues with this and I will use that in the future.
Oh, the real problem executing the query? Well, that's a different post...
I don't know exactly why, but for some time, the first time I display a code window within Visual Studio there's intense disk activity for about 10 seconds. From that point on opening other source files is a snap.
It's late so I'm going to make this a short one.
Locating the problem:
1) I fired up SysInternals fileMon.
2) set it to monitor only the C: drive
3) turned off some disk intensive applications (Folding@Home, MSN Desktop search...)
4) Created a new project in Visual Studio
Stared at the fileMon log for about 30 seconds while Visual Studio was writing to the file:
C:\Documents and Settings\kim\Local Settings\Application Data\Microsoft\VisualStudio\8.0\1033\ExpansionsXML.xml
5) Googled for ExpansionsXML.xml
Turns out this is Visual Studio's cache for snippets. The size was about 800KB. I deleted the file and created a new project. Visual Studio created a new file, but this time the size was 210KB. It still took close to 10 seconds to display the first source file. I deleted all the snippet templates from:
C:\Program Files\Microsoft Visual Studio 8\VC#\Snippets\1033\*.*
and
C:\Program Files\Microsoft Visual Studio 8\VB\Snippets\1033\*.*
Now opening a source file was instantaneous! Not that I use snippets a lot (I'm using CodeRush), but I still wanted the snippets to stay around so I restored the deleted snippets.
At this point I took a step back since something was puzzling me. Why does Visual Studio need to recreate the snippet cache every time I open Visual Studio? I only have a small amount of custom snippets and I almost never update my snippets.
How can I tell Visual Studio to stop caching the snippets if the cache is present?
I made the ExpansionsXML.xml read-only and that was sign enough for Visual Studio to stay away from the file!!!
I'm only smacking my head since this has been bugging me for a few days...