개기획자

테이블 별 row 수 조회

SELECT o.name , i.rows FROM sysindexes i INNER JOIN sysobjects o ON i.id = o.id WHERE i.indid < 2 AND o.xtype = 'U' ORDER BY i.rows DESC

트리거 생성 이후 잘못된 규약이나 오류로 인해 계정 로그인 불가 현상 해결

먼저 트리거에 문제가 있으면 해당 트리거를 drop 해야하나 세션 및 로그인이 불가한 경우에 사용하는 방법 입니다.

서버에 접속하여 윈도우 인증으로 로그인하여 현재 실행중인 트리거를 조회하여 삭제 합니다.

트리거 조회

SELECT * FROM master.sys.server_triggers DRPO TRIGGER 트리거이름 ON ALL SERVER

하지만 윈도우 인증 사용을 막은 경우 로그인 할 수있는 방법이 제한됩니다.

SQL SERVER 단일 사용자 모드로 변경했다 재설정 이후 다시 다중사용자 모드로 변경하는 방법도 있으나, DAC로 연결하여 단일 쿼리창을 열어 위에 쿼리문을 수행하는 편이 좋습니다.

DAC접속은 개체 탐색기로 연결은 불가하고, 데이터베이스 연결쿼리를 통해서 접속할 수 있습니다.

테이블 별 사이즈 조회

--테이블 별 사이즈 조회 SELECT CONVERT(VARCHAR(30), MIN(o.name)) AS t_name , LTRIM(STR(SUM(reserved) * 8192.0 / 1024.0, 15, 0) + ' KB') AS t_size FROM sysindexes i INNER JOIN sysobjects o ON o.id = i.id WHERE i.indid IN (0, 1, 255) AND o.xtype = 'U' GROUP BY i.id ORDER BY-- t_name ASC SUM(reserved) * 8192.0 / 1024.0 DESC

--모든 데이터베이스 크기 조회 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