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)
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.
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.