Blocking sproc spdeletecheckoutlists

[stefvanhooijdonk]

For two days in a row we got a blocking spdeletecheckoutlists at around 16h30.

It blocks access to sgvault on our the SQL 2005 server.
Our situation:
- SQL 2005
- sgvault ( in SQL CompatiblityMode 90 )
- Vault 3.1.8
- 6 repositories
- 130.000 files
- 13.000 folders

I changed the compatibity mode back to 80 since that's where we came from ( a sql2000 server )

[jclausius]

- Are you saying it is blocking *at* 16:30 or it is blocking *for* 16:30?
- How many users are accessing your Vault Server?
- Do your users checkout folders rather than the files they will be editing?
- Is SQL Server and the Vault Server on the same machine?
- How much main memory is present for the Vault Server? SQL Server?
- What is your configuration for disk I/O subsystems?

-----------------------
If you have a situation where people are checking out hundreds or thousands of files, the Vault Server has to maintain new lists. However, since the list only has a "current" meaning, the Server will periodically clean up old checkout lists.

Over time, if there is a large number of changes to checkout lists or large number of files checked out this clean up can be rather slow. If you look at Vault Server's vault.config file, you will see an XML element for "CheckoutListDaysToExpire". You may want to lower the value to 2 or 3. This will cause the clean up to occur more frequently, taking less time. Be sure the Vault Server after the change (iisreset.exe).

In any case, you may have to get through that first round of deletions before things improve. If you are still having problems after that, please contatct us. I can provide a SQL Query that removes all but the current checkout list.

[stefvanhooijdonk]

It blocking AT 16:30. no real information here I guess.
People checkout files. Hardly any folders of files.
we have around 40 users
SQL and vault run on a Dual Xeon 2Gb machine ( with HyperThreading makes 4 ) And the vault db is on a mirror scsi raid.
This is a decent server

I changed the CheckoutListDaysToExpire to "2".
Are there any other possible reasons for this blocking ?
- SQL2000/SQL2005 migration?
- large amount of files?
- corruption (of indexes)?
- bug in vault?
- amount of memory available?

[jclausius]

If it is a consistent 16:30, then I would suspect some SQL Server job is starting up, causing the stored procedure to block.

If you run sp_who2 (or use a tool to provide you with SQL process information), can you see the process causing the block?

You are correct in that it could be related to index fragmentation. You could rebuild the indicies of the entire sgvault database, and see if that solves the problem.


One note, on mirroring SCSI RAID disks. Mirroring is great for read performance, but it will slow things down when writing. In cases like this, I recommend configuring placing the tempdb on a RAID 0 or RAID 5 set and the sgvault database on a RAID 5 set.

[stefvanhooijdonk]

I had a look in the tables
tblcheckoutlists rowcount 25040
tblcheckoutlistitems rowcount 540478

Could it be that this is causing the dreaded blocking?

Can i remove records from these tables? Cause i saw a lot of checkout lists that were created 9 days ago.


Please? i have 10 developers who cannot continue working now. Our disk arrays are realy fast enough.

[stefvanhooijdonk]

USE [sgvault]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[spdeletecheckoutlists]
@repid = 4,
@daysold = 3

SELECT 'Return Value' = @return_value

for ( repid 2-9 ) next repid

seems ok now.

Question remains, why this sproc would (B)LOCK access to the DATABASE.

[jclausius]

25,000 different lists could be an explanation.

Let's try to clean things out:

1) Shut down the Vault Sever (Start -> Run -> iisreset.exe /stop)

2) Within a Query Tool, run

Code: Select all

SELECT repid, name FROM sgvault.dbo.tblrepositories

3) Find the repository in question, and run

Code: Select all

EXEC sgvault.dbo.spdeletecheckoutlists X, 1

where X is the repid from step 2.

4) Restart the Vault Server (Start -> Run -> iisreset.exe)

Since you say it is blocking, do this when no-one is using the database or can work offline while the SQL code does its work.

[jclausius]

Can you look at the table again? What are the row counts now?

Question remains, why this sproc would (B)LOCK access to the DATABASE.

I honestly don't know. Off the top of my head, it could be a couple of things:

- A scheduled job within SQL Server (like database backup)
- A scheduled job on the OS (disk defrag)
- A badly fragmented disk (the log file will need to grow)
- A badly fragmented index (index leaves will need to be modified)


If you encounter this again, running sp_who2 during the blocked process would tell you if the blocking was caused by something within SQL Server.