Cancel
Showing results for 
Search instead for 
Did you mean: 

Access to SQL database is locked during login

Valued Contributor
Valued Contributor

Hello,


Currently, we have an issue, that the login takes unusually long (1,5 minutes) and during the login process the SQL database is locked. Teamcenter is frozen for all users until the login process is completed.


I have uploaded the login log file.
You can see that it took 45.027 seconds to execute one SQL command.

 

Expect this login issues, our general performance is quite good.

Does someone have an idea what can cause this issue?

 

 

5 REPLIES

Re: Access to SQL database is locked during login

Solution Partner Phenom Solution Partner Phenom
Solution Partner Phenom
Someone has a lock on a resource related to login and hasn't released the transaction. Try using a combination of sp_lock2 and sp_who to find which session needs to be killed (or use SQL Activity Monitor?).

Randy Ellsworth, Teamcenter Architect, Applied CAx, LLC
NX 11 | SW 2016 | Creo 4 | TcUA 11.4
Evaluating: AW 3.4

Re: Access to SQL database is locked during login

Solution Partner Phenom Solution Partner Phenom
Solution Partner Phenom
I found this post on Google...

In the SQL Server Management Studio, to find out details of the active transaction, execute following command
DBCC opentran()

You will get the detail of the active transaction, then from the SPID of the active transaction, get the detail about the SPID using following commands
exec sp_who2 <SPID>
exec sp_lock <SPID>

For example, if SPID is 69 then execute the command as
exec sp_who2 69
exec sp_lock 69

Now , you can kill that process using the following command
KILL 69

I hope this helps

Randy Ellsworth, Teamcenter Architect, Applied CAx, LLC
NX 11 | SW 2016 | Creo 4 | TcUA 11.4
Evaluating: AW 3.4

Re: Access to SQL database is locked during login

Valued Contributor
Valued Contributor

Thank your for your fast reply.

 

It is always the new SPID of the user who wants to login which causes the locking.

 

The Status of following OBJID (POM_M_LOCK) is on WAIT during the whole login process.

On our test system which is working fine, this is not the case. 

 

spid

dbid

ObjId

IndId

Type

Resource

Mode

Status

55

7

1550732677

1

KEY

(bbf96d849e6a)                 

S

WAIT

Re: Access to SQL database is locked during login

Solution Partner Phenom Solution Partner Phenom
Solution Partner Phenom
There is definitely something wrong with the DB configuration then as the lock/unlock should be fractions of a second. Could be DB permissions for "infodba" or a missing DB attribute or a full TEMP tablespace or missing indexes, etc. You should turn on SQL debugging and call GTAC for assistance.

Randy Ellsworth, Teamcenter Architect, Applied CAx, LLC
NX 11 | SW 2016 | Creo 4 | TcUA 11.4
Evaluating: AW 3.4

Re: Access to SQL database is locked during login

Valued Contributor
Valued Contributor

We fixed it. Smiley Happy

 

On the PFORM table several indexes were required.

 

Now the login takes 10 seconds.