January 31, 2011 at 7:13 am
Hi,
We are running a stand alone SQL2008 standard Edition SP1 x64 (non-Clustered) server.
We needed to rename our server after a migration, and after that syspolicy_purge_history does not run. The job was also accedently deleted from SQLAgent.
I tried the fixes from Microsoft and even the rebuile script:
DECLARE @jobId uniqueidentifier
-- Obtain the current job identifier that is associated with the PurgeHistory
SELECT @jobId = CAST(current_value AS uniqueidentifier)
FROM msdb.dbo.syspolicy_configuration_internal
WHERE name = N'PurgeHistoryJobGuid'
-- Delete the job identifier association in the syspolicy configuration
DELETE FROM msdb.dbo.syspolicy_configuration_internal
WHERE name = N'PurgeHistoryJobGuid'
-- Delete the offending job
EXEC msdb.dbo.sp_delete_job @job_id = @jobId
-- Re-create the job and its association in the syspolicy configuration table
EXEC msdb.dbo.sp_syspolicy_create_purge_job
So does anyone have a solution to rebuild the syspolicy_purge_history job so my msdb will not get huge!
Please help.
January 31, 2011 at 12:21 pm
I am sure you got this idea already, but how about getting the script from another server and double check to see if you can put it on the deleted server?
January 31, 2011 at 1:06 pm
Yes, as a last resort. Just shaking my head on how the create script provided by Microsoft doesn't work.
I'll copy one fom another server and hope it works.
January 31, 2011 at 2:07 pm
copy the job from another server and don't forget to change the computer name in the job step 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply