• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

Job still executing.

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??
1 Solution
Aneesh RetnakaranDatabase AdministratorCommented:
Hi motioneye,

What does this return

SELECT * FROM  msdb..sysjobhistory

Also check whether tempdb has enough space to grow.

motioneyeAuthor Commented:
Its ok the job still executing, I'm checking from the log in the job.
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.
Eugene ZCommented:
(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)..

INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
Eugene ZCommented:
Solutions have been posted-

AaronAbend posted first about locks as source of the problems
 - so please give him points
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now