June 16, 2008 at 4:10 am
We have a scheduling app which has a vb.net function to return the server datetime for use in the application. I have seen the source code and they are literally running "select getdate()" as the sql command. no connection settings or changes are being made etc. The only area I dont fully understand the impact of is the function that returns a sql connection. This will either open a new one using a sql conneciton string (i have checked this is fine) or 'recycle' an existing one.
This scheduling app logs rows to an audit history table (object 2089058478) and is causing severe locking on that table for many minutes. I have looked at the locks and include an example here. My question is how can such a simple t-sql connection cause table locks and also have a single open transaction?
11060 0DB SGRANT
110620890584783PAG1:450133 IXGRANT
11060 0PAG1:450168 IXGRANT
110620890584783KEY(db012878c3c6) XGRANT
110620890584780TAB IXGRANT
110620890584781KEY(af003f4b29a2) XGRANT
110620890584782PAG1:407205 IXGRANT
110620890584782KEY(af0258032360) XGRANT
June 16, 2008 at 12:54 pm
Can you try by running a profiler and tracing out what exactly is happening inside?
Manu
June 16, 2008 at 1:28 pm
Can you post the VB code please?
Is it possible that you have implicit transactions and are reusing a connection that has an ope transaction?
Second the suggestion of profiler. It will show you exactly what's happening to the server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2008 at 5:44 pm
ran profiler and i am not getting anything else of meaning on that spid. before or after. I guess it is how the connections are being reused.
gilamonster, the developers do change the transaction isolation levels and connection settings so that is possible in their sql :(. I just never realised that the connection could be reused with an open transcation on it. If that is the case then I can trace down other code in the calling app and see where they have failed to close a transaction. thx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply