특정 프로세스에서 리소스를 오랫동안 잠글 경우 다른 프로세스에서 잠긴 리소스를 접근하지 못하는 상황이 발생한다.

로직에 버그가 있어 트랜잭션을 시작하고 롤백이나 커밋을 하지 않게 될 경우 차단이 발생되어 최악의 경우 전체 서비스가 멎는 사태가 발생하게 된다.

로직에 문제가 없다고 하더라도 인덱스가 설정되어 있지 않거나 잘못 설정되어 있어 트랜잭션 수행 시간이 길어지면 차단이 발생할 수 있다.

데드락의 경우에는 SQL Server에서 발생 즉시 프로세스 하나를 희생해서 다른 프로세스에 영향을 주지 않지만 차단의 경우에는 원래 시간이 오래 걸리는 작업인지 문제가 있는 작업인지 SQL Server가 판단할 수 없어 문제가 더 심각해진다.

BlockMon은 이러한 차단을 모니터링하고 차단이 발생했을때 차단된 프로세스와 차단을 유발한 프로세스의 정보와 각기 실행한 SQL 구문을 기록할 수 있는 차단 모니터링 도구이다.

주요기능

모니터링할 SQL Server를 여러개 등록할 수 있고 차단이 발생했을때 차단 정보를 기록한다.

차단 정보는 로그파일에도 기록되며 기록되는 정보는 프로세스의 상세정보와 Input Buffer의 내용(마지막으로 실행한 SQL 구문)도 포함한다.

부가기능

모니터링하고 있는 서버를 더블클릭하면 해당 서버의 현재 실행중인 프로세스리스트를 출력한다.

차단된 프로세스의 경우 붉은 색의 폰트로 강조되고 Input Buffer의 내용과 차단을 유발한 프로세스를 kill 할 수 있는 기능도 있다.

프로그램 소스 살펴보기

BlockMon은 sp_who2 시스템저장프로시저를 사용해서 현재 프로세스정보를 조회한다.

특정 시간간격으로 현재 프로세스정보를 조회하고 차단된 프로세스가 있을 경우 DBCC InputBuffer 구문으로 해당 프로세스의 마지막 SQL 구문을 조회한 후 차단정보를 기록한다.

// 현재 프로세스 조회
public static DataTable GetProcess(SqlConnection conn)
{
    string query = "set language us_english\r\nexec sp_who2";

    using (SqlCommand cmd = new SqlCommand())
    using (SqlDataAdapter da = new SqlDataAdapter())
    {
        cmd.Connection = conn;
        cmd.CommandText = query;
        cmd.CommandType = CommandType.Text;

        da.SelectCommand = cmd;
        DataTable dt = new DataTable();
        da.Fill(dt);

        return dt;
    }
}

// 프로세스의 마지막 수행 SQL 구문
public static void GetInputBuffer(SqlConnection conn, Int16 spid, InputBuffer inputBuffer)
{
    string query = "dbcc inputbuffer(" + spid.ToString() + ")";

    using (SqlCommand cmd = new SqlCommand())
    using (SqlDataAdapter da = new SqlDataAdapter())
    using (DataTable dt = new DataTable())
    {
        cmd.Connection = conn;
        cmd.CommandText = query;
        cmd.CommandType = CommandType.Text;

        da.SelectCommand = cmd;
        da.Fill(dt);

        if (dt.Rows.Count != 1) return;

        inputBuffer.EventType = dt.Rows[0]["EventType"].ToString();
        inputBuffer.Parameters = Convert.ToInt16(dt.Rows[0]["Parameters"]);
        inputBuffer.EventInfo = dt.Rows[0]["EventInfo"].ToString();
    }
}

다운로드

실행파일

BlockMon.zip

소스

http://code.google.com/p/sqlserver-blocked-process-monitor/

수정이력

프로그램과 본 문서의 수정내역은 이 곳에 기록한다.

수정내역이 생기면 별도의 문서를 생성하지 않고 본 문서를 직접 수정하되 수정된 항목을 이 곳에 기록한다.

2009년 09월 18일 최초 생성

- 문서 끝 -

이올린에 북마크하기

소개

데이터베이스의 스키마를 비교하는 툴은 대부분 상용제품이다.

OpenDBDiff이라는 오픈소스프로젝트가 있는데 거의 대부분의 개체비교를 지원하지만 소소한 버그와 스크립트 비교가 어렵다는 등의 단점이 있다.

그 외에 Visual Studio 2008 Team System 버전에 포함된 Schema Compare 프로젝트의 경우에는 UI도 편하고 스크립트 비교도 시각적으로 표현해주는 등의 장점도 있지만 우선 Visual Studio 2008 Team System 라이센스가 있어야 하고 비교옵션을 조정해 원하는 부분만 비교하지 못하는 등의 단점이 있다.

보통 작은 규모의 데이터베이스에서는 특정개체(테이블, 뷰, 저장프로시저, 사용자정의함수 등)만 주로 사용을할 뿐만 아니라 스키마 비교를 할 때에도 부분적으로 수행하기를 원하기 때문에 상용제품이나 옵션이 복잡한 프로그램이 굳이 필요하지 않다.

이런 이유로 내게 꼭 필요한 기능만 있고 사용하기 간편한 스키마 비교 프로그램을 만들기로 하였고 흔한 이름이지만 DBDiff이라는 프로그램이 만들어지게 되었다.

DBDiff은 눈치 챘겠지만 OpenDBDiff과 Visual Studio 2008의 Schema Comapare를 많은 부분 참조했다.

(소스 수준 참조가 아니라 유저 인터페이스 수준 참조)

주요기능

지원하는 개체

DBDiff에서는 다음 개체의 스키마를 비교할 수 있다.

• Tables
  • Column Collation
  • Identity Options
  • Column Order
  • Default Value
  • File Group
• Constraints
  • Primary Key
  • Foreign Key
  • Unique Key
  • Check
• Indexes
  • Pad Index
  • Ignore Dup Key
  • Allow Row Locks
  • Allow Page Locks
  • Fill Factor
  • File Group
• Views
• Procedures
• Functions
• Extended Properties

지원 되지 않는 개체는 다음과 같다.

• Indexes
  • XML Indexes
• Table Types
• User Data Types
• CLR Objects
  • Assemblies
  • CLR User Defined Functions
  • CLR Stored Procedures
  • CLR Triggers
• Triggers
  • DML Triggers
  • DDL Triggers
• Synonyms
• Schemas
• File Groups
• Partition Functions
• Partition Schemas
• Users
• Roles

현재는 지원되지 않는 개체가 너무 많지만 조금씩 늘려나갈 것이고, 작은 규모의 데이터베이스에서는 현재 지원하는 개체만으로도 충분히 쓸모가 있으리라 생각한다.

스크립트 비교

개체 생성 스크립트를 기준으로 비교원본과 비교대상의 스크립트를 텍스트 비교할 수 있다.

텍스트 비교는 라인단위로 이뤄지고 동일/추가/삭제 기준으로 텍스트를 비교한다.

(변경된 라인은 삭제 및 추가로 인식한다.)

스크린 샷

원본과 대상 데이터베이스를 설정한 후 확인버튼을 클릭하면 스키마 비교 결과가 다음과 같이 출력된다.

변경된 개체가 있을 경우 원본과 대상 데이터베이스의 해당 개체 생성 스크립트를 텍스트 비교할 수 있다.

서로 차이가 있는 부분이 종류(추가/삭제)별로 다르게 하이라이트되어  쉽게 차이를 파악할 수 있다.

다운로드

DBDiff(20090914).zip

수정이력

프로그램과 본 문서의 수정내역은 이 곳에 기록한다.

수정내역이 생기면 별도의 문서를 생성하지 않고 본 문서를 직접 수정하되 수정된 항목을 이 곳에 기록한다.

- 문서 끝 -

이올린에 북마크하기

이 프로그램은 DDL(Data Definition Language)문 실행 기록을 추적하는 프로그램입니다.

예를 들어, 잘 돌아가던 프로그램이 어느날 갑자기 실행되지 않아 살펴보니 SP(Stored Procedure)가 변경되었는데, 개발자들은 모두 자기가 아니라고 발뺌하는 상황을 가정해 보죠.
트랜잭션 로그를 뒤져 보지 않는 이상 언제 누가 변경했고 이전 스크립트는 어땠는지 알 수 있는 방법은 없습니다.
이럴때 이 프로그램이 유용합니다.

물론 이 프로그램은 DDL 변경 기록을 조회하는 기능뿐이고 이 프로그램이 빛을 발하기 위해서는 DDL 트리거를 데이터베이스와 서버에 생성해야 합니다.

DDL 트리거에 관련된 내용은 “DDL 트리거” 포스트를 참고하세요.

그럼 프로그램에 대해서 간략히 살펴보도록 하겠습니다.

▶ 다운로드

DDL Tracker_090617.zip

SQL Server 2005 환경에서 개발 및 테스트했습니다.
프로그램 구동 환경은 Windows 2000이상, .NET Framework 3.5이상 설치되어 있어야 합니다.

▶ 주요 기능

이 프로그램의 주요 기능은 DDL 문 추적 및 버전 비교 기능과 DDL 트리거 생성 기능입니다.

1. DDL 트리거 생성

DDL 트리거는 서버 트리거와 데이터베이스 트리거를 생성할 수 있습니다.

서버 트리거의 경우 master 데이터베이스에 audit_ddl_event 테이블을 생성하고 ddl_login_events 수준의 서버 DDL 트리거를 생성합니다.
트리거 명은 trg_audit_ddl_login 입니다.

데이터베이스 트리거의 경우 해당 데이터베이스에 audit_ddl_event 테이블을 생성하고 ddl_database_level_events 수준의 데이터베이스 DDL 트리거를 생성합니다.
트리거 명은 trg_audit_ddl_event 입니다.

2. DDL 기록 조회

데이터베이스 DDL 트리거를 생성한 후 SSMS(SQL Server Management Studio)에서 DDL문을 실행하면 DDL 트리거에 의해서 audit_ddl_event테이블에 실행된 DDL문이 기록됩니다. 이 때 SQL Server 로그인정보와 접속한 컴퓨터 정보등이 모두 기록됩니다.

특히 저장 프로시저(PROCEDURE)와 사용자 정의 함수(FUNCTION)는 최종 버전과 작업 버전의 텍스트 비교기능도 있어 어떤 문장이 삭제되고 추가되었는지 한 눈에 살펴볼 수 있습니다.

▶ 사용 방법

간단한 사용방법을 살펴보겠습니다.

먼저 스크린샷을 보도록 하죠.

위 스크린샷은 프로그램의 전체기능을 보여주고 있습니다.

1. 먼저 Connect 메뉴를 클릭해서 SQL Server 연결 팝업윈도우를 엽니다.

Host 입력박스에 SQL Server의 IP(인스턴스가 있을 경우 IP\인스턴스명)와 Port를 입력하고,
SQL Server 인증을 원할 경우 Login 입력박스와 Password 입력박스에 로그인 정보를 입력합니다.
로그인 정보를 입력하지 않을 경우 윈도우 인증을 수행합니다.

Ok 버튼을 클릭해서 서버에 연결합니다.
서버에 성공적으로 연결될 경우 Databases 리스트박스에 데이터베이스 목록이 출력됩니다.

2. DDL 트리거 생성

이미 DDL 트리거가 생성되어 있을 경우(엄밀히 말하면 audit_ddl_event 테이블이 생성되어 있을 경우) 데이터베이스를 클릭하면 해당 데이터베이스의 DDL 실행기록이 검색되어 그리드에 출력됩니다.(master 데이터베이스의 경우 데이터베이스 DDL과 서버 DDL 실행기록이 모두 출력됩니다.)

하지만 DDL 트리거가 없을 경우 오류 메시지박스가 뜨는데 이럴 경우 해당 데이터베이스에 DDL 트리거를 생성하면 됩니다.
Databases 리스트박스에서 마우스 오른쪽 버튼을 클릭하면 다음과 같이 컨텍스트메뉴가 출력됩니다.

“Create DDL Trigger for server” 메뉴를 선택할 경우 master 데이터베이스에 audit_ddl_event 테이블을 생성하고 서버 DDL 트리거를 생성합니다. 이후 로그인을 생성, 수정, 삭제하면 master데이터베이스의 audit_ddl_event 테이블에 DDL 문이 기록됩니다.

“Create DDL Trigger for database” 메뉴를 선택할 경우 현재 선택된 데이터베이스에 audit_ddl_event 테이블을 생성하고 데이터베이스 DDL 트리거를 생성합니다. 이후 데이터베이스에서 개체(테이블, 인덱스, 프로시저, 함수, …)를 생성, 수정, 삭제하면 해당 데이터베이스의 audit_ddl_event 테이블에 DDL 문이 기록됩니다.

트리거 생성 시 오류가 발생하면 해당 데이터베이스의 audit_ddl_event 테이블과 DDL 트리거를 삭제하신 후 다시 시도하면 성공적으로 생성할 수 있습니다.

3. DDL 기록 조회

DDL 트리거를 생성한 후 DDL 문이 여러번 실행되면 데이터베이스를 클릭하여 해당 데이터베이스에서 수행된 DDL 실행기록을 조회할 수 있습니다.
조회된 결과가 많을 경우 object type(TABLE, INDEX, PROCEDURE, FUNCTION, LOGIN…)과 object name(TABLE NAME, INDEX NAME, PROCEDURE NAME, …)으로 필터링해서 볼 수 있습니다.

또한 검색결과가 많을 경우 한 페이지에 50개씩 페이징해 볼 수 있습니다.

4. DDL 문 버전 관리

object type이 PROCEDURE 또는 FUNCTION일 경우 최종 실행된 DDL 문과 특정 시점에 실행된 DDL 문의 텍스트를 비교할 수 있습니다. 텍스트 비교는 삭제되었거나 추가된 문장을 비교합니다.

이상으로 프로그램에 대해서 간단히 살펴보았습니다.

사용하면서 문제가 발생하거나 궁금한 점이 있으시면 이 포스트에 댓글 달아주세요.

이올린에 북마크하기

최근에 블로그를 둘 로 나누었습니다.
나를 아는 사람들에게 공개할 것인지 말 것인지를 기준으로 나누었죠.

공개하는 블로그에는 프로그래밍 관련 기술과 독서등의 비교적 담백하고 건조한 내용을 담을 것이고,
비공개 블로그(나를 모르는 사람들에게는 공개)에는 감성적인 내용을 주로 담을 생각입니다.

어쨌든 이로 인해 기존 블로그의 포스트를 둘로 나누어야할 상황이 발생했죠.
새로운 블로그를 만들고 기존 블로그의 내용을 백업한 후 복원했는데, 일일이 포스트를 지워나가는게 곤욕스럽더군요.

그래도 참을성을 갖고 정리를 마무리했습니다만,
작업이 마무리된 후 리뷰 카테고리를 어디에 둘 것인지 고민하게 되었고 결국에는 이 곳에서 저 곳으로 옮겨야할 상황이 발생했죠.

티스토리의 백업 기능에는 부분 백업 기능이 없습니다.(제가 알기로)
하지만 부분 복원 기능은 있죠.

백업 xml 파일에서 <blog> 노드의 migrational 어트리뷰트를 true로 설정하면 됩니다.
(false로 설정할 경우 기존 내용은 홀라당 날라가게 되니 조심해야 합니다.)

이러한 아이디어를 바탕으로 전체 백업 xml파일에서 특정 카테고리의 포스트만 추출해 별도의 부분 백업 xml파일을 만드는 방법을 생각해 보았습니다.

어렵지 않게 구현이 가능할 것 같아 나중에 또 다시 필요하게될 경우를 대비해(또한 저와 같은 고민을 하시는 분들을 위해)

프로그램을 만들어보기로 했습니다.

프로그램 소스코드가 간단하기 때문에 이 포스트에 직접 적어 놓습니다.
수정이 필요하신 분들은 해당 소스를 카피하셔서 C# 2.0 컴파일러로 직접 컴파일해 보세요.
바이너리만 필요하신 분들을 위해 실행파일도 첨부합니다.

* 소스코드

* 실행파일

ExtractPostFromTT.exe

* 꼭! .net framework 2.0 runtime 을 설치하신 후 실행하세요.

이올린에 북마크하기(0) 이올린에 추천하기(0)