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

Job still executing.

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


What does this return

SELECT * FROM  msdb..sysjobhistory

Also check whether tempdb has enough space to grow.



Cheers!
0
 
motioneyeAuthor Commented:
Hi,
Its ok the job still executing, I'm checking from the log in the job.
0
 
AaronAbendCommented:
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
 
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)..

more:
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/kb/224453 
0
 
Eugene ZCommented:
Solutions have been posted-

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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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