August 22, 2007 at 7:50 am
Has anyone had it happen before that SQL Server 2000 jobs just stop working. It shows an execution status but it doesnt do anything.
Any ideas?
August 22, 2007 at 11:35 am
For me it happen with jobs connecting to Oracle and the server had to be stopped and restarted. Believe that turned out to be a driver issue.
What are your jobs doing?
August 22, 2007 at 11:37 am
I had this kind of experience. I had to restart the SQLAgent.
August 22, 2007 at 12:25 pm
SA,
Did all the jobs stop working or only some of them? If only some, what do they have in common?
My experience with jobs that show they're executing but never finish have been jobs that run DTS packages that use ODBC to read or write to another server. When ODBC dropped, a package task would hang causing the job to hang.
Greg
Greg
August 24, 2007 at 7:08 am
There are just 4 jobs that fail. The common point between them is they import data from text files.
I have tried stopping starting the agent / sql services. Tried to reset the account on the agent.
I read somewhere it could be an issue with MDAC. But that is a long shot. Any other suggestions.
These packages run when executed manually.
August 24, 2007 at 7:26 am
Are these imported using a DTS Package? Do you do a straight copy or do you manipulate with any scripting?
September 4, 2007 at 4:55 pm
There is no manipulation. It is a straight import.
September 5, 2007 at 8:23 am
I hate to have to suggest this, but a server re-boot might be in order.
I have had backup jobs decide to fail because they couldn't get enough memory allocated, and the results were similar to what you have seen.
If you can, try a server re-boot and see if they "magically" work again.
Brian
September 5, 2007 at 8:25 am
Has anyone experienced corrupted DTS packages?
We ran some file mon tools and it seems like the job can read the file but not import the data. Does file import using DTS transformations use Bulk Insert or BCP?
The job works fine sometimes when the server is rebooted but then just does nothing during other times.
sp_who2 shows it is awaiting command and in sleeping mode.
September 5, 2007 at 8:44 am
I haven't had any corrupted packages, but I've read about packages becoming unusable when transferred to another instance having to be recreated.
There is a Bulk Insert task in DTS and you can enable Bulk Copy in a Transform Data task. BCP is a separate utility and isn't used in DTS.
Greg
Greg
September 5, 2007 at 9:44 am
Is it possible it is a permission problem? I have had cases in the past where the security group changed the permissions of the account I was running the agent on and all of a sudden I couldn't reach the files I was pointing to. It ran fine when I ran it because it was using my permissions. It was when it ran through the schedule that it failed.
Ken
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
September 5, 2007 at 9:52 am
i have verified the permissions on the share (both share and ntfs permissions) and it looks ok. Moreover, the agent service account is local admin on the box.
September 5, 2007 at 12:26 pm
This maybe a long shot, but I had corruption in msdb database on sql 2005, maybe running dbcc checkdb on it would be one more thing to check.
-Chuck Lathrope
September 5, 2007 at 12:36 pm
I tried this as well and everything turned out fine...
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply