MS SQL Server - tempdb is locked

Author

tempdb is locked

airagos

2006-03-23, 8:24 pm

Here is the background:
- A database user define with readonly access.
- Six views to be used by that user
- The views reference objects in a different database and the user has
readonly access to those tables.
- The user does not have access directly to tempdb but they can create
temporary objects.

The application using these objects is connecting through JDBC. I do not
have access to their code but they assure they are not using any locking.

The problem:
Some times when the appications runs a process, the whole tempdb is locked
and any process trying to use the tempdb get locks.

Could you please give me ideas as per how to debug this problem? This is a
database that have realtime transactions so I am not able to bring it down
easily.

Thanks in advance for your help

Alejandro Mesa

2006-03-23, 8:24 pm

See if this helps.

INF: Canceled Transaction in a Temp Table Causes X and U Locks
http://support.microsoft.com/default.aspx/kb/159747

AMB

"airagos" wrote:

> Here is the background:
> - A database user define with readonly access.
> - Six views to be used by that user
> - The views reference objects in a different database and the user has
> readonly access to those tables.
> - The user does not have access directly to tempdb but they can create
> temporary objects.
>
> The application using these objects is connecting through JDBC. I do not
> have access to their code but they assure they are not using any locking.
>
> The problem:
> Some times when the appications runs a process, the whole tempdb is locked
> and any process trying to use the tempdb get locks.
>
> Could you please give me ideas as per how to debug this problem? This is a
> database that have realtime transactions so I am not able to bring it down
> easily.
>
> Thanks in advance for your help
>

airagos

2006-03-26, 8:23 pm

OK, That seems to be the behavior. However we are not using stored
procedures neither creating (explicitly) temporary tables and the code does
not cancel the query, it seems that it times out for some reason and the
connection is killed.

I said explicitly because -I am not 100% sure- it seems to me that the way
SQL Server resolves the query is using tempdb (it goes through hughes tables
that are being union). If that is really the case is there any way to
handle/change that behavior?

Any other suggestions?

Thanks for your help...

"Alejandro Mesa" wrote:
[color=darkred]
> See if this helps.
>
> INF: Canceled Transaction in a Temp Table Causes X and U Locks
> http://support.microsoft.com/default.aspx/kb/159747
>
>
> AMB
>
> "airagos" wrote:
>

Sponsored Links