I'm currently in a situation where I'm struggling to come to terms
with a strange locking problem in MS SQL Server 6.5. I'm getting quite
desperate and know not what to do. I would appreciate some suggestions
if you could spare the time.
(1) I'm not sa for the server, I do have dbo rights in some of the
databases on the server.
(2) There is no available source code for the application that is
working against the database.
(3) The application is an internet application with several
The application has performed fairly well over the past year, until
one month ago, when a locking problem started to appear for no
apparent reason. No major changes to the application was rolled out.
After investing some time - using sp_who, sp_who2, sp_lock, sp_lock2
and DBCC INPUTBUFFER(<spid>) manually and by convincing the DBA to run
a script that takes a snapshot on all locks every minute I can say
that I have a somewhat incomplete picture of what is happening.
For no particular reason sleeping processes are holding exclusive page
locks on both user database tables and system tables in tempdb. Those
processes doesn't seem to consume any CPU, they are just holding their
locks for about 5-20 minutes and then mysteriously disappearing. The
user tables that the locks are held against together with the tempdb
system tables are almost always the same.
Since exclusive locks are held on tempdb system tables no one is able
to access the application during time the locks are held, to much
The following characteristics are true for these lingering locks:
(1) The exist for about 5 to 20 minutes.
(2) They appear to be sleeping.
(3) Nothing can be seen when the DBCC INPUTBUFFER command is issued.
<I have dbo right to the database in question>
I find number (3) particularly disturbing since there has to be some
underlying SQL-statments executed in order to create exclusive locks
on the tables in question. At least, that is what I think.
The makers of the application can't find anything wrong with the
transactions that are updating the user tables that are locked
together with the tempdb tables.
I have investigated the SELECT INTO and INSERT INTO EXECUTE options
for tempdb locking, but none of those statements are issued accoring
to the makers of the application. Since this is a known problem, the
makers use permanent temporary tables, i.e. temporary tables that
A question related to this - how does deadlocking appear in the
database? If I have understood deadlocking in 6.5 correctly, a
deadlock victim is selected almost immediately and the victim
terminated, allowing SQL Server to continue with the next task at
Could the victim of a deadlock still linger around or what can happen?
My next moves will be to have SQL Trace logg all the SQL sent to the
database, but IIRC this will slow down perfomance quite a bit.
Parallell to this I'll try to install some of the software in debug
mode, that will allow us to logg some of the outgoing SQL from the
Does anyone have any input on this issue?
Care to make any suggestions?
Could someone please pity me? :-)
I have read all the sql-server related groups as far back as my USENET
server stores messages (a month or so), the SQL-server FAQ,
www.swynk.com, Inside Microsoft SQL Server 7.0 (I know it's the wrong
version), BOL, sqlserver.superexpert.com, someones *large* archive of
SQL Server discussions, and I haven't found something that is
applicable to my particular problems.