[스크립트]미러링/오류로그/배치/세션유입 체크

 

Active 세션 조회

SELECT er.session_id AS [세션ID], er.status AS [상태],
	   er.wait_type AS [대기유형],blocking_session_id,wait_resource ,
	   CONVERT(float, er.total_elapsed_time / 1000.0) AS [경과시간(s)], -- 쿼리 경과 시
	   CONVERT(float, er.wait_time / 1000) AS [대기시간(s)], -- 대기 시간
	   DB_NAME(er.database_id) AS [데이터베이스],es.host_name as [요청서버],login_name,
	   CONVERT(float, er.logical_reads * 8 / 1024.0) AS [읽기(MB)], -- 논리적 읽기
	   CONVERT(float, er.granted_query_memory * 8 / 1024.0) AS [쿼리메모리(MB)],  -- 쿼리 메모리
	   CONVERT(float, er.cpu_time / 1000.0) AS [CPU시간(s)], -- CPU 시간
	   er.command AS [명령],
	   CASE WHEN st.objectid IS NULL THEN 'Ad-hoc'
			 ELSE QUOTENAME(DB_NAME(st.dbid)) 
	  		 + '.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid))
			 + '.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)) END AS [프로시저이름],
	   left(st.text,3000) AS [쿼리문], 
	  -- transaction_isolation_level as [격리수준],
	  qp.query_plan AS [실행계획],*
	FROM sys.dm_exec_requests AS er  
	JOIN sys.dm_exec_sessions AS es    ON er.session_id = es.session_id
	INNER JOIN sys.sysprocesses AS sp    ON er.session_id = sp.spid
	CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st  
	CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) AS qp  
	WHERE 1=1
		--and er.session_id > 50
		--AND ( er.total_elapsed_time > @total_elapsed_time
		--	OR er.logical_reads > @logical_reads
		--	 )
			  
	order by er.total_elapsed_time desc
		 
/*
SELECT
 A.SESSION_ID, A.CONNECT_TIME,client_net_address,client_tcp_port, B.TEXT
FROM SYS.DM_EXEC_CONNECTIONS AS A
CROSS APPLY SYS.DM_EXEC_SQL_TEXT (A.MOST_RECENT_SQL_HANDLE) AS B
WHERE A.MOST_RECENT_SESSION_ID = 77
*/

 

에러로그 조회

DECLARE @datePre NVARCHAR(16)
DECLARE @datePost NVARCHAR(16)
set @datePre  = left(convert(nvarchar(40),dateadd(hour,-4,getdate()),120) ,16)
set @datePost = left(convert(nvarchar(40),dateadd(hour,0,getdate()),120) ,16)
if OBJECT_ID('tempdb..#ErrorLogHistory') is not null
	drop table #ErrorLogHistory 
CREATE TABLE #ErrorLogHistory(
	 [OccuredDate] datetime,
	[procInfo] [varchar](100) NULL,
	[txt] [nvarchar](max) NULL )
 
insert into #ErrorLogHistory
	exec [master].sys.xp_readerrorlog 0,1,null,null,@datePre,@datePost;
 
 select    [occureddate],procInfo,txt
 from #ErrorLogHistory
 
 order by OccuredDate desc
   

 

미러링 상태 체크

 SELECT
DB_NAME(database_id) AS 'DatabaseName', mirroring_role_desc
, mirroring_safety_level_desc , mirroring_state_desc
, mirroring_safety_sequence, mirroring_role_sequence
, mirroring_partner_instance , mirroring_witness_name
, mirroring_witness_state_desc , mirroring_failover_lsn
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;

 배치 실패 체크

DECLARE @Query NVARCHAR(max)
DECLARE @Param_date1 varchar(16), @Param_date2 varchar(16) --, @DBMSName varchar(30)
DECLARE @paramDefinit NVARCHAR(100)

set @paramDefinit = N'@Date1 varchar(8),@date2 varchar(8)'
set @Param_date1 = convert(varchar(8),dateadd(d,-1,getdate()),112)
set @Param_date2 = convert(varchar(8),getdate(),112)

 
 SELECT  JobL.name as [배치명], JobH.step_name as [스탭단계], 
		 REPLACE(message, '. ', '.' + CHAR(10)) as [메시지] , msdb.dbo.agent_datetime([JobH].run_date,[JobH].run_time) as [실패시간],   
		CASE WHEN LEN([JobH].run_duration ) IN (1,2) THEN Right([JobH].run_duration ,2)+'초'
			WHEN LEN([JobH].run_duration ) = 3 THEN 
						SUBSTRING(cast([JobH].run_duration  as varchar),1,1) + '분 ' + Right([JobH].run_duration ,2) + '초'
			WHEN LEN([JobH].run_duration ) = 4 THEN
						SUBSTRING(cast([JobH].run_duration  as varchar),1,2) + '분 ' + Right([JobH].run_duration ,2) + '초'
			WHEN LEN([JobH].run_duration ) = 5 THEN 
						SUBSTRING(cast([JobH].run_duration  as varchar),1,1) + '시 ' +
						SUBSTRING(cast([JobH].run_duration  as varchar),2,2) + '분 ' + 
						Right([JobH].run_duration ,2) + '초'
			WHEN LEN([JobH].run_duration ) = 6 THEN
						SUBSTRING(cast([JobH].run_duration  as varchar),1,2) + '시 ' +
						SUBSTRING(cast([JobH].run_duration  as varchar),3,2) + '분 ' + 
						Right([JobH].run_duration ,2) + '초'
		end as [수행시간],
		 msdb.dbo.agent_datetime([JobS].next_run_date,[JobS].next_run_time) as [다음 일정]
	  FROM  [msdb].dbo.sysjobhistory AS [JobH] WITH(NOLOCK)
	  left join msdb.dbo.sysjobs   as [JobL] WITH (NOLOCK)
	  on JobH.job_id = JobL.job_id
	  left join msdb.dbo.[sysjobschedules] as [JobS] with (NOLOCK)
	  on JobH.job_id = JobS.job_id
	  WHERE [JobH].run_date between @Param_date1 and @Param_date2
		and [JobH].run_status = 0  and [JobH].step_id > 0