MS SQL 비트연산(&)

DataBase/MS-SQL|2020. 12. 22. 10:42
반응형

사용 배경은 계속적으로 추가되는 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 

 

위와 같이 찾는다.

 

 

댓글()

디비 백업 프로시저 및 리포트 메일 발송 기능

DataBase/MS-SQL|2020. 10. 5. 22:30
반응형

전문가 분들은 광고 클릭후 닫아주시면 감사하겠습니다. :)

저와 같이 운영관리에 익숙치 않은 분들을 위한 실 사용 사례입니다.

 

 

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 에는 생각보다 많은 정보가 들어있습니다.

bit.ly/2Gxce24 참고

 

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">&nbsp;</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. 결과적으로 시스템에서 발송해서 수신된 메일은 아래와 같습니다.

 

 

 

적당히 내용을 수정하여 입맛에 맛도록 수정 사용하시기 바랍니다.

댓글()

저장하지 않은 쿼리 확인 하는 방법 , 강제 종료 시 쿼리 복구

DataBase/MS-SQL|2020. 9. 29. 23:34
반응형

때때로 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

댓글()

MSSQL 차등백업 방법 및 SQL

DataBase/MS-SQL|2020. 4. 13. 17:01
반응형

차등 백업은 전체 백업 마지막 시점을 기준으로 이후에 수정된 내용을 백업는 방법입니다.

기본적인 내용과 SQL문을 공유합니다.

 

-- 전체 백업

BACKUP DATABASE [test] TO  DISK = N'D:\Backup\test_full.bak' WITH NOFORMAT, NOINIT,  NAME = N'test-전체 데이터베이스 백업', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

 

 

-- 차등백업 1 

BACKUP DATABASE [test] TO  DISK = N'D:\Backup\test_1.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'test-전체 데이터베이스 백업', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

 

 

-- 차등백업 2 

BACKUP DATABASE [test] TO  DISK = N'D:\Backup\test_2.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'test-전체 데이터베이스 백업', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

 

 

 

-- 기본 전체 백업 + 차등 복구

문법

RESTORE DATABASE [디비명] FROM DISK = N'풀백업위치'

               WITH MOVE '논리적 DB명' TO 'mdf 복구위치'

                            ,MOVE '논리적 LOG명' TO 'ldf 복구위치',  NORECOVERY,  NOUNLOAD,  STATS = 5;

RESTORE DATABASE [tes디비명] FROM  DISK = N'차등백업위치' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

 

 

RESTORE DATABASE [test3] FROM DISK = N'D:\Backup\test_full.bak'

               WITH MOVE 'test' TO 'C:\DATABASE\MSSQL15.MSSQLSERVER\MSSQL\DATA\test3.mdf'

                            ,MOVE 'test_log' TO 'C:\DATABASE\MSSQL15.MSSQLSERVER\MSSQL\DATA\test3.ldf',  NORECOVERY,  NOUNLOAD,  STATS = 5;

RESTORE DATABASE [test3] FROM  DISK = N'D:\Backup\test_2.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

 

 

 

백업 및 복구 시점별 데이터 체크 

test : 풀백업  ,  test1 : 풀백업+차등1st ,  test2 : 풀백업+차등2nd

 

 

개인적인 생각에서

 

장점은 백업 용량이 데이터 변화에 비례하기 때문에 수정이 적다면 용량도 적어집니다.

수정된 데이터와 용량은 비례하게 됩니다.

 

단점은 전체 백업이 소실되면 복구가 불가능함. 전체 백업본이 있어야 차등백업본이 의미가 있기 때문에

무슨일이 있어도 전체 백업은 잘 보관해야합니다.

 

적당주기별 전체 백업과 차등백업을 처리한다면...스토리지가 좋아합니다.

댓글()

IDENTITY_INSERT가 OFF로 설정되면 테이블 '테이블명'의 ID 열에 명시적 값을 삽입할 수 없습니다.

DataBase/MS-SQL|2019. 11. 22. 12:11
반응형

쿼리 

 CREATE TABLE dbo.[테이블명] (
        seq int IDENTITY (1, 1) NOT NULL,
       id varchar(20) NOT NULL
 )
 
insert into dbo.[테이블명] (seq,id) values(1,'abc')

IDENTITY_INSERT가 OFF로 설정되면 테이블 '테이블명'의 ID 열에 명시적 값을 삽입할 수 없습니다.

 

IDENTITY 가 있는 데이터 테이블 생성 후 해당 컬럼 입력 시도 할 경우 에러 발생

 

아래와 같이 IDENTITY_INSERT ON 처리 후 실행 시 가능해짐

반드시 INSERT 후에 OFF 처리 해야 이후 문제가 안생김

SET IDENTITY_INSERT dbo.[테이블명] ON

insert into dbo.[테이블명] (seq,id) values(1,'abc')

SET IDENTITY_INSERT dbo.[테이블명] OFF

댓글()

MSSQL - 시간 분 시간별 그룹화 / 5분단위로

DataBase/MS-SQL|2019. 11. 5. 15:43
반응형

년,월,일,시,분 단위로 그룹핑하여 보기 각종 통계시 활용

 

select DATEPART(YEAR, adddt) '년'
,DATEPART(MONTH, adddt) '월'
,DATEPART(DAY, adddt) '일'
,DATEPART(HOUR, adddt) '시'
-- ,(DATEPART(MINUTE, adddt) / 10)
,count(*) FRom 테이블명 with(nolock)
 GROUP BY
DATEPART(YEAR, adddt)
,DATEPART(MONTH, adddt) 
,DATEPART(DAY, adddt)
,DATEPART(HOUR, adddt)
--,(DATEPART(MINUTE, adddt) / 10)
order by 1,2,3,4


select TIMEFROMPARTS(
         DATEPART(HOUR, adddt),
         DATEPART(MINUTE, adddt) / 5 * 5, 0,
         0,
         0)
,count(*) FRom 테이블명 with(nolock) 
group by TIMEFROMPARTS(
         DATEPART(HOUR, adddt),
         DATEPART(MINUTE, adddt) / 5 * 5, 0,
         0,
         0)
order by 1

 

댓글()

MSSQL, 테이블 및 인덱스 사이즈 구하기, Table and Index size in SQL Server

DataBase/MS-SQL|2019. 11. 5. 14:09
반응형
create table #t
(
  name nvarchar(128),
  rows varchar(50),
  reserved varchar(50),
  data varchar(50),
  index_size varchar(50),
  unused varchar(50)
)

declare @id nvarchar(128)
declare c cursor for
select '[' + sc.name + '].[' + s.name + ']' FROM sysobjects s INNER JOIN sys.schemas sc ON s.uid = sc.schema_id where s.xtype='U'

open c
fetch c into @id

while @@fetch_status = 0 begin

  insert into #t
  exec sp_spaceused @id

  fetch c into @id
end

close c
deallocate c

select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc

drop table #t

 

 

참고 URL : https://stackoverflow.com/questions/316831/table-and-index-size-in-sql-server

댓글()

database mdf 파일 사이즈 축소

DataBase/MS-SQL|2019. 10. 11. 10:52
반응형

 

로그 서버에 DB 파일 사이즈가 900기가에 육박하여 불필요한 로그를 삭제하고 

 

DBCC SHRINKFILE(DB명, TRUNCATEONLY)

 

위와 같이 축소를 했다.

 

하지만 크게 줄어들지 않은 mdf 파일 사이즈

 

왜 그렇지 하고 찾아보니 SHRINKFILE 옵션을 찾아보니 

 

(MS docs 링크)

 

NOTRUNCATE
target_percent의 지정 여부와 관계없이 데이터 파일의 끝에서 할당된 페이지를 파일 앞의 할당되지 않은 페이지로 이동합니다. 파일 끝의 사용 가능한 공간이 운영 체제에 반환되지 않고, 파일의 물리적 크기가 변경되지 않습니다. 그러므로 NOTRUNCATE를 지정하면 파일이 축소되지 않는 것처럼 보입니다. NOTRUNCATE는 데이터 파일에만 적용되며 로그 파일에는 영향을 주지 않습니다. 이 옵션은 FILESTREAM 파일 그룹 컨테이너에서 지원되지 않습니다.

 

TRUNCATEONLY
파일 끝의 모든 사용 가능한 공간을 운영 체제로 해제하지만 파일 내에서 페이지 이동을 수행하지 않습니다. 데이터 파일은 마지막으로 할당된 익스텐트까지만 축소됩니다. TRUNCATEONLY로 지정되면 target_size가 무시됩니다.
TRUNCATEONLY 옵션은 로그에 있는 정보를 이동시키지 않습니다. 하지만 로그 파일의 끝에서 비활성 상태의 VLF를 제거합니다. 이 옵션은 FILESTREAM 파일 그룹 컨테이너에서 지원되지 않습니다.

 

TRUNCATEONLY 옵션으로는 중간중간에 비어있는 공간에 대해서 해제를 하지 못한다는 것.

 

NOTRUNCATE 옵션으로 중간중간 비어있는 공간들을 당겨주고

TRUNCATEONLY 옵션으로 해제해주어야 원하는 결과를 얻을 수 있었다.

 

 


DBCC SHRINKFILE(DB명,NOTRUNCATE) 
-- DB 빈 공간 정리 마치 조각 모음과 같다. ( 빈 공간은 그대로 남아있다 )

DBCC SHRINKFILE(DB명, TRUNCATEONLY) 
-- 빈 여유 공간 제거 ( 사이즈 축소 )

 

 

 

댓글()