SQL blocking and SessionRelLock table

[Brody]

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]

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]

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