how to find out if a TSQL table is being updated?
DR
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?
Mike C#
"DR" <softwareen...@yahoo.com> wrote in message
news:%23BVXiq7...@TK2MSFTNGP03.phx.gbl...
> how to find out if a TSQL table is being updated?
>
> for example if someone does an update to a table that takes a long time,
> is there anyway for me to check the status of that update process?
Depends on what type of "status" you want. Basically you have two states
(executing/not executing) and three reasons by default (executing/finished
with no error/stopped due to error).
I suspect you're talking about checking a mid-operation status during the
update (i.e., how many rows have been updated at any given point in time).
For that you would need to break the update into multiple steps, like a loop
or cursor (ugh). The tradeoff is usually performance, and the total effect
would probably depend on several factors including how many iterations of
the loop you need to do. Personally I wouldn't bother with it, but it's
really up to you to decide how important a mid-point status check is.
JXStern
I suppose you could check the locks, that's not directly a status
read.
But let's not overlook the obvious, just running sp_who2 periodically
you can track the reads and cpu time for the spid, or watch it in the
new performance viewer. Doesn't tell you the table, but maybe you
already know the table, and you might be able to get the sql from dbcc
getinputbuffer or something.
J.
Erland Sommarskog
NOLOCK in question may reveal something. That would require you to
have good understanding of the UPDATE statement and the data in the
table.
If what takes time is the time to locate the rows to update or compute
the values, there is not much you can find.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx