Data Science Lab

데이터에 대한 이해와 spill in tempdb Version : SQL Server 2005, 2008, 2008R2, 2012 상황은 tempdb 데이터가 30GB에서 120GB로 증가 하였지만 tempdb의 로그 파일은 1GB밖에 증가하지 않았다. 이런 상황이 가능 할까? Tempdb에 대해 고려해야 할 때 tempdb에 로깅하는 것은 매우 효율적이다. tempdb는 다른 데이터베이스처럼 롤백 할 수 있도록 전과 후의 이미지를 필요로 한다. 그래서 업데이트 하기 전 성공적인 롤백이 가능하도록 이미지를 기록 한다. 위의 질문에 대한 답은 tempdb에 발생하는 정렬 유출(sort spill)을 고려하여 설명 할 수 있다. 다음 스크립트는 수 백만 행의 결과를 정렬한다. SELECT S.*, P.* ..

로그 파일이 많으면 왜 안 좋은가 Version : SQL Server 2005, 2008, 2008R2, 2012 SQL Server에서 발생하는 트랜잭션은 트랜잭션 로그 라는 로그 파일에 기록 된다. 기본 설정으로 SQL Server를 설치 하였다면 확장자는 ldf로 표시 되며 하나의 로그 파일이 생성된다. 트랜잭션 로그 파일은 순환 파일이다. 한 개의 물리 파일에 4개의 가상 로그 파일이 있다고 가정 하였을 때 데이터베이스가 생성될 때 물리 로그파일의 시작 부분에서 논리로그 파일이 시작 된다. 새 로그레코드는 논리 로그의 끝 부분에 추가되며 물리 로그의 끝 방향으로 확장 된다. 로그에 물리 로그 파일이 여러 개 있으면 논리 로그는 모든 물리로그 파일을 거친 후 첫 번째 물리로그 파일의 시작 부분으로..

트랜잭션 백업 실패와 전체 백업 성공 그리고 대처 방안 Version : SQL Server 2005, 2008, 2008R2, 2012 SQL Server에서 트랜잭션 로그는 SQL Server에서 발생하는 모든 행동을 기록 한다. 다음 사례는 트랜잭션 백업이 실패하지만 전체 백업은 성공하는 경우에 대해서 알아 본다. 다음과 같이 트랜잭션 로그가 손상되어 백업이 실패하였다. 하지만 전체 백업은 성공하였으며 DBCC CHECKDB 도 성공하였다. 트랜잭션 백업이 실패하였지만 전체 백업이 성공한 이유는 트랜잭션 로그의 아키텍처를 살펴보면 이해 할 수 있다. 트랜잭션 로그 물리 아키텍처 : http://sqlmvp.kr/140187358202 트랜잭션 로그 백업은 마지막 트랜잭션 로그 백업 이후에 생성된 트..

Fast recovery 와 로그 잠금 Version : SQL Server 2005, 2008, 2008R2, 2012 SQL Server 2005 이상의 Enterprise 버전에서는 fast recovery 기능이 도입 되었다. fast recovery는 복구 단계에서도 데이터베이스를 사용 할 수 있다. Fast recovery는 장애가 발생하였을 때 이전의 커밋되지 않은 트랜잭션들에 대해서는 장애 발생 이전으로 잠금을 획득하여 트랜잭션이 롤백 되는 동안 자신의 잠금을 사용자 간섭으로부터 보호 한다. 스토리지 엔진은 응급 복구 시 두 개의 패스로 수행 한다. 첫 번째 패스는(REDO) 로그 레코드를 읽고 두 번째 패스(UNDO)는 잠금을 확인 하고 실제 잠금을 획득 한다. Fast recovery ..

고스트 클린업 Version : SQL Server 2005, 2008, 2008R2, 2012 인덱스가 있는 행을 삭제 할 경우 인덱스의 효율성을 높이기 위해 인덱스의 리프-레벨에 삭제 하려는 행에 대해서는 우선 고스트 레코드(Ghost Record)로 마크한 다음 주기적인 삭제 작업이 실행 된다. GhostRecord(인덱스에서 행 삭제시발생) : http://sqlmvp.kr/140164866348 고스트 클린업 작업은 빈 데이터 할당 또는 인덱스 페이지 작업을 피하기 위해 기록(single record)을 남길 수 있다. 이러한 작업들은 백그라운드에서 실행된다. 고스트 클린업 작업은 삭제 트랜잭션이 완료 될 때까지 물리적으로 삭제 할 수 없다. 왜냐하면 트랜잭션 잠금이 트랜잭션 커밋이 될 때까지 ..

페이지 분할이 발생 하였을 때 롤백을 하면 어떻게 될까? Version : SQL Server 2005, 2008, 2008R2, 2012 페이지 분할이 발생하였을 때 롤백을 시도할 경우 분할된 페이지도 롤백이 될까? 다음 실습을 통해서 알아 보자. 실습을 위해 데이터베이스와 테이블을 생성한다. 페이지 분할을 발생 시킬 수 있는 클러스터 인덱스를 생성한다. USE MASTER; GO CREATE DATABASE pagesplittest; GO USE pagesplittest; GO CREATE TABLE t1 (c1 INT, c2 VARCHAR (1000)); GO CREATE CLUSTERED INDEX t1c1 ON t1 (c1); GO 페이지 분할을 위해 데이터를 입력 한다. 분할을 할 수 있도록 ..

DBCC WRITEPAGE DBCC 명령을 사용한 데이터 파괴하기 Version : SQL Server 2005, 2008, 2008R2, 2012 SQL Server에서 문서화 되지 않은 기능 DBCC WRITEPAGE 기능에 대해서 알아 본다. 이 명령을 사용한 문제는 전적으로 자신에게 있으니 사용시 주의하길 바란다. DBCC WRITEPAGE는 시스템 관리자 권한이 있을 경우 데이터베이스의 있는 모든 페이지의 바이트를 변경 할 수 있다. DBCC WRITEPAGE는 작업에 대해 트랜잭션을 기록하지 않는다. 이는 곧 롤백이 불가능하다는 것으로 해석 할 수 있다. 또한 시스템 관리자 권한이 있는 모든 사람이 사용할 수 있으므로 막을 수 있는 방법이 없다. 실습을 통해서 DBCC WRITEPAGE 사용방..

SQL Server Backup Error 3023 Version : SQL Server 2005, 2008, 2008R2, 2012 SQL Server에서 백업을 실행 하였는데 다음과 같은 오류가 발생 하였다. 에러로그에는 다음과 같은 오류 메시지가 기록되었다. Error 3023 Severity Level 16 Message Text Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. 오류 내용을 보..

Delete 작업과 페이지 offset 변화 Version : SQL Server 2005, 2008, 2008R2, 2012 SQL Server에서 테이블의 레코드를 삭제하는 경우 어떻게 처리되는지 알아본다. 실습을 위해 4개의 레코드가 8K의 페이지에 있는 테이블을 만든다. -- Create a simple table where 4 records fit onto 1 page CREATE TABLE TestTable ( Col1 INT IDENTITY(1, 1), Col2 CHAR(2000) ) GO 데이터가 한 페이지에 들어가도록 입력한다. -- Insert 4 records INSERT INTO TestTable VALUES ( REPLICATE('1', 2000) ), ( REPLICATE('2'..

트랜잭션 로그 및 LSN을 이용한 삭제된 데이터 복구 Version : SQL Server 2005, 2008, 2008R2, 2012 SQL Server에서 일어나는 모든 일은 트랜잭션 로그에 기록 된다. 지난 시간에 트랜잭션 로그를 활용하여 누가 어떤 명령을 실행하였는지 찾는 방법에 대해서 알아 보았다. SQL Server에서 Drop 및 Delete 사용자 찾기 : http://sqlmvp.kr/140202164558 이번 시간에는 트랜잭션 로그 및 LSN을 사용하여 삭제된 데이터를 복구하는 방법에 대해서 알아 본다. SQL Server 트랜잭션의 모든 레코드에는 LSN(로그 시퀀스 번호)으로 고유하게 식별된다. LSN은 numeric(25.0)의 값을 가지며 변경이 발생한 순서에 따라 번호가 매겨..