Solved

Job still executing.

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Select - Help with CASE 4 42
Solution for warm standby SQL server 20 34
Using Aggregate Functions to Count 3 32
TSQL previous 5 23
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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

919 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now