<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Jobs deleted are still running in MS SQL Server

Published on
10,787 Points
4,787 Views
Last Modified:
Approved
Hi,

Recently I faced an issue, which I never faced with SQL Server 2005.

OS: Windows Server 2003 64 Bit
Database: SQL Server 2005 SP2 64 Bit
Server: Amazon EC2 (Elastic Cloud Computing)


Issue:

Last week I have done few changes on my SQL Server 2005 database server.

1. Deleted few Jobs and few jobs were rescheduled.
2. Maintenance Plans - Deleted few sub plans and few sub plans are rescheduled.

The next day when I checked, I found that jobs & sub plans that I have deleted are still running & the jobs that I rescheduled are also running with their old schedules as well as with their new schedules.

As a result, backups & other sub plans are running twice and the space in the Backup disk drive got reduced.

One good thing I have done is to configure alert service (database mail) for all the jobs. So that I get mails whenever the jobs failed and their schedule ids are included in the mails.


Solutions Tried:

I checked the SQL Server agent history and I found everything seems to be OK. I could not find any entry for the failed jobs i.e. no entry for deleted jobs.

I also checked in MSDB database for jobs data... there is no data in the database for the jobs that are deleted and the existing data is fine with their schedules.

Restarted the Services and even the Server too. However, nothing seemed to be helping.

Unfortunately no help when searched online.


Solution that worked:

The one action that helped me to resolve the issue was deleting all the temporary files from the drive in which MS SQL Server is installed.

Why I deleted the temporary files?

My company develops web-based applications in .Net & Java. Whenever we update or change in our applications, we delete the cache created for that application and refresh it to see the updates on applications.

So, this made me to think of deleting temporary files. And it worked.

Many a times I deleted jobs and rescheduled them, but I never faced any issue. I don't know the cause of this issue and I never expected the deleting temporary files will work.

But finally it worked.

Thanks for reading this and I will be very happy if this helps anyone.

Thanks in advance for your comments and suggestions.
0
  • 3
5 Comments
LVL 56

Expert Comment

by:Mark Wills
Hi JagdishDevaku,

It probably has something to do with prefetch, so your experience with caching sounds like it was exactly the right thing to do.

Handy tip by the way, will keep that in mind for the occasional "mystery" problem.
0

Expert Comment

by:Eric Christensen
I know this is an old post, but I am running into the exact same issue, and I cannot find anything else helpful online.

When you say "temporary files", can you elaborate? Did you just search for and delete anything "*.tmp"? I have already done that, but the issue is still occurring; even after a restart. There are no jobs whatsoever on the SQL server, and I verified with "SELECT * FROM sysjobs".

To elaborate, I had initially set up a maintenance plan that included log backups every minute. I ended up changing that to every 15 minutes, but it is still running every minute. I completely deleted the maintenance plan (and associated jobs went with it), but it is still running every minute. I am baffled...
0
LVL 14

Author Comment

by:Jagdish Devaku
Hi,

Finally after long time I found someone who is facing similar issue.

I just logged into the server and deleted temporary files. (Run -> %temp% -> Select all the files and deleted).

Please let me know if this worked.

Thanks.
1

Expert Comment

by:Eric Christensen
Unfortunately, that does not seem to have done the trick for me. I deleted everything in %TEMP%, and I even deleted every stored profile on the server that was not my own. They are still running.

To note, I even stopped the agent service, but still getting the backups.

The strange thing is, I ran SQL Server Profiler, and I can see which account it is running under, but why is it showing that it is being run from SQL Server Management Studio?

Jobs running
The backup files are still being created.

Files still being dropped
0

Featured Post

OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month