A few thoughts:
Run a trace on it to see what it is doing (from enterprise manager - sql profiler / new / trace / . . .) This may show what it is doing but if it is stuck on one specific sql which has already been fired, it may not and you will have to do a dbcc inputbuffer. This won't solve your problem but can give you a hint where it is.
Select * from master..sysprocesses
pay special attention to physical_io, cpu, memory, and blocked for the spid of the process which is taking so long. If it is blocked, find out by what and possibly kill it if that seems appropriate. Otherwise, wait a minute and then - in another window - select again for the spid in question. Compare the mentioned columns to determine if the process is doing anything.
I have had a few instances in older versions of sybase where the linked list of the data pages got messed up and the reference was circular. (This appeared to be related to a table with a non-unique clustered index which was inserted into in places other than the end.) So - if you did a table scan , page 1 points to page 2 points to page 3 points to page 4 points to page 2. The symptom is that CPU goes very high because the whole thing gets cached although it is not necessary for it all to be cached depending on table size and the number. To find out if this is the problem (and as a normal maintenance procedure) Run :
dbcc checkdb 'database name'
I hope this helps. Drop me a note if you need more detail.
. . . Tom
Tom Zeblisky
Reuters
"Sexton, Kevin"
<kevin.[Email Address Removed] To: [Email Address Removed]
cemed.com> cc:
Subject: disk IO problems
01/17/02 09:57 AM
Header: Internal Use Only
Facts: Assume- no system changes, no sql or index changes, only 1 user
on system
A process which normally takes 3 hours to run is on it's 12th hour. The
only clue I have is the process has unusually low disk I/O.
Any ideas?
Kevin J. Sexton
Sr. Database Administrator
AdvanceMed Corporation
11710 Plaza America Drive
Reston, VA 20190-6017
http://www.eadvancemed.com
---------------------------------------------------------------------
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed] Visit our Internet site at http://www.reuters.com
Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.
MS Sql Server LazyDBA home page