Solved

Job still executing.

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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

688 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