관리용 SQL 모음

/**********************************************************************

     프로그램명 : 관리용 SQL 모음

     용      도 : 관리용

     이          력 : 2012-03-22 (오현석) 정리

**********************************************************************/

-- ======================================================================

-- 데이터베이스 용량 정보

-- ======================================================================

--## 전체 데이터베이스의 용량정보

SP_HELPDB

--## 특정 데이터베이스의 용량정보 및 데이터 파일 정보

SP_HELPDB @DBNAME = master

--## 현재 데이터베이스의 용량정보

SP_SPACEUSED

--## 현재 데이터베이스의 파일 용량정보

SP_HELPFILE

--## 현재 데이터베이스의 파일들의 여유 공간 확인 (SQL 2005용)

SELECT     name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB

FROM     sys.database_files

--## 현재 데이터베이스의 파일들의 여유 공간 확인 (SQL 2000용)

SELECT     name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB

FROM     sysfiles

--## 전체 데이터베이스의 로그사이즈, 로그사용률확인

DBCC SQLPERF (LOGSPACE)

-- ======================================================================

-- 데이터 공간 축소

-- ======================================================================

--## 'AMS' 데이터베이스를10%의여유공간를두고데이터베이스를축소

DBCC SHRINKDATABASE (AMS, 10)

--## 'AMS' 데이터베이스의'AMS_Data' 파일의사이즈를10MB로축소

-- 10MB 이상이필요한경우최소한으로줄일수있는사이즈로자동조절됨

-- 실용량이15MB가필요한경우위의명령어를내려도15MB로축소됨

DBCC SHRINKFILE (AMS_Data, 10)

--## 트랜젝션로그 지우기 (SQL 2008)

USE AMS;

GO

ALTER DATABASE AMS

SET RECOVERY SIMPLE;

GO

DBCC SHRINKFILE (AMS_log, 1);

GO

ALTER DATABASE AMS

SET RECOVERY FULL;

GO

--## 트랜잭션로그를백업하되로그잘라내기수행

BACKUP LOG AMS WITH TRUNCATE_ONLY

--## 데이터베이스의 자동증가 설정

-- 'AMS' 데이터베이스의'AMS_Data'를100MB 자동증가로변경하고

-- 'AMS_Log'를10MB 자동증가로변경함

ALTER DATABASE AMS

MODIFY FILE

       (NAME = AMS_Data, 

       FILEGROWTH = 100MB

)

GO

ALTER DATABASE AMS

MODIFY FILE

       (NAME = AMS_Log, 

       FILEGROWTH = 10MB

)

GO

-- ======================================================================

-- 테이블 정보 정보

-- ======================================================================

--## 현재 데이터베이스의 테이블들별 Row 수

select     a.name,

          sum(b.row_count) as row_count

from     sys.tables a

          inner join sys.dm_db_partition_stats b on a.object_id = b.object_id

where     a.type = 'U'

group by a.name

order by a.name

--## 현재 데이터베이스의 테이블 사이즈 조회

CREATE TABLE #TableSize (

     [TableName]               VARCHAR(50),

     [RowCount]               INT,

     [TableSize]               VARCHAR(50) ,

     [DataSpaceUsed]          VARCHAR(50) ,

     [IndexSpaceUsed]     VARCHAR(50),

     [UnusedSpace]          VARCHAR(50) 

)

INSERT INTO #TableSize 

EXEC SP_MSFOREACHTABLE 'SP_SPACEUSED "?"' 

SELECT * FROM #TableSize ORDER BY [TableName]

DROP TABLE #TableSize

--## 현재 데이터베이스의 테이블 정보

select     [Table] = a.name,

          [Column] = b.name,

          [DataType] = c.name,

          [Size] = b.max_length,

          [IsNull] = case b.is_nullable when 1 then 'null' else 'not null' end,

          [Identity] = case b.is_identity when 1 then 'yes' else 'no' end,

          [Identity seed value] = d.seed_value,

          [Identity increment value] = d.increment_value,

          [Identity last value] = d.last_value,

          [Primary Key] = f.name

from     sys.objects a

          inner join sys.columns b on a.object_id = b.object_id

          inner join sys.systypes c on b.user_type_id = c.xusertype

          left outer join sys.identity_columns d on a.object_id = d.object_id and b.column_id = d.column_id

          left outer join sys.index_columns e on a.object_id = e.object_id and b.column_id = e.column_id

          left outer join sys.key_constraints f on a.object_id = f.parent_object_id and e.index_id = f.unique_index_id and f.type = 'PK'

where     a.type = 'U'

order by a.name, b.column_id