Urgent: SQL Server PID Blocking
Moderator: SourceGear
Urgent: SQL Server PID Blocking
So this started some time over the weekend... People came in this morning and all of a sudden the Vault server can't retrieve the repository structure from the server.
Anyway, I've basically narrowed it down:
I clean the server (restart SQL, restart IIS, etc) and connect and login and get a normal set of entries in the log file (GetUserOptions, GetuserList, "Getting repository Structure", etc).
Then I attempt to check out and get a successful log message:
----7/11/2005 10:18:16 AM asills--s03cms01.globeranger.com.pvt(127.0.0.1)--SSL Disabled Checking out Files
----7/11/2005 10:18:16 AM asills--s03cms01.globeranger.com.pvt(127.0.0.1)--SSL Disabled CheckOutFiles returned:Success
Then naturally it attempts to refresh its view and I get this message:
----7/11/2005 10:18:16 AM asills--s03cms01.globeranger.com.pvt(127.0.0.1)--SSL Disabled Getting list of checkout changes.
Then the entire server stops responding. Going through the active process IDs in SQL I have on PID blocking another PID. The blocking PID's last command was "dbo.spdeletecheckoutlists;1" and it has tblcheckoutlistitems, tblcheckoutlists, tblcheckoutlocks, tempdb all actively locked. The blocked PID's last command was "dbo.spgetlockedfilechanges;1".
The blocked PID is actively in a SLEEPING state as is the blocking PID, which most definitely would be a deadlock (the blocked PID is doing an INSERT and the blocking PID is doing a delete).
Going through SQL Profiler I really don't see anything spectacular. I see the logon, I see the checkout, I see the repository refresh then it stops.
Vault is effectively useless at the moment on our server, do you have any suggestions???
Anyway, I've basically narrowed it down:
I clean the server (restart SQL, restart IIS, etc) and connect and login and get a normal set of entries in the log file (GetUserOptions, GetuserList, "Getting repository Structure", etc).
Then I attempt to check out and get a successful log message:
----7/11/2005 10:18:16 AM asills--s03cms01.globeranger.com.pvt(127.0.0.1)--SSL Disabled Checking out Files
----7/11/2005 10:18:16 AM asills--s03cms01.globeranger.com.pvt(127.0.0.1)--SSL Disabled CheckOutFiles returned:Success
Then naturally it attempts to refresh its view and I get this message:
----7/11/2005 10:18:16 AM asills--s03cms01.globeranger.com.pvt(127.0.0.1)--SSL Disabled Getting list of checkout changes.
Then the entire server stops responding. Going through the active process IDs in SQL I have on PID blocking another PID. The blocking PID's last command was "dbo.spdeletecheckoutlists;1" and it has tblcheckoutlistitems, tblcheckoutlists, tblcheckoutlocks, tempdb all actively locked. The blocked PID's last command was "dbo.spgetlockedfilechanges;1".
The blocked PID is actively in a SLEEPING state as is the blocking PID, which most definitely would be a deadlock (the blocked PID is doing an INSERT and the blocking PID is doing a delete).
Going through SQL Profiler I really don't see anything spectacular. I see the logon, I see the checkout, I see the repository refresh then it stops.
Vault is effectively useless at the moment on our server, do you have any suggestions???
- Attachments
-
- sqlprofiler.xls
- (29 KiB) Downloaded 802 times
You may want to check your statistics / indices for tbltreerevisionfolders and tblcheckoutlistitems. Blocking does not equal deadlock, but it will prevent users from selecting on a table which may be locked. After updating statistics or defragging or rebuilding the indices, you should try again.
I would also recommend upgrading to Vault 3.0.7 or Vault 3.1 Beta 3 server. There is a race condition in previous versions of Vault server which can cause problems.
As for deadlocks altogether, Vault 3.1 Beta 3 would be your best bet. We've gone through some extensive testing to remove deadlocks from this version of the server.
I would also recommend upgrading to Vault 3.0.7 or Vault 3.1 Beta 3 server. There is a race condition in previous versions of Vault server which can cause problems.
As for deadlocks altogether, Vault 3.1 Beta 3 would be your best bet. We've gone through some extensive testing to remove deadlocks from this version of the server.
Jeff Clausius
SourceGear
SourceGear
The server has a nightly maintenance schedule that updates the vault database. The last log file I received for it (last night at 1am) reported everything in tip top shape.
So you suggest a beta version? What will that do to the database? What if the beta version is worse? Can I revert back to an earlier version? Will every client have to be updated to the beta version as well?
So you suggest a beta version? What will that do to the database? What if the beta version is worse? Can I revert back to an earlier version? Will every client have to be updated to the beta version as well?
Do you know what command was run?asills wrote:The server has a nightly maintenance schedule that updates the vault database. The last log file I received for it (last night at 1am) reported everything in tip top shape.
Yes. We've been using it for months.asills wrote:So you suggest a beta version?
As with every DB schema change, it is a one way street. There is no way to get back. As for being "worse" than Vault 3.0.7 , its not possible. Vault 3.1 Server started with the Vault 3.0.7 code base, and had deadlock / concurrency / memory issues addressed.asills wrote:What will that do to the database? What if the beta version is worse? Can I revert back to an earlier version?
However, if you're not comfortable with Vault 3.1, then at least get up to Vault 3.0.7 so you do not encounter the race condition problem. However, as I mentioned, we are supporting Vault 3.1 Beta 3, so people should not be discouraged from its use. The biggest thing to be aware of right now is the schema change. On repositories with large folder counts, upgrading may take some time. Most people I've talked to, ran a test upgrade first.
No.asills wrote:Will every client have to be updated to the beta version as well?
Jeff Clausius
SourceGear
SourceGear
Yes, it's your suggested script for defragging, reindexing and updating statistics in your KB. DBCC REINDEX, UPDATE STATISTICS, DBCC INDEXDEFRAG.jclausius wrote:Do you know what command was run?
Okay, 3.0.7 didn't help at all. If nothing else, I can upgrade to the beta and if it doesn't help, I can simply restore last night's full backup since nobody's been able to do anything on it all day today anyway.jclausius wrote:However, if you're not comfortable with Vault 3.1, then at least get up to Vault 3.0.7 so you do not encounter the race condition problem. However, as I mentioned, we are supporting Vault 3.1 Beta 3, so people should not be discouraged from its use. The biggest thing to be aware of right now is the schema change. On repositories with large folder counts, upgrading may take some time. Most people I've talked to, ran a test upgrade first.
I'd like to suggest running some maintenance commands by hand.
Let's see what this does:
UPDATE STATISTICS sgvault.dbo.tblcheckoutlistitems
UPDATE STATISTICS sgvault.dbo.tbltreerevisionfolders
Next:
SELECT cli.* FROM sgvault.dbo.tblcheckoutlistitems cli
INNER JOIN sgvault.dbo.tblrepositories r ON r.currentcolistid = cli.colistid
ORDER BY r.repid
Does this second query finish?
Let's see what this does:
UPDATE STATISTICS sgvault.dbo.tblcheckoutlistitems
UPDATE STATISTICS sgvault.dbo.tbltreerevisionfolders
Next:
SELECT cli.* FROM sgvault.dbo.tblcheckoutlistitems cli
INNER JOIN sgvault.dbo.tblrepositories r ON r.currentcolistid = cli.colistid
ORDER BY r.repid
Does this second query finish?
Jeff Clausius
SourceGear
SourceGear
OK.
Run this query:
SELECT repid, name FROM sgvault.dbo.tblrepositories,
Now, using the repid from above (I used 2) in my sample:
Does this return?
What about this query:
EXEC sgvault.dbo.spdeletecheckoutlists @repid = 2, @daysold = 10
Run this query:
SELECT repid, name FROM sgvault.dbo.tblrepositories,
Now, using the repid from above (I used 2) in my sample:
Code: Select all
DECLARE @repid [int], @colistid [bigint];
SET @repid = 2;
SELECT @colistid = currentcolistid FROM sgvault.dbo.tblrepositories WHERE repid = @repid;
exec sgvault.dbo.spgetlockedfilechanges @repid, @basecolistid = 32492, @targetcolistid = @colistid, @refreshlist = 1
What about this query:
EXEC sgvault.dbo.spdeletecheckoutlists @repid = 2, @daysold = 10
Jeff Clausius
SourceGear
SourceGear
Yes, it took 28 seconds (repid 2 is my main repository we're having problems with).jclausius wrote:OK.
Run this query:
SELECT repid, name FROM sgvault.dbo.tblrepositories,
Now, using the repid from above (I used 2) in my sample:
Does this return?Code: Select all
DECLARE @repid [int], @colistid [bigint]; SET @repid = 2; SELECT @colistid = currentcolistid FROM sgvault.dbo.tblrepositories WHERE repid = @repid; exec sgvault.dbo.spgetlockedfilechanges @repid, @basecolistid = 32492, @targetcolistid = @colistid, @refreshlist = 1
Finished <1 second.jclausius wrote: What about this query:
EXEC sgvault.dbo.spdeletecheckoutlists @repid = 2, @daysold = 10
I re-ran the above spgetlockedfilechanges query after the spdeletecheckoutlists query and it only took 2 seconds this time.