What query is locking a table?

9 May 2005 5:22 PM

phavel

Hello. I have a table in a SQL 2000 database that is being locked for
several seconds at a time by a query that is doing a 'select *...'. This is
causing other applications to timeout and hang. I was not around for the
design of the ASP pages that access this database, and I am not sure how to
track down the page or app that is calling this query. In the ODBC conection
on our web server I have enabled logging of long running queries, which is
how I determined the cause of the locked database. I now need to track down
who's making the call. Thanks for any assistance on this.

One other point of note. This problem did not appear until the database
server was upgraded to SQL 2000 and Windows 2003 respectively. The previous
config of the database server was SQL 7 and Windows 2000. Is there anything
in these newer versions that would handle things differently? Thanks!

9 May 2005 9:27 PM

Andrew J. Kelly

sp_who2 and dbcc inputbuffer should get you there but see if these help:

http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking

--
Andrew J. Kelly SQL MVP

Show quoteHide quote

"phavel" <pha***@discussions.microsoft.com> wrote in message
news:D059BAA7-98E6-4B8F-9A68-4A992B49DAFB@microsoft.com...
> Hello. I have a table in a SQL 2000 database that is being locked for
> several seconds at a time by a query that is doing a 'select *...'. This
> is
> causing other applications to timeout and hang. I was not around for the
> design of the ASP pages that access this database, and I am not sure how
> to
> track down the page or app that is calling this query. In the ODBC
> conection
> on our web server I have enabled logging of long running queries, which is
> how I determined the cause of the locked database. I now need to track
> down
> who's making the call. Thanks for any assistance on this.
>
> One other point of note. This problem did not appear until the database
> server was upgraded to SQL 2000 and Windows 2003 respectively. The
> previous
> config of the database server was SQL 7 and Windows 2000. Is there
> anything
> in these newer versions that would handle things differently? Thanks!
>

9 May 2005 10:20 PM

phavel

Thanks a ton for these resources, they should get me the info I need. Much
appreciated.

Show quoteHide quote

"Andrew J. Kelly" wrote:

> sp_who2 and dbcc inputbuffer should get you there but see if these help:
>
> http://www.support.microsoft.com/?id=224453 Blocking Problems
> http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> Blocking
>
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "phavel" <pha***@discussions.microsoft.com> wrote in message
> news:D059BAA7-98E6-4B8F-9A68-4A992B49DAFB@microsoft.com...
> > Hello. I have a table in a SQL 2000 database that is being locked for
> > several seconds at a time by a query that is doing a 'select *...'. This
> > is
> > causing other applications to timeout and hang. I was not around for the
> > design of the ASP pages that access this database, and I am not sure how
> > to
> > track down the page or app that is calling this query. In the ODBC
> > conection
> > on our web server I have enabled logging of long running queries, which is
> > how I determined the cause of the locked database. I now need to track
> > down
> > who's making the call. Thanks for any assistance on this.
> >
> > One other point of note. This problem did not appear until the database
> > server was upgraded to SQL 2000 and Windows 2003 respectively. The
> > previous
> > config of the database server was SQL 7 and Windows 2000. Is there
> > anything
> > in these newer versions that would handle things differently? Thanks!
> >
>
>
>