DB Library

설명

아래 값을 모두 변경하여도 SQL Server 카운트가 보이지 않는 경우 ... SQL Server를 재시작

SQL Server 재시작에도 보이지 않는 경우 OS 재시작

1.레지스트리에서 Disable Performance Counters  값 변경 : 1 -> 0

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\Disable Performance Counters

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance\Disable Performance Counters

2. services.msc에 Remote Registry가 시작되었는지 체크
 
3. unlodctr 와 lodctr 을 이용해서 SQL 관련 카운터를 다시 등록할것

1) cmd(Administrator권한으로)

2) SQL SERVER의 binn폴더로 이동

3)unlodctr을 이용해서 SQL counters를 unload한다.
    e.g. unlodctr MSSQLSERVER (for default instance)
    e.g. unlodctr SQLSERVERAGENT (for default SQL Agent)
    e.g. unlodctr MSSQL$TEST (for named instance)
    e.g. unlodctr SQLAGENT$TEST (for SQL agent)

4)lodctr을 이용해서 SQL counters를 다시 등록한다.
    e.g. lodctr perf-MSSQLSERVERsqlctr.ini (for default instance)
    e.g. lodctr perf-SQLSERVERAGENTsqlagtctr.ini (for default SQL Agent)
    e.g. lodctr perf-MSSQL$TESTsqlctr.ini (for named instance)
    e.g. lodctr perf-SQLAGENT$TESTsqlagtctr.ini (for SQL Agent)

5)Remote Registry service를 다시 시작한다.
    net stop "Remote Registry" 
    net start "Remote Registry"

6)필요할 경우 WMI와 WinPrivSE.exe 싱크를 다시 맞춘다.
    e.g. winmgmt /resyncperfctr "5660" 
   cf)5660은 WinPrivSE.exe 의 pid

4. performance counter를 재등록 한다.

lodctr /R --> 모든 
주의!!! 모든 performance counter registry 세팅을 재등록하게 된다.

참고

https://www.travisgan.com/2013/05/missing-sql-performance-counters.html

-- 아래 스크립트를 사용하여 Long Running Check Job을 '[DBA] Long Running Job Check' 이름으로 생성

declare @JOB_INTERVAL_SEC table
(
	job_id   uniqueidentifier
,	run_interval_sec int
)

insert into @JOB_INTERVAL_SEC
select	job_id, datediff(SECOND, min(run_datetime_2), min(run_datetime_1)) as run_interval_sec
from 
(
	select	job_id
		,	case when job_num = 1 then run_datetime else getdate() end AS run_datetime_1
		,	case when job_num = 2 then run_datetime else getdate() end AS run_datetime_2
	from 
	(
		select	job_id
			,	convert(datetime, format(run_date,'####-##-##') + ' ' + format(run_time,'####:##:##'), 120) AS run_datetime
			,	row_number() over(partition by job_id order by  instance_id desc ) AS job_num
		from [msdb].[dbo].[sysjobhistory]
		where step_id = 0
	) AS T
	where job_num in (1,2)
) TT
group by job_id

--select * from @JOB_INTERVAL_SEC

declare	@cur_job_name sysname
	,	@cur_job_id uniqueidentifier
	,	@cur_start_execution_date datetime
	,	@cur_stop_execution_date datetime
	,	@cur_last_executed_step_date datetime
	,	@cur_next_scheduled_run_date datetime
	,	@cur_run_interval_sec int
	,	@cur_running_second int
	,	@cur_error_message varchar(1024)

declare cur cursor for

select	jam.job_name, jam.job_id
	,	sja.start_execution_date, sja.stop_execution_date, sja.last_executed_step_date, sja.next_scheduled_run_date
	,	JIS.run_interval_sec
	,	datediff(second, start_execution_date, getdate()) AS running_second
from
(
	select max(sa.session_id) AS session_id_max, sa.job_id, sj.name AS job_name from msdb.dbo.sysjobactivity sa
	inner join msdb.dbo.sysjobs sj
	on sa.job_id = sj.job_id
	where sj.enabled = 1
	group by  sa.job_id, sj.name
)  jam
inner join  msdb.dbo.sysjobactivity sja
on jam.session_id_max = sja.session_id
and jam.job_id = sja.job_id
inner join @JOB_INTERVAL_SEC JIS
on sja.job_id = JIS.job_id
where sja.stop_execution_date IS NULL
and datediff(second, start_execution_date, getdate()) > JIS.run_interval_sec

open cur
fetch next from cur into @cur_job_name, @cur_job_id, @cur_start_execution_date, @cur_stop_execution_date, @cur_last_executed_step_date, @cur_next_scheduled_run_date, @cur_run_interval_sec, @cur_running_second
		
while @@fetch_status = 0
begin
	set @cur_error_message = '[error] : the job schedule has not yet ended !!   ';
	set @cur_error_message = @cur_error_message + '[job_name] : ' + convert(varchar(100),@cur_job_name) + ' [cur_job_id] : ' + convert(varchar(100),@cur_job_id) + ' [cur_start_execution_date] : ' + convert(varchar(100),@cur_start_execution_date,121)
							+ ' [cur_run_interval_sec] : ' + convert(varchar(100),@cur_run_interval_sec) + ' [cur_running_second] : ' + convert(varchar(100),@cur_running_second)
							--+ ' cur_stop_execution_date : ' + convert(varchar(100),@cur_stop_execution_date,121) + ' cur_last_executed_step_date : ' + convert(varchar(100),@cur_last_executed_step_date) + ' cur_next_scheduled_run_date : ' + convert(varchar(100),@cur_next_scheduled_run_date)							
	
	--select @cur_job_name, @cur_run_interval_sec, @cur_running_second
	--select @cur_error_message
	if @cur_job_name <>  '[DBA] Long Running Job Check'
	BEGIN
		RAISERROR(@cur_error_message, 16,1) 
	END

	fetch next from cur into @cur_job_name, @cur_job_id, @cur_start_execution_date, @cur_stop_execution_date, @cur_last_executed_step_date, @cur_next_scheduled_run_date, @cur_run_interval_sec, @cur_running_second
end

close cur
deallocate cur