none
sysdatabases의 status값을 이용하여 db상태 확인하기 RRS feed

  • 일반 토론

  • 안녕하세요.

    정상DB의 상태를 확인하려면 다음과 같은 함수를 사용하면 됩니다.

      SELECT DATABASEPROPERTYEX('Northwind', 'Status')

    하지만, 원격 서버에 접속할 수 없는 상황이라면 위 함수를 수행할 수 없습니다.

    이 상황에서 단지 그 DB의 status값만 알고 있다면, 원격에서도 서버의 상태를 일일히 파악할 수 있습니다.

    status 값은 bit값으로 구성되어 있어서 상태파악을 위해서는 특별히 bitwise연산을 수행해야 합니다. 업무상 필요한데 하나씩 수행하기가 귀찮아서 sp로 만들었습니다.

    참조 

       BOL에서 sysdatabases

    이 글에 대한 의견은 언제든지 환영입니다.

    감사합니다. 한기환

    /*
     작성자 : 한기환
     작성일 : 2006-09-15
     내용
      master..sysdatabases의 status컬럼 내용을 가지고 그 DB의 상태 정보를 반환한다.
     입력값
      @input  master..sysdatabases의 status컬럼
      @input database name
     반환값
      각 DB상태를 컬럼으로 하여, 테이블 형태로 1 row를 반환한다.
      1이면 true, 0이면 false
     사용법
      exec dbo.usp_GetDBProperties
      exec dbo.usp_GetDBProperties 256
      exec dbo.usp_GetDBProperties 'master'
     기타
      특별히 status 256은 suspect를 의미한다.
    */
    CREATE PROC usp_GetDBProperties
    (@input nvarchar(64) = null)
    AS
    --declare @input int
    --set @input = 1073741840

    --모든 DB의 상태정보를 보고 싶은 경우
    IF (@input IS NULL)
    BEGIN
     SELECT
      status [status], [name], dbid
      , case when status &1 = 1 then 1 else 0 end as [autoclose]
      , case when status&4 = 4 then 1 else 0 end as [select into/bulkcopy]
      , case when status&8 = 8 then 1 else 0 end as [trunc. log on chkpt]
      , case when status&16 = 16 then 1 else 0 end as [torn page detection]
      , case when status&32 = 32 then 1 else 0 end as [loading]
      , case when status&64 = 64 then 1 else 0 end as [pre recovery]
      , case when status&128 = 128 then 1 else 0 end as [recovering]
      , case when status&256 = 256 then 1 else 0 end as [not recovered]
      , case when status&512 = 512 then 1 else 0 end as [offline]
      , case when status&1024 = 1024 then 1 else 0 end as [read only]
      , case when status&2048 = 2048 then 1 else 0 end as [dbo use only]
      , case when status&4096 = 4096 then 1 else 0 end as [single user]
      , case when status&32768 = 32768 then 1 else 0 end as [emergency mode]
      , case when status&4194304 = 4194304 then 1 else 0 end as [autoshrink]
      , case when status&1073741824 = 1073741824 then 1 else 0 end as [cleanly shutdown]
     FROM master..sysdatabases
    END
    ELSE IF(IsNumeric(@input) = 0) --문자(DB명)이 입력된 경우
    BEGIN
     SELECT
      status [status], [name], dbid
      , case when status &1 = 1 then 1 else 0 end as [autoclose]
      , case when status&4 = 4 then 1 else 0 end as [select into/bulkcopy]
      , case when status&8 = 8 then 1 else 0 end as [trunc. log on chkpt]
      , case when status&16 = 16 then 1 else 0 end as [torn page detection]
      , case when status&32 = 32 then 1 else 0 end as [loading]
      , case when status&64 = 64 then 1 else 0 end as [pre recovery]
      , case when status&128 = 128 then 1 else 0 end as [recovering]
      , case when status&256 = 256 then 1 else 0 end as [not recovered]
      , case when status&512 = 512 then 1 else 0 end as [offline]
      , case when status&1024 = 1024 then 1 else 0 end as [read only]
      , case when status&2048 = 2048 then 1 else 0 end as [dbo use only]
      , case when status&4096 = 4096 then 1 else 0 end as [single user]
      , case when status&32768 = 32768 then 1 else 0 end as [emergency mode]
      , case when status&4194304 = 4194304 then 1 else 0 end as [autoshrink]
      , case when status&1073741824 = 1073741824 then 1 else 0 end as [cleanly shutdown]
     FROM master..sysdatabases WHERE name = @input 
    END
    --숫자가 입력된 경우
    ELSE --IF(@input < 2147483647 and @input > 0)
    BEGIN
     SELECT
      @input [status], '' [name], '' dbid
      , case when @input&1 = 1 then 1 else 0 end as [autoclose]
      , case when @input&4 = 4 then 1 else 0 end as [select into/bulkcopy]
      , case when @input&8 = 8 then 1 else 0 end as [trunc. log on chkpt]
      , case when @input&16 = 16 then 1 else 0 end as [torn page detection]
      , case when @input&32 = 32 then 1 else 0 end as [loading]
      , case when @input&64 = 64 then 1 else 0 end as [pre recovery]
      , case when @input&128 = 128 then 1 else 0 end as [recovering]
      , case when @input&256 = 256 then 1 else 0 end as [not recovered]
      , case when @input&512 = 512 then 1 else 0 end as [offline]
      , case when @input&1024 = 1024 then 1 else 0 end as [read only]
      , case when @input&2048 = 2048 then 1 else 0 end as [dbo use only]
      , case when @input&4096 = 4096 then 1 else 0 end as [single user]
      , case when @input&32768 = 32768 then 1 else 0 end as [emergency mode]
      , case when @input&4194304 = 4194304 then 1 else 0 end as [autoshrink]
      , case when @input&1073741824 = 1073741824 then 1 else 0 end as [cleanly shutdown]
    END

    2006년 9월 15일 금요일 오전 6:52