SQL 2000 jobs running for days

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Microsoft SQL Server Performance Monitoring and Tuning Using traces in SQL Server Profiler SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Data restoration and DB property management Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Troubleshooting Distributed Transaction Coordinator errors in SQL Server Clearing the Windows page file and its effect on server performance Optimizing SQL Server indexes –- even when they're not your indexes Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works Archive: Performance Tuning Upgrading to SQL Server 2008 advantages and hardware requirements Tool to synchronize two SQL Server databases Performance tuning for SQL Server 2005 and Exchange running on SBS SQL Server tools don't appear in menu after SQL Server 2005 install Use SQL Profiler to find long running stored procedures and commands Maintain large SQL Server database and resolve website 'Timeout Error' How to retrieve SQL Server database disk space in use Troubleshoot SQL Server 2005 temporary table performance problems View query execution plan in SQL Server with this method Adding non-clustered indexes to SQL Server columns

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary contiguity  (SearchSQLServer.com) contiguous  (SearchSQLServer.com) drilldown  (SearchSQLServer.com) hashing  (SearchSQLServer.com) hybrid online analytical processing  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

VIEW MEMBER FEEDBACK TO THIS ASK THE EXPERT Q&A.

In Enterprise Manager you can right click on the Job and select Stop Job. Here is a pertinent screen shot:

From a T-SQL perspective the command would be sp_stop_job. Another option to troubleshoot the issue would be to consider a third party product by the name of SQL Sentry. I have used the product, recommend it and think it could help troubleshooting the issue.

I have tried your standard solution, but the job is still running with the status of "Performing completion actions." What's next?
—David C.

******************************************

Thank you for your question. If the job will not stop, you have three other options that are much more intrusive. These are:

1. Issue sp_who2 in Query Analyzer to determine which process (spid) is running the job. Issue a KILL command for the particular spid. Reissue sp_who2 to see if the process is still running.

2. When no other SQL Server jobs are running or scheduled to run, stop SQL Server Agent. This should stop the process internal to SQL Server. If the process is running in Windows, login to the SQL Server, start Task Manager, stop the Windows process such as a 32 or 16 bit application or a DOS session.

3. During a maintenance window, stop the SQL Server service. This will definitely stop the process.

I hope this helps!
— Jeremy Kadlec, Performance and tuning expert