Repository is gone?!

[CareBear]

Half an hour ago our primary repository disappeared from Source Gear Vault 3.0.2.

We have two repositories in the database that is visible when I "select * from tblrepositories":

2 7D452494-91E7-4B5B-8F5E-6F30082ECA9A Work $ 33851 2232 26232 0 9631 1 0 1 0 2 0 2005-02-22 10:30:10.783 0 11212
3 BA3646F1-3B97-43E0-958D-623AFEC4DB9E Private $ 33749 82 486 0 43 1 0 1 0 2 0 2005-02-22 10:30:10.783 0 10825


But when we logon with Vault Admin or Vault Client, we only get the Private repository option.

After doing some SQL tracing we found that the "spgetrepositoryinfoall" procedure is ran to get details about a specific repository.

Running the following gives correct results:

"exec dbo.spgetrepositoryinfoall @repid = 3"

3 Private $ 33749 82 486 2090906 3989505 43 1 0 1 2 0 BA3646F1-3B97-43E0-958D-623AFEC4DB9E 382 0 43


But this sql returns NOTHING:

"exec dbo.spgetrepositoryinfoall @repid = 2"

What does this procedure do? What values in the database can have become corrupt?


These procedures looks perfectly fine:

exec dbo.spgetrepositoryusers @repid = 2, @active = 1
exec dbo.spgetrepositoryusers @repid = 3, @active = 1


I have checked with everybody in the office and nobody has done anything special for this to happen. Just suddenly we we're unable to checkout any files Visual Studio. After re-launching Visual Studio, Vaule Client or Vault Admin, only the "Private" repository option was available.

Hope anyone can help to resolve this quickly!


Regards,
Sondre

[lbauer]

We'll get our SQL Server specialist involved, but first I'd suggest trying to backup your database (if you don't have a recent backup) and then restarting MS SQL Server. Maybe even rebooting the machine, if that's possible.

[CareBear]

I've taken backup of the database yes, and I've just restarted both the web server and database server.

Still there are no changes, only the "Private" repository is returned from the server.

To futher help with the debugging:

Vault Server is running on an Windows 2003 Server, on the internal network (not accessible outside/public).

Vault Database is running on another Windows 2003 Server, on the internal network (not accessible outside/public).

It's easter now and there are some people on hollidays already, so the load on our source control is less than normal.


Is there any special database tables I should select on to look for corrupt information?

Regards,
Sondre

[jclausius]

Sondre:

Let's take a look at something.

SELECT currenttxid FROM sgvault.dbo.tblrepositories WHERE repid = 2

SELECT MAX(txid) FROM sgvault.dbo.tbltransactions WHERE repid = 2

Do these numbers match?

[CareBear]

Yes running these select statements returns the same value (33851).

/Sondre

[jclausius]

What are the results here:

SELECT * FROM sgvault.dbo.tbltreerevisionfolders WHERE txid = 33851 AND treelevel = 0

[CareBear]

That returns nothing, no records.

[jclausius]

What about this one:

SELECT TOP 10 * FROM sgvault.dbo.tblfulltreerevisionfolders where repid = 2 ORDER BY txid DESC

[CareBear]

repid txid
2 33850
2 32353
2 30353
2 28336
2 26335
2 24147
2 22011
2 20011
2 18009
2 16007

[jclausius]

Any rows at all here:

SELECT * FROM sgvault.dbo.tbltreerevisionfolders WHERE txid = 33851

Also, what does this return

SELECT repid FROM sgvault.dbo.tbltransactions WHERE txid = 33851

[CareBear]

Any rows at all here:

SELECT * FROM sgvault.dbo.tbltreerevisionfolders WHERE txid = 33851

Also, what does this return

SELECT repid FROM sgvault.dbo.tbltransactions WHERE txid = 33851

1. The first returns no records.

2. Returns the repid 2.

[jclausius]

This is confusing. Let's take a look at the transaction...

SELECT * FROM sgvault.dbo.tbltransactions WHERE txid = 33851
AND

SELECT * FROM sgvault.dbo.tbltransactiondetails WHERE txid = 33851

[CareBear]

This is confusing. Let's take a look at the transaction...

SELECT * FROM sgvault.dbo.tbltransactions WHERE txid = 33851
AND

SELECT * FROM sgvault.dbo.tbltransactiondetails WHERE txid = 33851

txid repid userid txbegin txend
-------------------- ----------- ----------- ------------------------------------------------------ ------------------------------------------------------
33851 2 2 2005-03-22 13:26:40.787 2005-03-22 13:26:41.317

(1 row(s) affected)

txid txitem txtype itemobjverid otherobjverid
-------------------- ----------- ------ -------------------- --------------------
33851 1 1 261431 252719

(1 row(s) affected)

[jclausius]

Is there any chance something happened within SQL Server which would cause the tree rows to disappear?

Anything in the Vault Server log? SQL Server log? The Windows Event Log? Anything?


The stored procedures for a transaction is pretty solid, so I'm trying to figure out what made the tree rows disappear.

Since this problem is keeping you from working, let's solve this problem first.

1) Make a complete backup of the database.

2) To get you up and running as fast as possible, we're going to have to take back the last transaction. The following queries should help.

UPDATE sgvault.dbo.tblrepositories SET currenttxid = 33850 WHERE repid = 2
DELETE FROM sgvault.dbo.tbltransactiondetails WHERE txid = 33851
DELETE FROM sgvault.dbo.tblfsobjectversions WHERE origintxid = 33851
DELETE FROM sgvault.dbo.tbltransactions WHERE txid = 33851

3) As a further precaution, I would recommend uninstalling the Vault server ( keeping the database ) and reinstalling the Vault server ( keeping the database ). This will recreate the Vault Server database stored procedures / triggers - just in case something there was corrupted.


If there is nothing in SQL Server's logs or Windows Event Viewer logs, is it possible I can get the database backup from step 1? I'd like to see if I can spot a missing trigger or stored procedure problem which would have caused this problem.

[CareBear]

Where do I find the Vault log?

There doesn't seem to be anything related in the Windows Event Log.

I will try to run these SQL operations and see what happens. If nothing improves, we're taking backups every hour so I can luckily restore an backup before this problem occured.

I'll get back to you with status later.


Regards,
Sondre