[MS-SQL] SQL Server / SQL Server Agent error log (UI, Sql, csv)

MS-SQL에서 SQL Server의 log나 SQL Server Agent의 log를 볼 수 있는 방법에 대해 알아보자

1. UI

관리 -> SQL Server로그 
에 들어가면 Log를 볼 수 있다.
여기서 아무 로그나 더블클릭한다면 아래와 같은 창을 만날 수 있다.

위 화면을 통해 SQL Server / SQL Server Agent 로그를 볼 수 있다. 하지만 메시지를 필터하여 볼 수는 없다.

2. sql

T-SQL을 통해 확인할 수 있으며

-- @p1 : log번호 ( 0: 현재로그 / 1: 보관#1 로그 ..)
-- @p2 : 1: sql server log , 2: sql server agent log (default 1)
-- @p3 : 오류 로그 메시지의 필터링 (string)
-- @p4 : 오류 로그 메시지의 구체화 필터링 (string)
exec sp_readerrorlog @p1, @p2, @p3, @p4;

-- 예시) 현재로그 / sql server / 'database'라는 문자열이 있는 로그
exec sp_readerrorlog 0, 1, 'database';

위와 같이 T-SQL을 사용하여 조회할 수 있다.

하지만 이 T-SQL의 단점은 날짜를 필터링 할 수 없다는 것이다.
따라서 날짜를 필터링 할 수 있도록 테이블 변수에 넣어 날짜를 where절을 통해 필터링 하는 방법도 알아보자

우선 SQL Server log를 테이블 변수에 담아 조회하는 방법을 보면 아래와 같다.

-- 테이블 변수 선언
Declare @errorlog table(
	log_date Datetime,
	process_info nvarchar(50),
	text nvarchar(max)
);

-- insert 현재의 sql server log 
insert into @errorlog(log_date, process_info, text) exec sp_readerrorlog 0, 1;

select *
from @errorlog
where log_date >= format(GetDate()-1, 'yyyy-MM-dd')
and upper(text) like '%ERROR%'
order by log_date desc;

 

 

그리고 SQL Server Agent log를 테이블 변수에 담아 조회하는 방법을 보면 아래와 같다

-- 테이블 변수 선언
Declare @errorlog table(
	log_date Datetime,
	error_level int,
	text nvarchar(max)
);

-- insert 현재의 sql server log 
insert into @errorlog(log_date, error_level, text) exec sp_readerrorlog 0, 2;

select *
from @errorlog
where log_date >= format(GetDate()-1, 'yyyy-MM-dd')
and (upper(text) like '%ERROR%' or error_level = 1)
order by log_date desc;

 

3. csv (PowerShell script)

위 sql을 응용하여 PowerShell Script로 작성하여 '작업'으로 등록해보자.
우선 PowerShell Script는 아래와 같다 (SQL Server log기준으로 작성)

$ServerInstance = "MSSQLSERVER"
$Database = "master"
$Query = "
		Declare @errorlog table(
			log_date Datetime,
			process_info nvarchar(50),
			text nvarchar(max)
		);

		-- insert 현재의 sql server log 
		insert into @errorlog(log_date, process_info, text) exec sp_readerrorlog 0, 1;

		select *
		from @errorlog
		where log_date >= format(GetDate()-1, 'yyyy-MM-dd')
		and upper(text) like '%ERROR%'
		order by log_date desc;
		"
$OutputFile = "C:\test\sqlserver_log.csv"

# SQL execute and export file
$queryResult = Invoke-Sqlcmd -Database $Database -Query $Query

$queryResult | Export-Csv -Path $OutputFile -NoTypeInformation -Encoding UTF8



####################################################################################
## 파일을 append하고 싶다면
<#
if(Test-Path $OutputFile)
{
	$queryResult | Export-Csv -Path $OutputFile -NoTypeInformation -Append -Encoding UTF8
}
else
{
	$queryResult | Export-Csv -Path $OutputFile -NoTypeInformation -Encoding UTF8
}
#>

위와 같이 코드를 작성한 후 
SQL Server 에이전트 -> 작업 (마우스 우클릭) -> 새 작업

일반 탭 -> 이름 

단계 탭 -> 새로 만들기

위와 같이 유형을 'PowerShell'로 지정하고 코드를 붙여넣는다.
일정은 '작업'의 일정이 필요하면 넣도록 하고 test를 진행해보자

SQL Server 에이전트 -> 작업 -> 'sql server log'(마우스 우클릭) -> 작업시작단계

 

를 통해 작업을 시작시킨다.


위의 3가지 방법 (UI, sql, powershell)으로 sql server log, sql server agent log를 조회하는 방법에 대해 알아보았다.