MSSQL DB 관리 쿼리들..

-- ■■DBA의 정기적/일상적 업무
-- 1. 백업과 로그의 관리
-- 2. 인덱스 리빌드
-- 3. DBCC (DB 오류점검)
-- 4. 새로운 사용자 등록, 권한 부여
-- 5. UPDATE STATISTICS - 분포 페이지의 갱신
-- 6. 불필요한 로그 파일들의 삭제

-- ■■DB 정보 확인
EXEC SP_HELPDB     -- 디비 관련 정보 확인 SP
SELECT DATABASEPROPERTYEX('DB명', 'RECOVERY') -- 해당 디비 복구 모델 확인
ALTER DATABASE [DB명] SET RECOVERY SIMPLE  -- 해당 디비 복구 모델 변경 (SIMPLE / FULL)
EXEC SP_HELPINDEX [테이블명]    -- 해당 테이블 인덱스 정보 확인
SELECT @@CONNECTIONS    -- 커넥션 수 확인?
EXEC SP_WHO      -- 커넥션 정보 확인
DBCC LOGINFO      -- 모든 로그 크기 및 정보 확인
EXEC SP_HELPCONSTRAINT [테이블명]   --해당 테이블의 제약 정보 확인

-- ■■DB 의 존재 유무에 따라 삭제하는 구문
IF EXISTS ( SELECT name
  FROM master.dbo.sysdatabases
  WHERE name = 'DB명' )
 BEGIN
 ALTER DATABASE [DB명] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
 DROP DATABASE [DB명]
 END
GO      -- 디비 삭제

  --// 로그 추가 및 [옵션]실제 최소 크기 및 최대치(MAXSIZE)를 변경한다. (FILEGROWTH : 증가치 설정)
ALTER DATABASE [DB명]
ADD LOG FILE(
 NAME     = '[FILENAME]',
 FILENAME   = '[PATH]\[FILENAME]',
 SIZE      = 10MB,
 MAXSIZE   = 20MB,
 FILEGROWTH = 10%  
 )

ALTER DATABASE Database_Name
MODIFY FILE (
NAME = Database_Name_LOG,
SIZE = 2 MB )
GO



-- ■■DB 테이블 컬럼 모두 보기
SELECT TABLE_NAME,
 COLUMN_NAME,columnproperty(OBJECT_ID(TABLE_NAME),
 COLUMN_NAME, 'IsIdentity') as IsIdentity,
 ORDINAL_POSITION, IS_NULLABLE, DATA_TYPE,
 CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, COLLATION_NAME,
 CHARACTER_SET_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
-- WHERE TABLE_NAME = '테이블명'
ORDER BY TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME

-- ■■함수 관련
ROUND(숫자, 몇번째자리까지남길건지, 음수이면반올림/나머지버림)

--관리도구에서 성능면에가면 시스템 모니터를 할 수 있음
-- ■■프로파일러 관련
DBCC DROPCLEANBUFFERS    -- 프로파일러 쿼리 듀레이션 체크 중 캐시 삭제

-- ■■SET OPTION 관련
SET NOCOUNT ON     -- 쿼리실행시 나오는 카운트수 끔
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- 락없이 읽기 가능 옵션
ALTER DATABASE [DB명] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE -- DB 모드 변경
ALTER DATABASE [DB명] SET  MULTI_USER    -- 기본 모드

-- ■■TABLE COLUMN 추가 수정 삭제
ALTER TABLE [테이블명] ADD [추가할컬럼명] [속성]
-- 추가할 때는 NULLABLE이나 DEFAULT 값이 정해져있는 것만 가능함
ALTER TABLE [테이블명] ALTER COLUMN [컬렴명] [변경할 속성]
EXEC SP_RENAME '테이블명.[수정할컬럼명]', '수정될이름', 'COLUMN'
ALTER TABLE [테이블명] DROP COLUMN [삭제할컬럼명]




-- ■■INDEX 추가 :: INCLUDE OPTION은 2000에는 적용 안됨,
--  :: INDEX WIZARD RECOMMEND INDEX -> MILES_LOG 은 안해도됨
CREATE NONCLUSTERED INDEX IX01_MEM_INF
 ON [dbo].[MEM_INF] ([MEM_SQUAD])
 INCLUDE ([MEM_CD])

CREATE NONCLUSTERED INDEX IX02_MEM_INF
 ON [dbo].[MEM_INF] ([COMPANY_INF_CD])
 INCLUDE ([MEM_CD],[MEM_SRL_NUM],[MEM_CLASS],[MEM_NAME])

CREATE NONCLUSTERED INDEX [IX03_MEM_INF]
 ON [dbo].[MEM_INF] ([ANNU_SCHEDUL_CD])

--------------------------------------------------------------------
-- ■■DB로그 축소
-- 2003버전

SELECT * FROM ::fn_dblog(default, default) -- LOG 내용 확인
DBCC SQLPERF(LOGSPACE)  -- 모든 DB 이름, 로그사이즈, 사용률, 상태 확인
BACKUP LOG [DB명] WITH NO_LOG -- 로그 내용 지우기 (옵션 NO_LOG)
SP_HELPDB [DB명]   -- 대상 파일 이름 확인
DBCC SHRINKFILE([DB로그파일명], 5, TRUNCATEONLY) -- 5메가까지 축소
DBCC SHRINKFILE('DB로그파일명', 1)   -- 같은내용

-- 2008버전
http://h9911120.blog.me/50140277008
출처 : http://memocube.blogspot.com/2011/06/mssql2008-mssql2008.html

TRUNCATE_ONLY 옵션은 2008 버전부턴 사용되지 않습니다
로그를 비정상적으로 지우기 보다 제대로 사용하라는 의미인지 모르겠습니다
암튼 트랜잭션 로그의 올바른(?) 처치 방법은 두가지 인거 같네요

1. 데이터베이스 복구모델을 simple
ALTER DATABASE [DB명] SET RECOVERY SIMPLE
GO

DBCC SHRINKFILE([DB명], 1)
GO

ALTER DATABASE [DB명] SET RECOVERY FULL
GO

2. 트랜잭션 로그를 백업하고 로그파일 크기를 줄인다.
Backup log MILES to disk='c:\log_backup01.trn'
GO

DBCC SHRINKFILE('DB로그파일명', 2)
GO

이런 과정을 거쳐도 안줄어 드는 경우 한 번 더 반복하면 됩니다

--■■ 2008로그축소 버전2
------------------------------------------------------------------------------
-- Otto R. Radke - http://ottoradke.com
-- Info: T-SQL script to shrink a database's transaction log. Just set the
-- database name below and run the script and it will shrink the
-- transaction log.
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- Update the line below with the name of the database who's transaction
-- log you want to shrink.
------------------------------------------------------------------------------
USE YourDatabaseName
------------------------------------------------------------------------------
-- Don't change anything below this line.
------------------------------------------------------------------------------
GO
-- Declare variables
DECLARE @SqlStatement as nvarchar(max)
DECLARE @LogFileLogicalName as sysname
-- Alter the database to simple recovery
SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY SIMPLE'
EXEC ( @SqlStatement )
-- Make sure it has been altered
SELECT [name], [recovery_model_desc] FROM sys.databases WHERE [name] = DB_NAME()
-- Set the log file name variable
SELECT @LogFileLogicalName = [Name] FROM sys.database_files WHERE type = 1
-- Shrink the logfile
DBCC Shrinkfile(@LogFileLogicalName, 1)
-- Alter the database back to FULL
SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY FULL'
EXEC ( @SqlStatement )
-- Make sure it has been changed back to full
SET @SqlStatement = 'SELECT [name], [recovery_model_desc] FROM ' + DB_NAME() + '.sys.databases WHERE [name] = ''' + DB_NAME() + ''''
EXEC ( @SqlStatement )
------------------------------------------------------------------------------
-- ■■ WHILE문 연습
DECLARE @N INT
SET @N = 0
WHILE @N < 10000
 BEGIN
  EXEC HERDIN_INC_100
  SET @N = @N + 1
 END

USE [DB명]
 SET NOCOUNT ON;
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT COUNT(1) FROM HERDIN

SELECT COUNT(*) FROM HERDIN

EXEC SP_WHO