DB Library

mssql 운영시 의도치 않게 데이터가 삭제되는 경우, 추적을 위한 방안

'TestDB' DB의 'tblTest'테이블 데이터 삭제 시, BAK_tblTest_Delete 테이블에 삭제 데이터 및 세션 정보 저장

USE [TestDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[delete_info_insert]
ON [dbo].[tblTest]
FOR UPDATE, DELETE AS

	DECLARE   @login_name	nvarchar(128)
		, @program_name	nvarchar(128)
		, @host_name	nvarchar(128)

			
	SELECT    @login_name = ISNULL(original_login_name, ORIGINAL_LOGIN())
		, @program_name = T1.program_name
		, @host_name = T1.host_name
	FROM sys.dm_exec_sessions T1
	WHERE T1.session_id = @@spid

	INSERT INTO BAK_tblTest_Delete ([Seq], [ID], [TestParam], [CreateDatetime], [loginname], [program_name], [hostname])
	SELECT
		[Seq], [ID], [TestParam], [CreateDatetime], @login_name, @program_name, @host_name
	FROM deleted;

"데이터베이스 보안 주체는 데이터베이스의 스키마을(를) 소유하며 삭제할 수 없습니다."라는 에러는 MSSQL에서 사용자를 삭제하려고 할때 이 사용자가 스키마를 소유하고 있다면 삭제할 수 없다는 에러 메시지가 나온다.

위 그림처럼 SSMS에서 마우스 클릭을 통해  사용자를 삭제하려고 할때 경고 안내창이 나온다.

아래와 같이 Drop User isoltek; 라는 SQL을 실행하여 삭제하려고 하면 결과 메시지에 삭제할 수 없다는 내용이 나온다.

위 에러는 삭제할 사용자가 소유한 스키마를 dbo 로 이관 시키고 삭제하면 해결된다.

사용자가 소유한 스키마를 dbo로 이관 시키는 방법은 두가지가 있다.

가. 마우스 클릭을 통해 이관하는 방법

나. SQL 창에서 SQL을 실행하여 이관하는 방법

이해할 있도록 이 과정을 모두 캡쳐해서 붙인다.

(아래 붙인 그림들은 클릭하면 모두 원본으로 볼 수 있다. 만약 글씨가 작아 크게 보고자 할경우 그림을 클릭하면 된다.)

여기까지가 마우스를 사용한 과정이다.

이것을 SQL 창에서 실행하면 아래와 같은 명령어를 사용하면 위 그림과 같은 결과를 갖는다.

Select * From [sys].[schemas] 
Where [principal_id]=USER_ID('isoltek');

Alter Authorization On Schema::isoltek To dbo;

이렇게 두줄이면 되는 것을 마우스로 하려면 10개 정도의 과정을 수행해야 한다.

사람마다 어떤 것이 편할지는 모른다. 각자 취향이 있으므로 자신에게 편한 방법으로 수행하면 된다.

이렇게 하고난 후 삭제를 하면 "데이터베이스 보안 주체는 데이터베이스의 스키마을(를) 소유하며 삭제할 수 없습니다."라는 에러 없이 삭제할 수 있다.

참고 : https://m.blog.naver.com/reinstate10/220588912597

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @dbname SYSNAME
SET @dbname = QUOTENAME(DB_NAME());


WITH XMLNAMESPACES
	(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT	X.*
	,	t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') as [Schema]
	,	t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') as [Table]
	,	t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') as [Column]
	,	ic.DATA_TYPE AS ConvertFrom
	,	ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength
	,	t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo
	,	t.value('(@Length)[1]', 'int') AS ConvertToLength
FROM
(
	SELECT	db_name(qt.dbid) AS 'db_name'
		,	qt.text AS 'sp_text'
		,	substring(qt.text, (qs.statement_start_offset/2)+1
		,	((  case qs.statement_end_offset
				when -1 then datalength(qt.text)
				else qs.statement_end_offset
				end - qs.statement_start_offset)/2) + 1) as statement_text
		,	qs.creation_time
		,	qs.execution_count AS 'ExecutionCount'
		,	ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second'
		,	DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache(min)'
		,	ISNULL(qs.total_elapsed_time/qs.execution_count, 0) /1000.0AS 'avg_Elapsed_Time(ms)'
		,	qs.total_elapsed_time/1000.0/1000.0 AS 'total_Elapsed_Time(sec)'
		,	max_elapsed_time /1000.0 AS 'max_Elapsed_Time(ms)'
		,	(qs.total_worker_time/qs.execution_count) /1000.0 AS 'avg_Worker_Time(ms)'
		,	qs.total_worker_time/1000.0 AS 'total_Worker_Time(ms)'
		,	max_worker_time /1000.0 as 'max_worker_time(ms)'
		,	ISNULL(qs.total_logical_reads/qs.execution_count, 0) AS 'avg_logical_reads'
		,	total_logical_reads
		,	qs.max_logical_reads
		,	ISNULL(qs.total_physical_reads/qs.execution_count, 0) AS 'avg_physical_reads'
		,	total_physical_reads
		,	qs.max_physical_reads
		,	ISNULL(qs.total_logical_writes/qs.execution_count, 0) AS 'avg_physical_writes'
		,	qs.total_logical_writes
		,	qs.max_logical_writes
		,	cast(qp.query_plan as xml) as query_plan
		,	OBJECT_NAME(qp.objectid,qp.dbid) as sp_name
    FROM sys.dm_exec_query_stats AS qs
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
	CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,qs.statement_start_offset,qs.statement_end_offset) qp
)X
--sys.dm_exec_cached_plans AS cp
--CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t)
JOIN INFORMATION_SCHEMA.COLUMNS AS ic
ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')
AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)')
AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)')
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1

mssql db를 사용하다보면 vlf count가 증가하는 것을 볼수 있다.

vlf count가 증가하면 DML 및 로그 백업시 지연 이슈가 발생 할 수 있기 때문에 적절하게 축소해 주는것이 유리하다

2019 버전에서는 계속해서 증가하지 않도록 개선이 되었으나, 이하 버전에서는 관리가 필요

vlf 확인

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 사용자 DB + TempDB 이름을 임시 테이블로 저장, 변수 선언
IF OBJECT_ID(N'tempdb..#DB') IS NOT NULL
BEGIN
	DROP TABLE #DB
END

CREATE TABLE #DB (name sysname, dbid smallint)

INSERT INTO #DB (name, dbid)
SELECT name, dbid FROM master.dbo.sysdatabases
--WHERE name not in ('master', 'msdb', 'model')
GO

print  '*** TR Log VLFs'
DECLARE @dbname SYSNAME
DECLARE @min_dbid SMALLINT
DECLARE @SQL VARCHAR(8000)
DECLARE @productversion VARCHAR(128)
DECLARE @Majorversion INT

SET @productversion = convert(VARCHAR(128), SERVERPROPERTY('ProductVersion'))
SET @Majorversion = convert(INT, left(@productversion, charindex('.', @productversion) - 1))

IF OBJECT_ID(N'tempdb..#LOGFILECNT') IS NOT NULL 
BEGIN
     DROP TABLE #LOGFILECNT
END


CREATE TABLE #LOGFILECNT (
	seq INT identity
	,dbname SYSNAME
	,logcount INT
	)

SET @min_dbid = 0

WHILE (@min_dbid IS NOT NULL)
BEGIN
	SELECT @min_dbid = MIN(dbid)
	FROM #DB
	WHERE dbid > @min_dbid

	IF (@min_dbid IS NULL)
		BREAK

	SELECT @dbname = NAME
	FROM #DB
	WHERE dbid = @min_dbid

	INSERT INTO #LOGFILECNT (dbname)
	SELECT @dbname

	SET @SQL = 'USE [' + @dbname + ']; '

	IF @Majorversion < 11
	BEGIN
		SET @SQL = @SQL + '
  CREATE TABLE #T1 (c1 nvarchar(30), c2 nvarchar(30), c3 nvarchar(30), c4 nvarchar(30), c5 nvarchar(30), c6 nvarchar(30), c7 nvarchar(30))
  INSERT INTO #T1 EXEC (''DBCC LOGINFO'')
  UPDATE #LOGFILECNT SET logcount = (SELECT count(*) FROM #T1)
  WHERE dbname = ''' + @dbname + '''
  DROP TABLE #T1
  '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + '
  CREATE TABLE #T1 (c1 nvarchar(30), c2 nvarchar(30), c3 nvarchar(30), c4 nvarchar(30), c5 nvarchar(30), c6 nvarchar(30), c7 nvarchar(30), c8 nvarchar(30))
  INSERT INTO #T1 EXEC (''DBCC LOGINFO'')
  UPDATE #LOGFILECNT SET logcount = (SELECT count(*) FROM #T1)
  WHERE dbname = ''' + @dbname + '''
  DROP TABLE #T1
  '
	END

	EXEC (@SQL)
END

SELECT dbname
	,logcount
FROM #LOGFILECNT
ORDER BY seq

IF OBJECT_ID(N'tempdb..#LOGFILECNT') IS NOT NULL 
BEGIN
     DROP TABLE #LOGFILECNT
END
GO

vlf 축소

-- checkpoit 발생
CHECKPOINT

-- 버퍼 클린
DBCC DROPCLEANBUFFERS

-- Log Backup
BACKUP LOG TestDB TO  DISK = N'e:\BACKUP\TestDB_log.trn' WITH NOFORMAT, NOINIT,  NAME = N'TestDB Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

-- DB Shrink
USE TestDB
GO
DBCC SHRINKFILE (N'TestDB_log' , 0, TRUNCATEONLY)
GO



USE [master]
GO
ALTER DATABASE TestDB MODIFY FILE ( NAME = N'TestDB_log', SIZE = 10GB )
GO


dbcc loginfo(TestDB)

[waiteresource 정보]
RID. 잠금이 보유 또는 요청된 테이블 내의 단일 행을 식별합니다. RID는 RID: db_id:file_id:page_no:row_no로 표시됩니다. 예를 들면 RID: 6:1:20789:0과 같습니다.

OBJECT. 잠금이 보유 또는 요청된 테이블을 식별합니다. OBJECT는 OBJECT: db_id:object_id로 표시됩니다. 예를 들면 TAB: 6:2009058193과 같습니다.

KEY. 잠금이 보유 또는 요청된 인덱스 내의 키 범위를 식별합니다. KEY는 KEY: db_id:hobt_id (index key hash value)로 표시됩니다. 예를 들면 KEY: 6:72057594057457664 (350007a4d329)와 같습니다.

PAG. 잠금이 보유 또는 요청된 페이지 리소스를 식별합니다. PAG는 PAG: db_id:file_id:page_no로 표시됩니다. 예를 들면 PAG: 6:1:20789와 같습니다.

EXT. 익스텐트 구조를 식별합니다. EXT는 EXT: db_id:file_id:extent_no로 표시됩니다. 예를 들면 EXT: 6:1:9와 같습니다.

DB. 데이터베이스 잠금을 식별합니다. DB는 다음 방법 중 하나로 표시됩니다.

DB: db_id

DB: db_id[BULK-OP-DB]. 이 방법은 백업 데이터베이스에서 수행된 데이터베이스 잠금을 식별합니다.

DB: db_id[BULK-OP-LOG]. 이 방법은 특정 데이터베이스에 대해 백업 로그에서 수행된 잠금을 식별합니다.

APP. 응용 프로그램 리소스에서 수행된 잠금을 식별합니다. APP는 APP: lock_resource로 표시됩니다. 예를 들면 APP: Formf370f478과 같습니다

[확인 예시] 

waitresource = 11:24:3169121  -> 첫 번째 값은 DB 아이디. 
  
DBCC traceon (3604) WITH no_infomsgs          
DBCC PAGE (11,24,3169121) with  no_infomsgs, tableresults 

ParentObject Object Field VALUE 
PAGE HEADER: Page @0x000000095145E000 m_objId (AllocUnitId.idObj) 16431 
PAGE HEADER: Page @0x000000095145E000 m_indexId (AllocUnitId.idInd) 256 
PAGE HEADER: Page @0x000000095145E000 Metadata: AllocUnitId 7.20576E+16 
PAGE HEADER: Page @0x000000095145E000 Metadata: PartitionId 7.20576E+16 
PAGE HEADER: Page @0x000000095145E000 Metadata: IndexId 2                                   <---- 인덱스 아이디
PAGE HEADER: Page @0x000000095145E000 Metadata: ObjectId 132468542                     <---- 테이블 아이디 

object_id 값과 index 값을 알았으니 어느 테이블에 어느 객체인지 확인 하면 된다. 

select * from sys.objects where object_id = 132468542 
tblPlayer 132468542 NULL 1 0 U  USER_TABLE 2021-01-07 08:27:49.803 2021-01-07 08:27:49.823 0 0 0

select * from sys.indexes where object_id = 132468542 and index_id = 2
132468542 PK_tblPlayer 2 2 NONCLUSTERED 1 2 0 1 0 80 0 0 0 1 1 0 NULL NULL

결론 : tblPlayer 테이블의 PK_tblPlayer 인덱스에서 잠금이 발생

참고 : https://ceusee.tistory.com/140 [Bora.Choi]

일반적으로 동일한 SQL Server 인스턴스 내에서 파일 이동이 필요할 경우에 이 방법을 사용하며, 만약 다른 인스턴스나  다른 SQL Server로 이동한다면 분리 및 연결(sp_attach_db, sp_detach_db 시스템 저장 프로시져) 방법 또는 백업 및 복원 방법을 이용하면 됩니다.


[시나리오]
1. 데이터베이스명 : TestDB

2. 변경 전 파일 경로 
   1) C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDB.mdf
   2) C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDB_log.ldf

3. 변경 후 파일 경로
   1) D:\Data\TestDB.mdf  
   2) D:\Data\TestDB_log.ldf


[작업절차]

USE MASTER
GO

-- 1. 파일의 논리 이름을 확인합니다.
SELECT file_id, name, physical_name  FROM sys.master_files WHERE database_id = db_id('LAIGO');
/* LAIGO, LAIGO_LOG */

-- 2. 30초 후 모든 작업을 롤백하고 단일 사용자 모드로 전환합니다.
ALTER DATABASE LAIGO SET SINGLE_USER WITH ROLLBACK AFTER 30


-- 3. LAIGO 데이터베이스 오프라인 상태로 전환합니다.
ALTER DATABASE LAIGO SET OFFLINE


-- 4. mdf,ldf 파일 위치를 변경합니다.


-- 5. mdf 파일 위치 정보를 변경합니다.
ALTER DATABASE LAIGO MODIFY FILE (NAME=LAIGO, FILENAME='d:\data\laigo.mdf')


-- 6. ldf 파일 위치 정보를 변경합니다.
ALTER DATABASE LAIGO MODIFY FILE (NAME=LAIGO_LOG, FILENAME='d:\data\laigo_log.ldf')


-- 7. 데이버테이스를 온라인 상태로 전환합니다.
ALTER DATABASE LAIGO SET ONLINE


--8. 멀티 사용자 모드로 변경합니다.
ALTER DATABASE LAIGO SET MULTI_USER


-- 9. 정상적으로 변경되었는지 확인합니다.
SELECT  name, physical_name  AS CurrentLocation, state_desc  FROM  sys.master_files
WHERE  database_id  = DB_ID(N'LAIGO');

참고 : https://laigo.kr/76 [Lai Go's Blog]

USE master
GO


--1. tempdb의논리파일이름확인

SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')
GO


--2. ALTER DATABASE 사용하여파일위치변경

ALTER DATABASE tempdb MODIFY FILE(NAME = tempdev, FILENAME = 'd:\mssql\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE(NAME = templog, FILENAME = 'e:\mssql\templog.ldf')
GO


--3. Processor만큼파일분할및사이즈변경및파일사이즈, 증가옵션설정

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 20480KB , FILEGROWTH = 10240KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'd:\mssql\tempdev2.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'd:\mssql\tempdev3.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = N'd:\mssql\tempdev4.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 163840KB )
GO

--4.SQL Server 서비스 재시작.
 

--5.SQL Server 서비스가 시작된것을확인후 정상 이동 확인 

SELECT name, physical_name, state_desc 
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')

참고 : http://blog.naver.com/PostView.nhn?blogId=jevida&logNo=140149742633

--{SQL handle} 부분에 값을 입력하여 실행

(방법 1)
SELECT sql_handle AS Handle,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS Text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE sql_handle = --{SQL Handle}

(방법2)
select * from sys.dm_exec_query_plan({SQL Handle})
select * from sys.dm_exec_query_plan(0x0600030D550343000000000000000)

참고

https://rauofthameem.wordpress.com/2012/09/14/sql-query-that-gets-sql-statement-from-sqlhandle/

https://holictoweb.tistory.com/17

원인

SQL Server Agent 서비스 제어는 SQL Server Agent Roles 권한이 부여되어 있거나 또는 sysadmin role 이 부여되어 있어야 합니다.
test_srv 계정은 SQL Sever Agent Roles 또는 sysadmin role 이 없습니다. 

해결방법

방법1. 
SSMS - Security - Logins - test_srv - User Mapping - msdb - SQLAgentUserRole 부여(필요에 따라 SQLAgentReaderRole, SQLAgentOperatorRole 부여 가능)
단, 운영체제 외부 리소스를 사용하기 위해서는 별도의 Credentials, SQL Server Proxy Accounts 를 설정할 수 있음.

test_srv 계정에 view database 권한 필요

만약 개체 존재 여부를 먼저 체크 하는 로직을 실행 한다면 아래 권한 추가

방법2. 
sysadmin role 부여 (권장하지 않음)


참고 : https://docs.microsoft.com/ko-kr/sql/ssms/agent/sql-server-agent-fixed-database-roles?view=sql-server-2017

https://laigo.kr/497

문제

개체의 정의가 변경된다고 해서 이 개체를 참조하는 (스키마 바인딩 되지 않은) SP, UDF, VIEW의 메타데이터가 자동으로 갱신되지는 않습니다.

해결

--SQL Server 2000
참조한 개체는 Alter를 이용해서 다시 컴파일되어야 합니다. VIEW의 경우 sp_refreshview가 존재했지만, 다른 개체는 일일히 수작업을 해야 했었죠.

--SQL Server 2005
SQL Server 2005 SP2에서 sp_refreshsqlmodule 이 처음으로 소개되었습니다. 이 system sp를 이용하면 변경된 개체를 참조하는 SP, UDF, VIEW에 대해서 한번에 변경된 메타데이터를 갱신해 줍니다. 여전히 sp_refreshview도 사용 가능합니다.

실행 예)
EXEC sys.sp_refreshsqlmodule 'dbo.to_upper';

출처
http://msdn2.microsoft.com/ko-kr/library/bb326754.aspx
https://optimizer.tistory.com/entry/SQL2k5sprefreshsqlmodule-vs-sprefreshview