개기획자

--모든 데이터베이스 크기 조회 SELECT sys.databases.name , CONVERT(VARCHAR,SUM(size)*8/1024/1024) +' GB' AS [Total disk space] FROM sys.databases JOIN sys.master_files ON sys.databases.database_id = sys.master_files.database_id GROUP BY sys.databases.name ORDER BY [Total disk space], sys.databases.name

인덱스 정의서

--인덱스 정의서 select OBJECT_NAME(a.object_id) TableName, '' kortablename, a.name IndexName, TYPE_NAME(a.type) TypeName, Type_desc, is_unique , index_column_id colseq , COL_NAME(a.object_id, column_id) colName , (case when is_descending_key = 1 then 'DESC' else 'ASC' end) sortType from sys.indexes A join sys.index_columns B on a.object_id=b.object_id and a.index_id=b.index_id

쿼리 변경이력 확인

--쿼리 변경이력 확인 SELECT * FROM sys.sql_modules sm inner join sys.all_objects ao on sm.object_id = ao.object_id order by modify_date desc

30%이상 조각난 인덱스 조회

SELECT DB_NAME() AS 'DatabaseName' , OBJECT_NAME(S.OBJECT_ID) AS 'TableName' , I.NAME AS 'IndexName' , ROUND(AVG_FRAGMENTATION_IN_PERCENT, 2) AS 'Fragmentation %' FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) AS S INNER JOIN SYS.INDEXES AS I ON S.OBJECT_ID = I.OBJECT_ID AND S.INDEX_ID = I.INDEX_ID WHERE S.DATABASE_ID = DB_ID() -- 현재 데이터베이스 AND I.NAME IS NOT NULL -- HEAP은 무시 AND S.AVG_FRAGMENTATION_IN_PERCENT > 30 AND OBJECTPROPERTY(S.OBJECT_ID, 'IsMsShipped') = 0 -- 시스템 개체 무시 ORDER BY [Fragmentation %] DESC

작업스케줄러 실패 모니터링

SELECT sysjobs.name, sysjobs.description, sys_srvs.last_run_outcome, sys_srvs.last_outcome_message, sys_srvs.last_run_date, sysjobschedules.next_run_date FROM msdb.dbo.sysjobs AS sysjobs INNER JOIN msdb.dbo.sysjobservers AS sys_srvs ON sysjobs.job_id = sys_srvs.job_id LEFT OUTER JOIN msdb.dbo.sysjobschedules as sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id WHERE sys_srvs.last_run_outcome = 0; --실패JOB

대기 상태에 있는 wait, spid 확인

select * from sysprocesses where waittime > 0 and spid > 50

백업 및 백업모델 조회

SELECT CASE WHEN finishDate IS NOT NULL THEN '백업완료' ELSE '미백업' END AS [백업상태] , B.[NAME] AS [데이터베이스 이름] , A.[NAME] AS [백업파일 이름] , B.recovery_model_desc AS [데이터베이스 복구모델] , A.timesec AS [백업실행시 걸린 시간(초)] , B.create_date AS [데이터베이스 생성일] , A.finishDate AS [백업날짜] , A.backupsize AS [백업파일크기] FROM ( SELECT NAME -- 백업파일이름 , database_name -- 백업대상 데이터베이스 , USER_NAME -- 백업작업을 실행한 사용자 , CONVERT(CHAR(10), backup_finish_date, 120) AS finishDate -- 백업된날짜 , DATEDIFF(ss, backup_start_date, backup_finish_date) AS timesec -- 백업실행시간 , REPLACE(CONVERT(VARCHAR, CONVERT(MONEY, ROUND(backup_size / 1024, 0)), 1), '.00', '') + 'KB' AS backupsize -- 백업사이즈 FROM msdb.dbo.backupset WITH(NOLOCK) WHERE TYPE = 'D' AND backup_finish_date > CONVERT(CHAR(10), GETDATE(), 120) ) AS A RIGHT JOIN ( SELECT NAME -- DBMS내에 존재하는 DB들 , create_date -- DB생성일 , recovery_model_desc -- DB복구모델 FROM MASTER.sys.databases WHERE NAME NOT IN ( 'model', 'tempdb' ) ) AS B ON A.database_name = B.NAME

sp_updatestats

IF OBJECT_ID('tempdb..#temp_index_rebuild') IS NOT NULL 
        DROP TABLE tempdb..#temp_index_rebuild

BEGIN TRY
--BEGIN TRAN

use DB명

    SET NOCOUNT ON;

    DECLARE @objectid INT;
    DECLARE @indexid INT;
    DECLARE @partitioncount BIGINT;
    DECLARE @schemaname NVARCHAR(130);
    DECLARE @objectname NVARCHAR(130);
    DECLARE @indexname NVARCHAR(130);
    DECLARE @partitionnum BIGINT;
    DECLARE @partitions BIGINT;
    DECLARE @frag FLOAT;
    DECLARE @pagecount INT;
    DECLARE @command NVARCHAR(4000);

    DECLARE @page_count_minimum SMALLINT
    SET @page_count_minimum = 10

    DECLARE @fragmentation_minimum FLOAT
    SET @fragmentation_minimum = 30.0

    SELECT  object_id AS objectid ,
            index_id AS indexid ,
            partition_number AS partitionnum ,
            avg_fragmentation_in_percent AS frag ,
            page_count AS page_count
    INTO    #temp_index_rebuild
    FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,
                                           'LIMITED')
    WHERE   avg_fragmentation_in_percent > @fragmentation_minimum
            AND index_id > 0
            AND page_count > @page_count_minimum;

IF CURSOR_STATUS('global', 'partitions') >= -1
BEGIN
 PRINT 'partitions CURSOR DELETED' ;
    CLOSE partitions
    DEALLOCATE partitions
END
    DECLARE partitions CURSOR LOCAL
    FOR
        SELECT  *
        FROM    #temp_index_rebuild;

    OPEN partitions;

    WHILE ( 1 = 1 )
        BEGIN;
            FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag, @pagecount;

            IF @@FETCH_STATUS < 0
                BREAK;

            SELECT  @objectname = QUOTENAME(o.name) ,
                    @schemaname = QUOTENAME(s.name)
            FROM    sys.objects AS o
                    JOIN sys.schemas AS s ON s.schema_id = o.schema_id
            WHERE   o.object_id = @objectid;

            SELECT  @indexname = QUOTENAME(name)
            FROM    sys.indexes
            WHERE   object_id = @objectid
                    AND index_id = @indexid;

            SELECT  @partitioncount = COUNT(*)
            FROM    sys.partitions
            WHERE   object_id = @objectid
                    AND index_id = @indexid;

            SET @command = N'ALTER INDEX ' + @indexname + N' ON '
                + @schemaname + N'.' + @objectname + N' REBUILD';

            IF @partitioncount > 1
                SET @command = @command + N' PARTITION='
                    + CAST(@partitionnum AS NVARCHAR(10));

            EXEC (@command);
            --print (@command); --uncomment for testing

            PRINT N'Rebuilding index ' + @indexname + ' on table '
                + @objectname;
            PRINT N'  Fragmentation: ' + CAST(@frag AS VARCHAR(15));
            PRINT N'  Page Count:    ' + CAST(@pagecount AS VARCHAR(15));
            PRINT N' ';
        END;

    CLOSE partitions;
    DEALLOCATE partitions;

    DROP TABLE #temp_index_rebuild;

END TRY
BEGIN CATCH
    PRINT 'ERROR ENCOUNTERED:' + ERROR_MESSAGE()
END CATCH

SELECT P.spid, P.login_time, P.last_batch, P.status, P.program_name, P.cmd, C.client_net_address
   FROM sys.sysprocesses AS P INNER JOIN sys.dm_exec_connections AS C
   ON P.spid = C.session_id