Monitoring whether a query is still working or it's killed the server 1

[Zukkster]

I set off a query to update a table with a 40 million rows by referencing an equally big table. It's been running for a long time - over the weekend. I know if I stop the query it will take as long to undo as it took to run.

The transaction log hasn't grown too big and when I look at the task manager it's hardly registering any activity.

How can I tell if I've just killed the server or the query really is taking ages. I have a horrible feeling I've written a dumb query and asked it to do so much that it's still working but it's just taking a long time to page the data because it's having to deal with more than it can handle.

 

[Glowworm27]

use

dbcc opentran('databasename') to see if the transaction is still running if it is you will see some info about the transaction, the SPID, the time it started and a few other things.

FROM BOL

DBCC OPENTRAN
Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. Results are displayed only if there is an active transaction or if the database contains replication information. An informational message is displayed if there are no active transactions.

Syntax
DBCC OPENTRAN 
    (    { 'database_name' | database_id} )
        [ WITH TABLERESULTS 
            [ , NO_INFOMSGS ] 
        ] 

Arguments
'database_name'

Is the name of the database for which to display the oldest transaction information. Database names must conform to the rules for identifiers. For more information, see Using Identifiers. 

database_id

Is the database identification (ID) number for which to display the oldest transaction information. Obtain the database ID using the DB_ID function.

WITH TABLERESULTS

Specifies results in a tabular format that can be loaded into a table. Use this option to create a table of results that can be inserted into a table for comparisons. When this option is not specified, results are formatted for readability. 

NO_INFOMSGS

Suppresses all informational messages.

Remarks
If neither database_name nor database_id is specified, the default is the current database.

Use DBCC OPENTRAN to determine whether an open transaction exists within the log. When using the BACKUP LOG statement, only the inactive portion of the log can be truncated; an open transaction can cause the log to not truncate completely. In earlier versions of Microsoft® SQL Server™, either all users had to log off or the server had to be shut down and restarted to clear uncommitted transactions from the log. With DBCC OPENTRAN, an open transaction can be identified (by obtaining the system process ID from the sp_who output) and terminated, if necessary.

Result Sets
DBCC OPENTRAN returns this result set when there are no open transactions:

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

you can also use that to return the SPID and you can end a transaction using Kill({spid number})

this from BOL...

A. Use KILL to terminate a SPID
This example shows how to terminate SPID 53.

KILL 53

B. Use KILL spid WITH STATUSONLY to obtain a progress report.
This example generates a status of the rollback process for the specific spid.

KILL 54
KILL 54 WITH STATUSONLY

--This is the progress report.
spid 54: Transaction rollback in progress. Estimated rollback completion: 80% Estimated time left: 10 seconds.

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[SQLSister]

For future reference
How to Use Batch Size to Speed Mass Updates, Inserts and Deletes faq183-3141

Questions about posting. See faq183-874