TeamCity migration to SQL Server causing hangs

I successfully migrated our TeamCity records to SQL Server 2005 SP3 (running on Windows Server 2008), following the instructions in the documentation. TC is connecting to SQL Server thru JTDS, it created the version.dat and dbProperties file and the migration seemed to work ok. However, now when I try running a build, the build freezes while publishing artifacts. I ran a trace on SQL Server and saw that there was a cascading stored proc issue happening, and more or less was running in an infinite loop. The lines I saw that kept coming up were IF TRANCOUNT < 0 ROLLBACK.

Any ideas what could be causing this?

7 comments
Comment actions Permalink

Apparently setting poolPreparedStatements=false in the database.properties file fixes the problem.

0
Comment actions Permalink

Please submit a bug report if it happens again. It's quite strange that disabling of the prepared statements pool solved your problem.

0
Comment actions Permalink

After double checking my changes, it was not poolPreparedStatements that fixed the problem. It was changing the collation on my TeamCity DB. It was set to Latin_General_CI_AI, but apparently setting it to Latin_1253_CI_AI fixed it. Maybe because that's what was selected as the original collation when installing (I think I've read about an issue with SQL Server where it doesn't like you using different collations somewhere).

0
Comment actions Permalink

Any updates on this issue?

We just installed 5.0.1 on TeamCity and have SQL Server 2005 SP3 for our database. We had within two days a blocking issue on each day where the main webpage becomes unresponsive for hours on end until we restart TeamCity or kill the database spid that is causing all the block. I had our DBA group look into it and they said the statement below is the source of the blocking.  Our collation is set to SQL_Latin1_General_CP1_CI_AS.  We were on 4.x for 2+ years running Postgres 8.3 as the back end and never had this problem. Our quick fix for the time being is to kill the blocking spid so TeamCity is usable, but this is not a long term fix by any means.


(@P0 varchar(255),@P1 bigint)

UPDATE build_state SET MODIFICATION_ID =  @P0  WHERE ID =  @P1

Bob



UPDATE#1: I see the docs call for CI_AI as the previous poster said they switched too. I will try that and see what happens though I don't see how that would cause the above statement to block.

UPDATE#2:Made this change in the morning and by lunch have seen the blocking occur again. When doing sp_who2 on the database I found a few connections causing issues. Below are the statements from each of the blocking connections.  I had to manually kill each one to get the TeamCity server to be reponsive.  Can a JetBrains rep recommend if setting our SQL Server database to snapshot isolation would help avoid the issue?


(@P0 varchar(255),@P1 bigint)
UPDATE build_state
SET MODIFICATION_ID =  @P0  
WHERE ID =  @P1


(@P0 nvarchar(4000),@P1 int)
SELECT MAX(bs.MODIFICATION_ID)
FROM build_state bs
WHERE bs.BUILD_TYPE_ID =  @P0  
AND       (1 =  @P1  OR (bs.IS_CANCELED = 0 AND bs.BUILD_ID IS NOT NULL))
AND       bs.IS_PERSONAL = 0


(@P0 varchar(255),@P1 bigint)
UPDATE build_state SET MODIFICATION_ID =  @P0  
WHERE ID =  @P1


(@P0 varchar(255),@P1 bigint)
UPDATE build_state SET MODIFICATION_ID =  @P0  
WHERE ID =  @P1

Message was edited by: BobMN

0
Comment actions Permalink

Is there an estimated date of when this fix will be released?

Thanks
Bob

0
Comment actions Permalink

The fix should be included in 5.0.2. In fact this is the workaround, because we think the problem is in MSSQL server rather than in TeamCity. If there is a deadlock in MSSQL, db engine should report it, not hang.

If this problem reproduces in your case please try to install this build:
ftp://ftp.intellij.net/pub/.teamcity/TW-10169/TeamCity-10774.war

This build is very close to 5.0.2 and should be quite stable.

0

Please sign in to leave a comment.