Every day now we have this procedure blocking all other processes on sgvault with the result that we can no longer login, check in or check out until SQL Server is restarted.
I did find another reference to this same problem in http://support.sourcegear.com/viewtopic.php?t=5997 and, when I try the proposed resolution of manually running the procedure on the repositories, I always get the block on one particular repository.
Any way to solve this problem as it is getting very frustrating having to down SQL Server all the time ?
Regards,
Brett
spdeletecheckoutlists blocking
Moderator: SourceGear
What version of SQL server are you using?
Had you looked into the part on the indexes as well? Defragmenting or rebuilding those is detailed out in our Database Maintenance Article.
Had you looked into the part on the indexes as well? Defragmenting or rebuilding those is detailed out in our Database Maintenance Article.
Hi Beth,
We are using SQL Server 2000.
I have run the Defrag Indexes script but that didn't help.
What might be causing the problem is the amount of data in one of the two tables. The lists table has 800 odd rows but the items table is approaching 5 million rows.
Tomorrow is a public holiday so I have stopped IIS on the Vault server machine and am manually running the procedure on the problem repository on the SQL Server machine. Hopefully this will resolve my problem.
Cheers,
Brett
We are using SQL Server 2000.
I have run the Defrag Indexes script but that didn't help.
What might be causing the problem is the amount of data in one of the two tables. The lists table has 800 odd rows but the items table is approaching 5 million rows.
Tomorrow is a public holiday so I have stopped IIS on the Vault server machine and am manually running the procedure on the problem repository on the SQL Server machine. Hopefully this will resolve my problem.
Cheers,
Brett
The tables I am referring to are tblcheckoutlists and tblcheckoutlistitems, as per the referenced post.
The database (in simple recovery mode) is about 2GB and the log file just under 2GB. I am not sure about growth as I haven't been monitoring it.
The manual execution of spdeletecheckoutlists on the one repository (out of 4) ran for 1.5 hours and tblcheckoutlists has dropped to 200 odd rows and tblcheckoutlistitems to 950,000 odd rows. Running against the other repositories finishes in seconds.
I have restarted IIS and we will see tomorrow (Thursday) when we are back at work if this has helped at all.
Cheers,
Brett
The database (in simple recovery mode) is about 2GB and the log file just under 2GB. I am not sure about growth as I haven't been monitoring it.
The manual execution of spdeletecheckoutlists on the one repository (out of 4) ran for 1.5 hours and tblcheckoutlists has dropped to 200 odd rows and tblcheckoutlistitems to 950,000 odd rows. Running against the other repositories finishes in seconds.
I have restarted IIS and we will see tomorrow (Thursday) when we are back at work if this has helped at all.
Cheers,
Brett
Update: Vault run perfectly on Thursday and Friday but today, Monday, the problem is back again.
Running sp_who2 revealed that spdeletecheckoutlists was being blocked by GHOST CLEANUP running against the sgvault database.
Some searching on the net found that the block might be occurring as GHOST CLEANUP runs against databases set as auto-shrink and this can be resource intensive, especially on large databases.
I have now turned off the auto-shrink option for the sgvault database and we'll see if this helps at all.
Cheers,
Brett
Running sp_who2 revealed that spdeletecheckoutlists was being blocked by GHOST CLEANUP running against the sgvault database.
Some searching on the net found that the block might be occurring as GHOST CLEANUP runs against databases set as auto-shrink and this can be resource intensive, especially on large databases.
I have now turned off the auto-shrink option for the sgvault database and we'll see if this helps at all.
Cheers,
Brett