보미네

SQL Server는 기동할 때 마다 모든 트랜잭션을 커밋 또는 롤백 시키기 위해서 복구(recovery) 작업을 수행한다. 이 복구 과정은 보통 수 초에서 수 분정도 소요되지만 한창 수정 작업이 진행되는 도중에 서버가 중단되었다면 복구 작업은 최소한 수정 작업이 진행된 시간 또는 로그 디바이스에 대한 경쟁 때문에 그 이상 걸릴 수 있다.

 SQL Server가 복구할 수 있는 충분한 시간을 주도록 하고 현재와 과거의 에러 로그 파일 및 NT의 에러 로그를 점검하여 어떤 문제가 발생하지는 않았는지 확인해야 한다. 만약 하드웨어 문제나 SQL Server의 버그가 발생하였다면 에러가 기록되어 있을 것이다.

 복구 작업이 CPU와 다스크를 사용하는지 알아보기 위해서 디스크의 작동상황을 알려주는 램프와 sysprocesses 활동 상황을 점검한다. 매우 드문 경우이긴 하지만 SQL Server가 데이터베이스를 정확하게 복구하지 못할 수도 있다.

 복구 과정을 확인하는 그 밖의 방법으로는 각 트랜잭션이 롤포워드(roll forward) 또는 롤백(rollback) 되었을 때 에러 로그에 기록하도록 트레이스 플래그(trace flag) 3412를 설정할 수도 있다.

 만약 데이터베이스가 복구되지 않고 백업한 것도 없다면 다음의 트레이스 플래그를 사용하여 복구 과정을 건너 뛸 수 있다. 단, 이렇게 하면 데이터베이스/데이터의 일관성이 깨질 수 있지만 다른 대안이 없는 경우에는 이 방법을 사용하되 반드시 필요한 객체를 BCP나 다른 툴을 사용하여 즉시 백업 받아야 한다.

3607 : 모든 데이터베이스의 자동 복구 생략
3608 : 마스터 데이터베이스를 제외한 모든 데이터베이스의 자동 복구 생략

그래도 데이터베이스를 사용할 수 없다면 – suspect라고 표시됨 – 다음의 명령을 실행하여 데이터베이스를 비상 모드(emergency mode)로 설정한다(먼저 수정작업을 허용해야 한다). 그 다음에 데이터베이스에 접속하여(SQL Server를 재기동할 필요 없음) 필요한 데이터를 백업 받으면 된다.

UPDATE master..sysdatabases SET status=-32768 WHERE name=’’

모든 방법이 실패하고 어떻게 해야 할 바를 모르겠다면 마이크로소프트 제품 지원 서비스(PSS)에 바로 전화하면 된다. 그들은 이와 같은 문제에 대비해서 1년 365일 24시간 내내 서비스를 제공하고 있으며 데이터 손실에 비하면 소요되는 비용은 무시 할 만 하다.

 use master
 
go
 

if object_id('sp_lock2') is not null
  drop proc sp_lock2
 go
 
 
 

create proc sp_lock2
as
 
set nocount on
set transaction isolation level read uncommitted

-- CTRL - T 모드로 변경 하세요
-- 도구 > 옵션 > 결과 텍스트 > 글꼴 > 굴림체
 -- 마스터 에서 돌리세요
-- 최초 김민석
 -- SQL Server MVP 2006~2009
 -- by minsouk@hotmail.com
-- 수정 하만철
 -- 20100624 세션정보 추가 김민석
-- 20110826 세션정보 수정 김민석
-- 20120919 varchar(max) 변경 김민석

/**** object view 생성을 위로 올렸습니다~!! ****/
 

if object_id('dbo.v_objlist') is not null
 drop view v_objlist
 
declare @viewheader varchar(max), @viewbody varchar(max)
 select @viewheader ='' , @viewbody =''
 if object_id('v_objlist') is not null
drop view v_objlist
 set @viewheader = 'create view dbo.v_objlist as '
 select
@viewbody = @viewbody + 'union all select db_id('''+quotename(name)+''') dbid
  , name collate database_default name
, id
  from '+quotename(name)+'.dbo.sysobjects '+char(13)+char(10)
 from master.dbo.sysdatabases
where dbid > 4
 select @viewbody = stuff(@viewbody, 1,10, '')
 exec (@viewheader + @viewbody)
 
print @viewheader + @viewbody
 

 
print N'######################################################################'
 print N'세션정보'
 print N'######################################################################'
 
DECLARE @VERSION INT
SELECT @VERSION = SUBSTRING(CAST(SERVERPROPERTY('PRODUCTVERSION') AS VARCHAR(100))
  , 1, CHARINDEX('.',CAST(SERVERPROPERTY('PRODUCTVERSION') AS VARCHAR(100)))-1)
 
 
 
IF @VERSION >= 9 BEGIN
SELECT SESSION_ID
  , CASE TRANSACTION_ISOLATION_LEVEL
WHEN 0 THEN '지정되지 않음'
WHEN 1 THEN '커밋되지 않은 읽기'
 WHEN 2 THEN '커밋된 읽기'
 WHEN 3 THEN '##반복 읽기##'
 WHEN 4 THEN '@@직렬화 가능@@'
 WHEN 5 THEN 'XX스냅숏XX' ELSE '?' END
, *
FROM SYS.DM_EXEC_SESSIONS
  WHERE SESSION_ID > 50
 END

print N'######################################################################'
 print N'락인포 어뷰징 확인 200개 ver 0.1'
 print N'######################################################################'
 
select top 200
  rsc_text
  , count(*) cnt
  , case req_status
when 1 then N'허가됨'
 when 2 then N'변환중'
 when 3 then N'대기중'
  end req_status
  , max(left(db_name(rsc_dbid),30)+case when len(c.name) > 30 then '...' else '' end) dbname
, max(left(c.name,30)+case when len(c.name) > 30 then '...' else '' end) objname
, max(rsc_indid) IndId
  , max(case rsc_type
when 1 then null
  when 2 then 'DB'
  when 3 then 'File'
  when 4 then 'Index'
  when 5 then 'Table'
  when 6 then 'Page'
  when 7 then 'Key'
  when 8 then 'Extent'
  when 9 then 'RID'
  when 10 then 'App'
  end) Type
  , max(case req_mode --(0,3,6,7,8,9)
  when 0 then null
  when 1 then N'Sch-S:스키마 안전성'
 when 2 then N'Sch-M:스키마 수정'
 when 3 then N'S:공유'
 when 4 then N'U:업데이트'
 when 5 then N'X:단독'
 when 6 then N'IS:내재 공유'
 when 7 then N'IU:내재 업데이트'
 when 8 then N'IX:내재 단독'
 when 9 then N'SIU:공유 내재 업데이트'
 when 10 then N'SIX:공유 내재 단독'
 when 11 then N'UIX:업데이트 내재 단독'
 when 12 then N'BU:대량 작업'
 when 13 then N'RangeS_S:공유 키 범위 및 공유 리소스'
 when 14 then N'RangeS_U:공유 키 범위 및 업데이트 리소스'
 when 15 then N'RangeI_N:삽입 키 범위 및 Null 리소스'
 when 16 then N'RangeI_S:RangeI_N 및 S 잠금의 겹침으로 만들어진 키 범위 변환'
 when 17 then N'RangeI_U:RangeI_N 및 U 잠금의 겹침으로 만들어진 키 범위 변환'
 when 18 then N'RangeI_X:RangeI_N 및 X 잠금의 겹침으로 만들어진 키 범위 변환'
 when 19 then N'RangeX_S:RangeI_N 및 RangeS_S. 잠금의 겹침으로 만들어진 키 범위 변환'
 when 20 then N'RangeX_U:RangeI_N 및 RangeS_U 잠금의 겹침으로 만들어진 키 범위 변환'
 when 21 then N'RangeX_X:단독 키 범위 및 단독 리소스'
  end) Mode
  , max(case req_ownertype
when 1 then N'트랜잭션'
 when 2 then N'커서'
 when 3 then N'세션'
 when 4 then N'ExSession'
  end) req_ownertype
 from
master.dbo.syslockinfo a with (nolock)
  left join master.dbo.v_objlist c with (nolock)
  on c.dbid = a.rsc_dbid
  and c.id = a.rsc_objid
 where
req_spid <> @@spid
 -- and req_status = 1
  and rsc_type <> 2
group by req_status, rsc_text
 order by req_status, count(*) desc
 

/**** N' 추가했습니다~!! ****/
 print N'######################################################################'
 print N'헤드블럭만 보기 by minsouk@hotmail.com ver 0.1'
 print N'######################################################################'
 
select *
 from master.dbo.sysprocesses
where blocked = 0
and spid in (select blocked from master.dbo.sysprocesses where blocked <> 0)
 
print N'######################################################################'
 print N'헤드블럭 쿼리보기 by minsouk@hotmail.com ver 0.1'
 print N'######################################################################'
 
 
 
/**** adhoc 경우 dbid, objectid 가 null 이라 dbname 보여주기위해 dbid 추가 했습니다!! ****/
 declare cur_headblock cursor fast_forward
for
select spid, sql_handle, dbid
  from master.dbo.sysprocesses
where blocked = 0
and spid in (select blocked from master.dbo.sysprocesses where blocked <> 0)
 declare @spid varchar(6)
 declare @dbid int
 declare @handle varbinary(64);
 open cur_headblock
 fetch next from cur_headblock into @spid, @handle, @dbid
 while (@@fetch_status != -1)
 begin
  print '#########################'
  print 'dbcc inputbuffer for spid ' + @spid
print '#########################'
 
 /***** adhoc, proc 구분하고 objname 보게 바꿔봤습니다~!! ****/
  select case when fn.dbid is null then 'AdHoc' else 'Proc' end as qry_type, db_name(@dbid)
dbname, vo.name as objname, [text]
from ::fn_get_sql(@handle) fn
  left outer join v_objlist vo on fn.dbid = vo.dbid and fn.objectid = vo.id
  exec ('dbcc inputbuffer (' + @spid + ')')
  fetch next from cur_headblock into @spid, @handle, @dbid
 end
 deallocate cur_headblock
 
print N'######################################################################'
 print N'락트리 보기 by minsouk@hotmail.com ver 0.2'
 print N'######################################################################'
 
if object_id ('tempdb..#tbl_sysprocesses') is not null
  drop table #tbl_sysprocesses

create table #tbl_sysprocesses
 (
depth int
  , tree varchar(7000)
  , spid int
  , blocked int
  --, sql_handle varbinary(64)
 )
 
insert into #tbl_sysprocesses (depth, tree, spid, blocked)
select 0, cast(spid as varchar(100)) spid , spid, blocked
 from master.dbo.sysprocesses
where blocked = 0
and spid in (select blocked from master.dbo.sysprocesses where blocked <> 0)
 
declare @max_depth int
 set @max_depth = 5
 
while (1=1)
 begin
insert into #tbl_sysprocesses (depth, tree, spid, blocked)
select a.depth + 1 depth , a.tree + ' > ' +cast(b.spid as varchar(8000)) tree , b.spid, b.blocked
  from #tbl_sysprocesses a
  inner join master.dbo.sysprocesses b
  on a.spid = b.blocked
  where depth in (select max(depth) from #tbl_sysprocesses)
  and b.spid <> b.blocked
  if @@rowcount = 0 break
  set @max_depth = @max_depth - 1
  if @max_depth <= 1 break
end

declare @cnt varchar(10)
 select @cnt = cast(cnt as varchar(10)) from ( select count(*) cnt from sysprocesses where blocked <> 0 ) a
 
print N'######################################################################'
 print N'블럭카운트 : '+@cnt
print N'######################################################################'
 
select convert(char(10), cast((b.waittime / 1000) * 1.1574074074074073E-5 as datetime) , 108) as[hh:mm:ss]
  , left(a.tree, 40)+case when len(a.tree) > 40 then '...' else '' end locktree, b.*
 from #tbl_sysprocesses a
  inner join master.dbo.sysprocesses b
  on a.spid = b.spid
order by tree
 

print N'######################################################################'
 print N'######################################################################'
 print N'######################################################################'
 print N'락인포 보기 by minsouk@hotmail.com ver 0.5'
 print N'######################################################################'
 print N'######################################################################'
 print N'######################################################################'
 print N''
 
/*
 if object_id ('dbo.usp_create_v_objlist') is not null
 drop proc dbo.usp_create_v_objlist
 */
 
--exec dbo.usp_create_v_objlist
 
--set rowcount 200
 
print N'######################################################################'
 print N'락인포 허가 200개 exclude rsc_type db by minsouk@hotmail.com ver 0.6'
 print N'######################################################################'
 
select top 200
  req_spid spid
  , left(db_name(rsc_dbid),30)+case when len(c.name) > 30 then '...' else '' end dbname
, left(c.name,30)+case when len(c.name) > 30 then '...' else '' end objname
, rsc_indid IndId
  , case rsc_type
when 1 then null
  when 2 then 'DB'
  when 3 then 'File'
  when 4 then 'Index'
  when 5 then 'Table'
  when 6 then 'Page'
  when 7 then 'Key'
  when 8 then 'Extent'
  when 9 then 'RID'
  when 10 then 'App'
  end Type
  , rsc_type
  , rsc_text
  , case req_mode --(0,3,6,7,8,9)
  when 0 then null
  when 1 then N'Sch-S:스키마 안전성'
 when 2 then N'Sch-M:스키마 수정'
 when 3 then N'S:공유'
 when 4 then N'U:업데이트'
 when 5 then N'X:단독'
 when 6 then N'IS:내재 공유'
 when 7 then N'IU:내재 업데이트'
 when 8 then N'IX:내재 단독'
 when 9 then N'SIU:공유 내재 업데이트'
 when 10 then N'SIX:공유 내재 단독'
 when 11 then N'UIX:업데이트 내재 단독'
 when 12 then N'BU:대량 작업'
 when 13 then N'RangeS_S:공유 키 범위 및 공유 리소스'
 when 14 then N'RangeS_U:공유 키 범위 및 업데이트 리소스'
 when 15 then N'RangeI_N:삽입 키 범위 및 Null 리소스'
 when 16 then N'RangeI_S:RangeI_N 및 S 잠금의 겹침으로 만들어진 키 범위 변환'
 when 17 then N'RangeI_U:RangeI_N 및 U 잠금의 겹침으로 만들어진 키 범위 변환'
 when 18 then N'RangeI_X:RangeI_N 및 X 잠금의 겹침으로 만들어진 키 범위 변환'
 when 19 then N'RangeX_S:RangeI_N 및 RangeS_S. 잠금의 겹침으로 만들어진 키 범위 변환'
 when 20 then N'RangeX_U:RangeI_N 및 RangeS_U 잠금의 겹침으로 만들어진 키 범위 변환'
 when 21 then N'RangeX_X:단독 키 범위 및 단독 리소스'
  end Mode
  , req_mode
  , case req_status
when 1 then N'허가됨'
 when 2 then N'변환중'
 when 3 then N'대기중'
  end req_status
  , req_refcnt
, req_lifetime
, req_ecid [req_ecid (isParallel)]
  , case req_ownertype
when 1 then N'트랜잭션'
 when 2 then N'커서'
 when 3 then N'세션'
 when 4 then N'ExSession'
  end req_ownertype
  , req_transactionID
  , req_transactionUOW [req_transactionUOW (isDTC)]
 from
master.dbo.syslockinfo a with (nolock)
  left join master.dbo.v_objlist c with (nolock)
  on c.dbid = a.rsc_dbid
  and c.id = a.rsc_objid
 where
req_spid <> @@spid
  and req_status = 1
  and rsc_type <> 2
order by
spid -- 정렬
 
print N'######################################################################'
 print N'락인포 변환 200개 by minsouk@hotmail.com ver 0.5'
 print N'######################################################################'
 
select top 200
  req_spid spid
  , left(db_name(rsc_dbid),30)+case when len(c.name) > 30 then '...' else '' end dbname
, left(c.name,30)+case when len(c.name) > 30 then '...' else '' end objname
, rsc_indid IndId
  , case rsc_type
when 1 then null
  when 2 then 'DB'
  when 3 then 'File'
  when 4 then 'Index'
  when 5 then 'Table'
  when 6 then 'Page'
  when 7 then 'Key'
  when 8 then 'Extent'
  when 9 then 'RID'
  when 10 then 'App'
  end Type
  , rsc_type
  , rsc_text
  , case req_mode --(0,3,6,7,8,9)
  when 0 then null
  when 1 then N'Sch-S:스키마 안전성'
 when 2 then N'Sch-M:스키마 수정'
 when 3 then N'S:공유'
 when 4 then N'U:업데이트'
 when 5 then N'X:단독'
 when 6 then N'IS:내재 공유'
 when 7 then N'IU:내재 업데이트'
 when 8 then N'IX:내재 단독'
 when 9 then N'SIU:공유 내재 업데이트'
 when 10 then N'SIX:공유 내재 단독'
 when 11 then N'UIX:업데이트 내재 단독'
 when 12 then N'BU:대량 작업'
 when 13 then N'RangeS_S:공유 키 범위 및 공유 리소스'
 when 14 then N'RangeS_U:공유 키 범위 및 업데이트 리소스'
 when 15 then N'RangeI_N:삽입 키 범위 및 Null 리소스'
 when 16 then N'RangeI_S:RangeI_N 및 S 잠금의 겹침으로 만들어진 키 범위 변환'
 when 17 then N'RangeI_U:RangeI_N 및 U 잠금의 겹침으로 만들어진 키 범위 변환'
 when 18 then N'RangeI_X:RangeI_N 및 X 잠금의 겹침으로 만들어진 키 범위 변환'
 when 19 then N'RangeX_S:RangeI_N 및 RangeS_S. 잠금의 겹침으로 만들어진 키 범위 변환'
 when 20 then N'RangeX_U:RangeI_N 및 RangeS_U 잠금의 겹침으로 만들어진 키 범위 변환'
 when 21 then N'RangeX_X:단독 키 범위 및 단독 리소스'
  end Mode
  , req_mode
  , case req_status
when 1 then N'허가됨'
 when 2 then N'변환중'
 when 3 then N'대기중'
  end req_status
  , req_refcnt
, req_lifetime
, req_ecid [req_ecid (isParallel)]
  , case req_ownertype
when 1 then N'트랜잭션'
 when 2 then N'커서'
 when 3 then N'세션'
 when 4 then N'ExSession'
  end req_ownertype
  , req_transactionID
  , req_transactionUOW [req_transactionUOW (isDTC)]
 from
master.dbo.syslockinfo a with (nolock)
  left join master.dbo.v_objlist c with (nolock)
  on c.dbid = a.rsc_dbid
  and c.id = a.rsc_objid
 where
req_spid <> @@spid and req_status = 2
 order by
spid -- 정렬
 
print N'######################################################################'
 print N'락인포 대기 200개 by minsouk@hotmail.com ver 0.5'
 print N'######################################################################'
 
select top 200
  req_spid spid
  , left(db_name(rsc_dbid),30)+case when len(c.name) > 30 then '...' else '' end dbname
, left(c.name,30)+case when len(c.name) > 30 then '...' else '' end objname
, rsc_indid IndId
  , case rsc_type
when 1 then null
  when 2 then 'DB'
  when 3 then 'File'
  when 4 then 'Index'
  when 5 then 'Table'
  when 6 then 'Page'
  when 7 then 'Key'
  when 8 then 'Extent'
  when 9 then 'RID'
  when 10 then 'App'
  end Type
  , rsc_type
  , rsc_text
  , case req_mode --(0,3,6,7,8,9)
  when 0 then null
  when 1 then N'Sch-S:스키마 안전성'
 when 2 then N'Sch-M:스키마 수정'
 when 3 then N'S:공유'
 when 4 then N'U:업데이트'
 when 5 then N'X:단독'
 when 6 then N'IS:내재 공유'
 when 7 then N'IU:내재 업데이트'
 when 8 then N'IX:내재 단독'
 when 9 then N'SIU:공유 내재 업데이트'
 when 10 then N'SIX:공유 내재 단독'
 when 11 then N'UIX:업데이트 내재 단독'
 when 12 then N'BU:대량 작업'
 when 13 then N'RangeS_S:공유 키 범위 및 공유 리소스'
 when 14 then N'RangeS_U:공유 키 범위 및 업데이트 리소스'
 when 15 then N'RangeI_N:삽입 키 범위 및 Null 리소스'
 when 16 then N'RangeI_S:RangeI_N 및 S 잠금의 겹침으로 만들어진 키 범위 변환'
 when 17 then N'RangeI_U:RangeI_N 및 U 잠금의 겹침으로 만들어진 키 범위 변환'
 when 18 then N'RangeI_X:RangeI_N 및 X 잠금의 겹침으로 만들어진 키 범위 변환'
 when 19 then N'RangeX_S:RangeI_N 및 RangeS_S. 잠금의 겹침으로 만들어진 키 범위 변환'
 when 20 then N'RangeX_U:RangeI_N 및 RangeS_U 잠금의 겹침으로 만들어진 키 범위 변환'
 when 21 then N'RangeX_X:단독 키 범위 및 단독 리소스'
  end Mode
  , req_mode
  , case req_status
when 1 then N'허가됨'
 when 2 then N'변환중'
 when 3 then N'대기중'
  end req_status
  , req_refcnt
, req_lifetime
, req_ecid [req_ecid (isParallel)]
  , case req_ownertype
when 1 then N'트랜잭션'
 when 2 then N'커서'
 when 3 then N'세션'
 when 4 then N'ExSession'
  end req_ownertype
  , req_transactionID
  , req_transactionUOW [req_transactionUOW (isDTC)]
 from
master.dbo.syslockinfo a with (nolock)
  left join master.dbo.v_objlist c with (nolock)
  on c.dbid = a.rsc_dbid
  and c.id = a.rsc_objid
 where
req_spid <> @@spid
  and req_status = 3
 order by
spid -- 정렬
 
set rowcount 0
 
print N'######################################################################'
 print N'블럭되는 쿼리보기 sql_handle 별 50개 by minsouk@hotmail.com ver 0.2'
 print N'######################################################################'
 
declare cur_blocked cursor fast_forward
for
select top 50 max(spid) spid, sql_handle, max(dbid) dbid from sysprocesses where blocked <> 0
group by sql_handle
 --declare @spid varchar(6)
 --declare @handle varbinary(64)
 open cur_blocked
 fetch next from cur_blocked into @spid, @handle, @dbid
 while (@@fetch_status != -1)
 begin
  print '|||||||||||||||||||||||||'
  print 'dbcc inputbuffer for spid ' + @spid
  print '|||||||||||||||||||||||||'
  select case when fn.dbid is null then 'AdHoc' else 'Proc' end as qry_type, db_name(@dbid)
dbname, vo.name as objname, [text]
from ::fn_get_sql(@handle) fn
  left outer join v_objlist vo on fn.dbid = vo.dbid and fn.objectid = vo.id
  exec ('dbcc inputbuffer (' + @spid + ')')
  fetch next from cur_blocked into @spid, @handle, @dbid
 end
 deallocate cur_blocked
 

go
 

exec dbo.sp_lock2
 
go