Bora.Choi

-- 퍼옴. /* Find Duplicate Indexes This script returns the Duplicate indexes on any database I use 2 User defined Functions Compatible with SQLServer 2000 (I used sp3) It won't work in SQL Server 7.0 because It uses user-defined functions and a Memory. Created by G.R. Preethiviraj Kulasingham pkulasingham@virtusa.com Written on : February 20, 2003 Modified on : May 18, 2004 Additional code written..

SQL SERVER 2000 이상 EventClass : 92, 93, 94, 95 번은 DB File의 증가하거나 Shrink 하는 이벤트 임. Default Trace가 되고 있는 SQL Server에서는 해당 이벤트를 수집하고 있으며, 증가/축소되는 사이즈를 확인할 수 있다. begin try if (select convert(int,value_in_use) from sys.configurations where name = 'default trace enabled' ) = 1 begin declare @curr_tracefilename varchar(500) ; declare @base_tracefilename varchar(500) ; declare @indx int ; select @curr_t..

SET NOCOUNT ON DECLARE @shrinkUnitSize INT, @TotalSizeMB INT, @filename varchar(50), @start_time datetime, @idx int SET @shrinkUnitSize = 500 SELECT @TotalSizeMB = size/128, @filename= name FROM master..sysaltfiles where dbid=db_id('dba') and fileid =1 SELECT @TotalSizeMB AS CurrentSizeMB, GETDATE() AS StartTime SET @idx = 1 WHILE (@TotalSizeMB > 300000)-- 현재사이즈가GB보다클때줄이는작업(file사이즈를GB아래로조정하는작업) ..

SQL Server 2005 이상 1. JOB의 수행 완료 시간 계산 SELECT a.name, b.run_date, convert(varchar(4),run_time/10000) + ':' + convert(varchar(4),run_time/100%100) + ':' + convert(varchar(4),run_time%100) as run_time ,convert(varchar(4),run_duration/10000) + ':' + convert(varchar(4),run_duration/100%100) + ':' + convert(varchar(4),run_duration%100) as run_duration ,convert(int, (run_duration/10000 * 3600 ) + ((ru..

SQL SERVER 2008 SP 2 이상 출처: http://sqlfool.com/category/sql-scripts/ 인덱스 조각화를 얻고 실행하는 sp 이다. SQLl 2005의 sp2 이상에서 가능하다. 1. 테이블 생성 /* Scroll down to the see notes, disclaimers, and licensing information */ DECLARE @indexDefragLog_rename VARCHAR(128) , @indexDefragExclusion_rename VARCHAR(128) , @indexDefragStatus_rename VARCHAR(128); SELECT @indexDefragLog_rename = 'dba_indexDefragLog_obsolete_' +..

미사용 Table 목록 SELECT idx.object_id, OBJECT_NAME(IDX.object_id) as object_name, max(ps.row_count) as row_count, max(obj.create_date) as create_date, max(obj.modify_date) as modify_date FROM sys.dm_db_index_usage_stats AS DIS RIGHT OUTER JOIN sys.indexes AS IDX ON DIS.object_id = IDX.object_id AND DIS.index_id = IDX.index_id JOIN sys.objects AS OBJ ON IDX.object_id = OBJ.object_ID JOIN sys.dm_db_pa..