SQL blocking and SessionRelLock table

If you are having a problem using Vault, post a message here.

Moderator: SourceGear

Post Reply
Brody
Posts: 19
Joined: Sun Feb 22, 2004 6:14 pm
Location: Auckland, New Zealand

SQL blocking and SessionRelLock table

Post by Brody » Sun Feb 22, 2004 6:24 pm

We have an issue with the SessionRelLocks table in version 1.2.3.

It currently has over 3.7 million rows and appears to cause Vault to Block itself on the SQL server.

The session table has 913 rows.

We have been having some performance issues periodically - mainly during the checkout and checkin phase of our daily build - But today the system is just locked up.

Because we have to get this working we have developed a plan to truncate the SessionRelLock table and delete all records in the Session table.

We have shut down the Vault Service and taken a backup. What effect will purging these two tables have?

Regards

Brody

dan
Posts: 2448
Joined: Wed Dec 17, 2003 5:03 pm
Location: SourceGear
Contact:

Post by dan » Sun Feb 22, 2004 8:27 pm

Jeff will have to give you the lowdown on what happens when you clear those tables - I seem to remember it not being a problem.

But, I can verify that the session table does get cleared out properly in 2.0, and that a 2.0 upgrade clears out those tables. This was a bug that showed up when using the IDE client heavily - sessions were not getting cleared out properly, due to the APIs from the IDE not deterministically telling Vault when the IDE is really being exited.

jclausius
Posts: 3706
Joined: Tue Dec 16, 2003 1:17 pm
Location: SourceGear
Contact:

Post by jclausius » Mon Feb 23, 2004 9:09 am

brody:

we've identified and fixed a problem with vault 1.2.x related to these tables. i would recommend upgrading to vault 2.0.0 asap to avoid any additional hassles.

in the event you cannot upgrade to 2.0.0 just yet, here is an excerpt of the vault 1.2.x to vault 2.0.0 upgrade script. you can run it as many times as you like to "clean" any abandoned sessions.


-- remove fk referencing tblsessions
ALTER TABLE [dbo].[tblsessionrellocks] DROP CONSTRAINT [fk_tblsessionrellocks_tblsessions]
GO

-- truncate session tables
TRUNCATE TABLE [dbo].[tblsessionrellocks]
TRUNCATE TABLE [dbo].[tblsessions]

-- reestablish foreign key
ALTER TABLE [dbo].[tblsessionrellocks] ADD CONSTRAINT [fk_tblsessionrellocks_tblsessions] FOREIGN KEY ( [sessionid] ) REFERENCES [dbo].[tblsessions] ( [sessionid] ) ON DELETE CASCADE

hth
Jeff Clausius
SourceGear

Post Reply