While we use TFS (Team Foundation Server) for all of our current development projects, we do maintain a SourceGear Vault server for most if not all of our legacy projects.  I've had nothing but a great experience with Vault over the past 3 or 4 years that I've used it.

Recently, however, I moved the server and SQL databases over to a new Windows Server 2008 x64 system with SQL Server 2005 SP2.  Everything seems to transition smoothly.  This particular SCC system does not get a lot of activity due to the nature of what data it maintains, but about a month after the upgrade one user suddenly couldn't access the system with authentication errors.  Likewise, I was unable to access several pages on the administrative Vault website.  The error reported was 'Object reference not set to an instance of an object.'

The next thing I did was open the Sql Profiler and watch the activity as it pertained to the Vault database (sgvault).  I noticed that it was attempting to execute a stored procedure by the name of spgettreestructure with the repository id and the transaction id.  Attempting to run the statement directly, I was presented with a SQL Server error:

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

This had me a little worried, but I opened up a ticket with SourceGear support.  After a few emails back and forth, a GoToMeeting, I was referred to Pinalkumar's blog page.  It turns out this was a bug in SQL Server that was fixed in the Cumulative update package 6 for SQL Server 2005 SP2.  Applying the patch (http://support.microsoft.com/default.aspx/kb/946608/LN/) fixed the issue :)