MariaDB MaxScale 2022.10 웨비나
https://www.youtube.com/watch?v=rhtL0mtIGP0
https://www.youtube.com/watch?v=rhtL0mtIGP0
로그 파일에 대부분 용량을 차지하는 /* Ping server ... */ 메시지
숨기는 방법
환경설정 - 로그 기록 - 디버그 메시지 체크 해제
SELECT TABLE_SCHEMA
, TABLE_NAME
, COLUMN_NAME
, CHARACTER_MAXIMUM_LENGTH
, ORDINAL_POSITION
, COLUMN_DEFAULT
, DATA_TYPE
, COLUMN_TYPE
, COLUMN_KEY
, EXTRA
, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '디비명'
AND COLUMN_NAME = '컬럼명';
동일 컬럼이 여러개 인 경우 뒤져보기.
사용 배경은 계속적으로 추가되는 Flag 컬럼에 대해 고민 중 하나의 공용 컬럼에 값을 다 넣어버리기로 함.
DECLARE @i INT
SET @i = 6
SELECT @i & 1 , 결과 => 0
SELECT @i & 2 , 결과 => 2
SELECT @i & 4 , 결과 => 4
SELECT @i & 6 , 결과 => 6
SELECT @i & 8 , 결과 => 0
INT 타입의 컬럼에 정수형 데이터를 넣고 2진법으로 보아 계산을 하는 방법.
2번째 조회인 @i & 2 의 내용을 보면.
6 > 0110
2 > 0010
======
0010 > 2
결과 값이 2가 나온다.
일반적으로 사용할 경우에는
SELECT *
FROM 테이블
WHERE 컬럼 & 2 > 0
위와 같이 0보다 크거나(True) 작은 경우(False)로 구분하여 사용된다.
ex)
취미 체크하기
ㅁ 축구(1) ㅁ농구(2) ㅁ독서(4)
위와 같이 체크박스가 있는 경우 취미가 축구와 독서일 경우
저장값은 두 값의 합을 넣는다. >> 5
DECLARE @i INT
SET @i = 5
SELECT @i & 1 , 결과 => 1
SELECT @i & 2 , 결과 => 0
SELECT @i & 4 , 결과 => 4
축구가 포함된 경우를 찾으려면
SELECT *
FROM TABLE
WHERE 취미 & 1 > 0
위와 같이 찾는다.
SSMS(SQL Server Management Studio)
도구 - 옵션에 아래 옵션을 체크 해제 하면 됩니다.
쿼리실행 - SQL Server - 고급 - 완료 시간 표시 체크 해제
전문가 분들은 광고 클릭후 닫아주시면 감사하겠습니다. :)
저와 같이 운영관리에 익숙치 않은 분들을 위한 실 사용 사례입니다.
1. master DB에 백업 기록을 위한 테이블을 생성했습니다.
USE [master]
GO
-- 시작안되는 경우도 발생하기 때문에 startDt 기본값을 넣지 않음.
CREATE TABLE [dbo].[tblBackupDB](
[dbName] [varchar](50) NULL,
[startDt] [datetime] NULL,
[endDt] [datetime] NULL,
[msg] [ntext] NULL,
[mdfSize] [int] NULL,
[ldfSize] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
2. 백업을 위한 프로시저 생성, DATEPART를 이용해 특정 요일마다 풀백업과 차등백업을 반복하도록 설정.
샘플을 위해 특정 요일에 풀백업 그 외 차등백업으로 규정하였으나
적절한 풀백업과 차등백업을 번갈아가며 환경에 맞는 주기를 설정합니다.
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_DataBaseBackup]
AS
BEGIN
DECLARE @dbName varchar(4000), @SQL varchar(4000)
DECLARE @yoil int , @mdfSize int , @ldfSize int
SELECT @yoil = DATEPART(WEEKDAY,GETDATE())
-- 백업대상 기록 , 시스템 디비는 제외
INSERT INTO master.dbo.tblBackupDB(dbName)
SELECT name
FROM sys.databases
WHERE UPPER(name) NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
-- 서버 아이피 체크
declare @ip_v4 varchar(16)
SELECT DISTINCT @ip_v4 = local_net_address
FROM sys.dm_exec_connections
WHERE local_net_address IS NOT NULL and local_net_address <> '127.0.0.1'
DECLARE bk_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE UPPER(name) NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN bk_cursor
FETCH NEXT FROM bk_cursor
INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
-- mdf 파일 사이즈
SELECT @mdfSize = SUM(size)*8/1024 --MB
FROM sys.databases
JOIN sys.master_files
ON sys.databases.database_id=sys.master_files.database_id
WHERE sys.databases.name = @dbName and type='0'
-- ldf 파일 사이즈
SELECT @ldfSize = SUM(size)*8/1024 --MB
FROM sys.databases
JOIN sys.master_files
ON sys.databases.database_id=sys.master_files.database_id
WHERE sys.databases.name = @dbName and type='1'
-- 백업시작 기록/용량 기록
UPDATE master.dbo.tblBackupDB
SET startDt = GETDATE() , mdfSize = @mdfSize, ldfSize = @ldfSize
WHERE dbname = @dbName
AND startDt is null
IF @yoil = 7 -- 토요일은 풀백업
BEGIN
--풀백업
SET @SQL = 'BACKUP DATABASE ['+@dbName+'] TO DISK = N''D:\Backup\'+convert(varchar(30), getdate(),112)+'_'+@dbName+'.bak'' WITH COMPRESSION, INIT, NAME = '''+@dbName+' - Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10;'
END
ELSE -- 주간은 차등백업
BEGIN
--차등백업
SET @SQL = 'BACKUP DATABASE ['+@dbName+'] TO DISK = N''D:\Backup\'+convert(varchar(30), getdate(),112)+'_'+@dbName+'.bak'' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'''+@dbName+' - Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10;'
END
--print @SQL
EXEC (@SQL)
-- 백업종료 기록
UPDATE master.dbo.tblBackupDB
SET endDt = GETDATE()
WHERE dbname = @dbName
AND CONVERT(CHAR(10),startDt,23) = CONVERT(CHAR(10),GETDATE(),23)
AND endDt IS NULL
END TRY
BEGIN CATCH
-- 에러 기록
UPDATE master.dbo.tblBackupDB
SET msg = 'ERROR_NUMBER : ' + CAST(ERROR_NUMBER() AS VARCHAR) + '
ERROR_MESSAGE: ' + ERROR_MESSAGE()
WHERE dbname = @dbName
AND CONVERT(CHAR(10),startDt,23) = CONVERT(CHAR(10),GETDATE(),23)
AND endDt IS NULL
END CATCH
FETCH NEXT FROM bk_cursor
INTO @dbName
END
CLOSE bk_cursor;
DEALLOCATE bk_cursor;
-- FREEPROCCACHE 는 메모리 상에 전체 캐시를 비우기
-- https://bit.ly/3icmJ85
DBCC FREEPROCCACHE;
END
3. 백업 후 남겨진 기록과 msdb.dbo.backupset 테이블 데이터를 참조하여 리포팅 메일 발송.
메일 발송을 위해 데이터베이스 메일 구성이 되어있어야 합니다.
html 코딩은 개인 취향대로.. msdb.dbo.backupset 에는 생각보다 많은 정보가 들어있습니다.
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spSendBackupReport]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @dbName varchar(100),@sDt datetime , @eDt datetime , @result nvarchar(50)
DECLARE @html varchar(max) , @ip_v4 varchar(16) , @subject varchar(200)
DECLARE @idx int , @mdfSize int , @ldfSize int , @backupSize int ,@totalBackupSize bigint , @BackupType nvarchar(20)
SET @idx = 1
SELECT DISTINCT @ip_v4 = local_net_address
FROM sys.dm_exec_connections
WHERE local_net_address IS NOT NULL and local_net_address <> '127.0.0.1'
SET @subject = 'DB Server Backup Report - '+ @ip_v4
SET @totalBackupSize = 0
SET @html = '
<div><h2>시스템에서 자동 발송되는 DB 백업 결과 메일입니다.</h2></div>
<div><h3> IP : '+@ip_v4+'</h3></div>
<div><h3> DATE : '+ CONVERT(VARCHAR(10) , GETDATE(),121) +'</h3></div>
<div><h3> Total Backup Size : #TOTALBACKUPSIZE# ( ※ 단순참고용, 연산에 의한 오차발생 ) </h3></div>
<table style="border-collapse: collapse;font-size: small;">
<tr>
<td colspan="6"> </td>
</tr>
<tr "class="tr">
<td style="width:20px;border: 1px solid black;text-align:center;padding: 5px;">#</td>
<td style="width:150px;border: 1px solid black;text-align:center;padding: 5px;">DB</td>
<td style="width:150px;border: 1px solid black;text-align:center;padding: 5px;">DB Size</td>
<td style="width:180px;border: 1px solid black;text-align:center;padding: 5px;">Backup Size<br/>/ Type</td>
<td style="width:100px;border: 1px solid black;text-align:center;padding: 5px;">Start<br/>/ End</td>
<td style="width:100px;border: 1px solid black;text-align:center;padding: 5px;">Processing Time (Min.)</td>
<td style="width:70px;border: 1px solid black;text-align:center;padding: 5px;">Result</td>
</tr>
'
-- https://bit.ly/2Gxce24 // msdb.dbo.backupset 백업셋 데이터 테이블 참조
DECLARE vendor_cursor
CURSOR FOR
SELECT D.name ,B.startDt , B.endDt
, case when B.endDt is null then '<span style="color:red;font-weight:bold;">실패</span>' else '성공' end as result
, B.mdfSize , B.ldfSize
, BS.compressed_backup_size
, case Type when 'I' then N'차등백업' when 'D' then N'풀백업' end as BackupType
FROM sys.databases D
LEFT OUTER JOIN tblbackupDB B
ON D.name = DBNAME AND CONVERT(CHAR(10),endDt,23) = CONVERT(CHAR(10),GETDATE(),23)
LEFT OUTER JOIN msdb..backupset BS
ON D.name = BS.database_name AND CONVERT(CHAR(10),BS.backup_start_date,23) = CONVERT(CHAR(10),GETDATE(),23)
WHERE UPPER(D.name) NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor
INTO @dbName ,@sDt , @eDt, @result , @mdfSize , @ldfSize , @backupSize , @BackupType
WHILE @@FETCH_STATUS = 0
BEGIN
SET @html +='
<tr>
<td style="border: 1px solid black;text-align:center;padding: 5px;">'+ cast(@idx as varchar) +'</td>
<td style="border: 1px solid black;text-align:center;padding: 5px;">'+@dbName+'</td>
<td style="border: 1px solid black;text-align:center;padding: 5px;">DATA : '+ CAST(FORMAT(isnull(@mdfSize,0)/1024, N'#,0') AS VARCHAR)+' GB <br/>Log : '+ CAST(FORMAT(isnull(@ldfSize,0), N'#,0') AS VARCHAR)+' MB</td>
<td style="border: 1px solid black;text-align:center;padding: 5px;">'+ CAST(FORMAT(isnull(@backupSize,0)/1024/1024, N'#,0') AS VARCHAR)+' MB<br/>'+ @BackupType+'</td>
<td style="border: 1px solid black;text-align:center;padding: 5px;">'+ltrim(rtrim(isnull(CONVERT(CHAR(8), @sDt, 8),'-')))+'<br/>'+ltrim(rtrim(isnull(CONVERT(CHAR(8), @eDt, 8),'-')))+'</td>
<td style="border: 1px solid black;text-align:center;padding: 5px;">'+case when ltrim(rtrim(isnull(DATEDIFF(MINUTE,@sDt,@eDt),'-'))) = '0' then N'1 미만' else ltrim(rtrim(isnull(DATEDIFF(MINUTE,@sDt,@eDt),'-'))) end +'</td>
<td style="border: 1px solid black;text-align:center;padding: 5px;">'+@result+'</td>
</tr>
'
SET @totalBackupSize += @backupSize
SET @idx = @idx + 1
FETCH NEXT FROM vendor_cursor
INTO @dbName ,@sDt , @eDt, @result, @mdfSize , @ldfSize , @backupSize , @BackupType
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;
SET @html += '</table>'
SET @html = replace(@html,'#TOTALBACKUPSIZE#',CAST(FORMAT(isnull(@totalBackupSize,0)/1024/1024, N'#,0') AS VARCHAR)+' MB')
EXEC msdb. dbo.sp_send_dbmail -- sp_send_dbmail 상세 >> https://bit.ly/30zkdTj
@profile_name='메일프로필',
@body_format = 'HTML' , -- 바디타입
@recipients= '받을사람',
@blind_copy_recipients= '숨김참조',
@subject = @subject,
@body =@html
END
4. 결과적으로 시스템에서 발송해서 수신된 메일은 아래와 같습니다.
적당히 내용을 수정하여 입맛에 맛도록 수정 사용하시기 바랍니다.
때때로 ssms가 강제 종료 된다거나 저장하지 않고 닫았을 때
SQL 쿼리를 복구 해야하는 경우 아래의 쿼리에서 일정 시간동안 쿼리 확인이 가능합니다.
Use <database>
SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC
참조 Url : qastack.kr/programming/15034832/recover-unsaved-sql-query-scripts
SQL Server 에이전트에 등록된 작업 목록을 조회 하는 SQL 입니다.
SELECT
A.name,
case description when '설명이 없습니다.' then '' else description end as description,
CASE convert(
nvarchar(3),
[freq_type],
3
) WHEN 4 THEN N' 매일' WHEN 8 THEN N' 매주 ' + CASE WHEN [freq_interval] & 1 = 1 THEN N'일' ELSE '' END
+ CASE WHEN [freq_interval] & 2 = 2 THEN ' 월' ELSE '' END + CASE WHEN [freq_interval] & 4 = 4 THEN ' 화' ELSE '' END + CASE WHEN [freq_interval] & 8 = 8 THEN ' 수' ELSE '' END + CASE WHEN [freq_interval] & 16 = 16 THEN N' 목' ELSE '' END + CASE WHEN [freq_interval] & 32 = 32 THEN N' 금' ELSE '' END + CASE WHEN [freq_interval] & 64 = 64 THEN N' 토' ELSE '' END WHEN 16 THEN N'하루 한번 '
+ CAST(
[freq_interval] AS VARCHAR(3)
) + N' 매달 ' + CAST(
[freq_recurrence_factor] AS VARCHAR(3)
) + N' 매달' WHEN 32 THEN N'발생 ' + CASE [freq_relative_interval] WHEN 1 THEN N'첫째' WHEN 2 THEN N'둘째' WHEN 4 THEN N'셋째' WHEN 8 THEN N'넷째' WHEN 16 THEN N'마지막' END + ' ' + CASE [freq_interval] WHEN 1 THEN N'일' WHEN 2 THEN N'월' WHEN 3 THEN N'화' WHEN 4 THEN N'수' WHEN 5 THEN N'목' WHEN 6 THEN N'금' WHEN 7 THEN N'토' WHEN 8 THEN N'일' WHEN 9 THEN N'평일' WHEN 10 THEN N'주말' END + N' 모든 ' + CAST(
[freq_recurrence_factor] AS VARCHAR(3)
) + N' 매달' END AS [Recurrence],
CASE convert(
nvarchar(3),
[freq_subday_type],
3
) WHEN 1 THEN STUFF(
STUFF(
RIGHT(
'000000' + CAST(
[active_start_time] AS VARCHAR(6)
),
6
),
3,
0,
':'
),
6,
0,
':'
) WHEN 2 THEN N'주기적 발생 ' + CAST(
[freq_subday_interval] AS VARCHAR(3)
) + N' 초(s) 간격 ' + STUFF(
STUFF(
RIGHT(
'000000' + CAST(
[active_start_time] AS VARCHAR(6)
),
6
),
3,
0,
':'
),
6,
0,
':'
) + ' & ' + STUFF(
STUFF(
RIGHT(
'000000' + CAST(
[active_end_time] AS VARCHAR(6)
),
6
),
3,
0,
':'
),
6,
0,
':'
) WHEN 4 THEN N'주기적 발생 ' + CAST(
[freq_subday_interval] AS VARCHAR(3)
) + N' 분(s) 간격 ' + STUFF(
STUFF(
RIGHT(
'000000' + CAST(
[active_start_time] AS VARCHAR(6)
),
6
),
3,
0,
':'
),
6,
0,
':'
) + ' & ' + STUFF(
STUFF(
RIGHT(
'000000' + CAST(
[active_end_time] AS VARCHAR(6)
),
6
),
3,
0,
':'
),
6,
0,
':'
) WHEN 8 THEN N'주기적 발생 ' + CAST(
[freq_subday_interval] AS VARCHAR(3)
) + N' 시간(s) 간격 ' + STUFF(
STUFF(
RIGHT(
'000000' + CAST(
[active_start_time] AS VARCHAR(6)
),
6
),
3,
0,
':'
),
6,
0,
':'
) + ' & ' + STUFF(
STUFF(
RIGHT(
'000000' + CAST(
[active_end_time] AS VARCHAR(6)
),
6
),
3,
0,
':'
),
6,
0,
':'
) END [Frequency], D.step_id, D.step_name, D.command
FROM msdb.dbo.sysjobs A
INNER JOIN msdb.dbo.sysjobschedules B ON A.job_id = B.job_id
INNER JOIN msdb.dbo.sysschedules C ON B.schedule_id = C.schedule_id
INNER JOIN msdb.dbo.sysjobsteps D ON A.job_id = D.job_id
ORDER BY A.name
실행 결과는 대략 아래와 같이 조회가 됩니다.