Solved

Job still executing.

Posted on 2006-10-28
6
181 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
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 42

Accepted Solution

by:
EugeneZ earned 250 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 42

Expert Comment

by:EugeneZ
ID: 17838362
Solutions have been posted-

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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

837 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