Hi,
Last week I faced a different issue, which I never faced before 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 did the following operations on SQL Server 2005.
1. Deleted some Jobs and rescheduled some jobs.
2. Deleted sub plans and rescheduled some sub plans under Maintenance Plans.
The next day when I checked, I found that jobs & sub plans that had been deleted are still running and rescheduled jobs & sub plans are also running with their old schedules and new schedules.
As a result, backups & other sub plans are running twice and the space in the Backup disk drive got reduced.
One of the best things I have done was to configure alert service (database mail) for all the jobs. Therefore, 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 remaining data is fine with their schedules.
Restarted the Services and even the Server too. However, nothing seemed to be helping.
No real information available by searching on the internet regarding this issue.
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 new things on 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.
Deleting of jobs or rescheduling of jobs I did many a times and (normally) worked every time. This is the first time I faced this issue with this server.
Thank God I finally solved the issue. If you find anything like this type of issue, then I can suggest trying to clean up those temporary files.
If someone can give better solution on the above issue are also welcome and thanks to him or her in advance.
Last week I faced a different issue, which I never faced before 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 did the following operations on SQL Server 2005.
1. Deleted some Jobs and rescheduled some jobs.
2. Deleted sub plans and rescheduled some sub plans under Maintenance Plans.
The next day when I checked, I found that jobs & sub plans that had been deleted are still running and rescheduled jobs & sub plans are also running with their old schedules and new schedules.
As a result, backups & other sub plans are running twice and the space in the Backup disk drive got reduced.
One of the best things I have done was to configure alert service (database mail) for all the jobs. Therefore, 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 remaining data is fine with their schedules.
Restarted the Services and even the Server too. However, nothing seemed to be helping.
No real information available by searching on the internet regarding this issue.
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 new things on 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.
Deleting of jobs or rescheduling of jobs I did many a times and (normally) worked every time. This is the first time I faced this issue with this server.
Thank God I finally solved the issue. If you find anything like this type of issue, then I can suggest trying to clean up those temporary files.
If someone can give better solution on the above issue are also welcome and thanks to him or her in advance.
by: mark_wills on 2009-09-21 at 23:40:30ID: 3673
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.