Today I would like to share a job failure issue which I faced in my environment. The job name is
syspolicy_purge_history.
What is this Job ?
================
SQL Server 2008 introduced a new feature called
Policy Based Management.
So after the installation of SQL Server 2008 or upgrade to 2008, it will create this job by default, which runs daily @ 2:00AM.
What it Does ?
===============
When a policy is defined and when it runs it stores the results in MSDB in the below tables.
syspolicy_policy_execution_history_details_internal
syspolicy_policy_execution_history_internal
syspolicy_policy_category_subscriptions_internal
Over a period of time,
this may cause data getting piled up and your MSDB will be filled. The job syspolicy_purge_history prunes the data older than the the days defined in HistoryRetentionInDays property of Policy Management.
In any case if the job is not created, then you can go ahead and run the below statement to create it.
EXEC msdb.dbo.sp_syspolicy_create_purge_job
Now we will Come to Our Issue... When I checked the job history, The third step was failing and the reason was mentioned as below.
"Executed as user: The process could not be created for step 3 of job 0x6C76502E1BAA0640B4EA991B90359043 (reason: The process cannot access the file because it is being used by another process). The step failed."
I started troubleshooting the issue with Powershell like changing the Execution Policy roles, but that didn't helped. I even tried stopping the Antivirus, but still the job was failing with same error.
Later I came across a file in the root of C:\ drive called 'Program'. It was little suspicious and was mentioned in some articles regarding the confusion it creates in OS and the problems it created for starting several applications.
I tried renaming it and was unsuccessful. It was throwing an error that its being used by another process. I started my investigations behind this and ran Process Explorer to find the details of the Handle.
Once the Process Explorer was opened, I Clicked on Find Handle, then got a Window to search the file 'Program'. Once I found inside that search window, I selected that inside the above window and right click for the below options.
I choose to close Handle to remove the connection to the file 'Program'. Once that was done, Navigate to the root of C and tried renaming the file and this time I succeeded.
Then I rerun the job, and it completed successfully. HURRAY !!!
It was an excellent learning to me. I hope it was a new info to atleat few DBA's.
Please post your commnets or concerns...
Have a Great day...
Cheers!!!
Thanks
Jinu
