?
Solved

Job still executing.

Posted on 2006-10-28
6
Medium Priority
?
193 Views
Last Modified: 2008-03-03
Hi,
I've created a job for indexdefrag, the status now still executing for 2 days but when I check in QA sp_who2 active, the job has status as sleeping not runnable why??
0
Comment
Question by:motioneye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17827761
Hi motioneye,


What does this return

SELECT * FROM  msdb..sysjobhistory

Also check whether tempdb has enough space to grow.



Cheers!
0
 

Author Comment

by:motioneye
ID: 17827792
Hi,
Its ok the job still executing, I'm checking from the log in the job.
0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 17829262
Is there a query window open somewhere that is executing a query on the table you are indexing? That would lock the table and prevent the index command.

Check for locks on the object you are trying to index.
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 750 total points
ID: 17833369
(What is your sql server version\edition\SP? on what OS...? HDD- RAID?)

------------------------------------------------------------------------
if you run
sp_who2 'active'
and see - 'Sleeping':

Typically, a sleeping status indicates that the SPID has completed execution and is waiting for the application to submit another query or batch.

----
Looks like some another process is running and do not let indexdefrag finish
also - maybe you have very big database

If you wish fast index refresh - try DBCC DBReindex instead of 'indexdefrag'
in case if you have maint window (tables will be blocked)..

more:
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/kb/224453 
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 17838362
Solutions have been posted-

AaronAbend posted first about locks as source of the problems
 - so please give him points
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question