SQL Server Agent Job에서 sysploicy_purge_history  작업실패

 

·       Version : SQL Server

 

SQL Server 2008 이후로 SQL Server Agent Job에서는 syspolicy_purge_history라는 job 등록되어 있다해당 Job MSDB 쌓여 있는 히스토리를 정리하는 작업을 진행한다 가끔 해당 Job 실행시 아래와 같은 오류가 발생할  있는데오류가 무엇인지 확인해보고 해결하는 방법에 대해서 살펴 본다.

 

Job 실패하였을때해당 로그를 살펴보면 아래와 같은 오류로그가 발생하였다.

Date                    7/29/2019 1:23:41 PM

Log                       Job History (syspolicy_purge_history)

 

Step ID                3

Server                

Job Name                         syspolicy_purge_history

Step Name                       Erase Phantom System Health Records.

Duration                           00:00:00

Sql Severity        0

Sql Message ID  0

Operator Emailed          

Operator Net sent         

Operator Paged

Retries Attempted          0

 

Message

Executed as user: NT Service\SQLSERVERAGENT. The job script encountered the following errors. These errors did not stop the script:  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The specified module 'SQLPS' was not loaded because no valid module file was found in any module directory.  '  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'SQLSERVER:'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'SQLSERVER:' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.  '.  Process Exit Code -1.  The step failed.

 

해당 Job  실행하는 명령을 직접 Powershell콘솔에서 실행하여도 아래와 같은 오류가 발생한다.

 

해당 오류는 명령이 참조하는 모듈의 PATH 설정이 누락되어 발생한 경우로시스템 변수에서 해당 변수값을 지정하면 해결이 가능하다모듈의 PATH 경로는 SQL 버전마다 조금씩 다르며 아래 경로를 참고할  있도록 한다.

C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules

 

모듈이 위치한 경로를 확인하였으면 [My Computer] – [right click] – [Properties]에서 [Advance] 탭을 선택하여 [Environmeont Variables]  클릭한다 PSModulePath 이름으로 모듈이 위차한 PATH 등록(또는 수정)한다.

 

경로가 올바르게 수정되었으면, SQLPS 시작하여 정상적으로 PATH 설정이 완료되었는지 확인이 가능하다또는 SQL Agent에서 해당 Job 실행하여 정상적으로 실행이 되는지 확인할  있다.

 

 

[참고자료]

https://blog.sqlauthority.com/2016/03/08/sql-server-syspolicy_purge_history-job-failing-step-erase-phantom-system-health-records/

 

2019-08-07 / Sungwook Kang / http://sungwookkang.com

 

SQL Server, Agent job, sysploicy_purge_history , Agent 히스토리 삭제, PSModulePath, Erase Phantom System Health Records



출처: https://sqlmvp.tistory.com/1330?category=618825 [Database Lab]




profile

강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.