2010. 6. 4. 01:16

데이터베이스 사이즈

데이터베이스 사이즈 측정

  1. DB파일사이즈
  2.  Estimating the size of Table
  • (Heap 사이즈 혹은 클러스터 인덱스 사이즈) + 넌 클러스터 인덱스 사이즈의 합

  • 사이즈계산법

  • 사이즈 계산법으로 말고 sp_spaceused 에 datasize와 인덱스 사이즈를 보고 테이블 전체 사이즈를 아는것과 다른가?

    • sp_spaceused 프로시저 확인해 보기 : 확인해 보니 복잡하지 않고 윗 부분과 동일한 것 같음

    1.  --=====================================================
    2.  - 테이블 사이즈  
    3. --======================================================

    4. SELECT  

      1.   @reservedpages = SUM (reserved_page_count), 

      2.   @usedpages = SUM (used_page_count), 

      3.   @pages = SUM ( 

      4.    CASE 

      5.     WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) 

      6.     ELSE lob_used_page_count + row_overflow_used_page_count 

      7.    END 

      8.    ), 

      9.   @rowCount = SUM ( 

      10.    CASE 

      11.     WHEN (index_id < 2) THEN row_count 

      12.     ELSE 0 

      13.    END 

      14.    ) 

      15.  FROM sys.dm_db_partition_stats 

      16.  WHERE object_id = @id; 

      17.  

      18. SELECT  

      19.   name = OBJECT_NAME (@id), 

      20.   rows = convert (char(11), @rowCount), 

      21.   reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'), 

      22.   data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'), 

      23.   index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'), 

      24.   unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')

       

 

 

이 글은 스프링노트에서 작성되었습니다.

'T-SQL' 카테고리의 다른 글

Index::Defrag Script v4.0  (0) 2010.06.15
T_SQL::미 사용 Table  (0) 2010.06.15
T-SQL::Removing Duplication Data  (1) 2010.06.03
T-SQL::DB_Restore_move_to  (0) 2010.06.03
2010. 6. 3. 23:56

T-SQL::DB_Restore_move_to

  1.  --=============================================================
  2. -- 장비 이동하거나 DB 복원시 파일을 다른 디렉토리로 보관해야할때
  3. -- 파일들이 많은 DB의 경우 일일이 변경하기 어려움이 있어서 원본 기준으로 복원스크립트 생성하고
  4. -- 디렉토리 경로와 백업 파일 경로만 넣어주면 되게끔 생성 한다.
  5. --==============================================================
  6.  

  7. SET NOCOUNT ON

  8. DECLARE @sql            nvarchar(max)

  9. DECLARE @sql_move       nvarchar(3000)

  10. DECLARE @move           nvarchar(200)

  11. DECLARE @backup_type    char(1)

  12. DECLARE @name           sysname

  13. SET @backup_type = 'L' --N

  14. SET @sql_move = ''

  15. DECLARE cur_restore CURSOR FOR

  16.     SELECT name FROM sys.databases WHERE database_id > 4 and NAME != 'LiteSpeedLocal' and state = 0

  17.     ORDER BY name

  18.  

  19. OPEN cur_restore

  20. FETCH NEXT FROM cur_restore

  21. INTO @name

  22.  

  23. WHILE @@FETCH_STATUS = 0

  24. BEGIN

  25.     SET @sql = NULL
        SET @sql_move = ''

  26.     IF @backup_type = 'L'

  27.     BEGIN

  28.         SET @sql = 'exec master.dbo.xp_restore_database' + char(13)

  29.                    + '@database = ''' + @name + '''' + char(13)

  30.                    + ',@filename = ''''' + char(13)

  31.                    + ',@filenumber = 1' + char(13)

  32.                    + ',@with = ''REPLACE''' + char(13)

  33.                    + ',@with = ''NORECOVERY''' + char(13)

  34.  

  35.  

  36.         DECLARE cur_move CURSOR FOR

  37.                 SELECT ',@with = ''MOVE ''''' +

  38.                             name + ''''' TO N''''' +

  39.                             filename + ''''''' '

  40.                 FROM sys.sysaltfiles  WHERE dbid = db_id(@name)

  41.                 ORDER BY fileid

  42.         OPEN cur_move

  43.         FETCH NEXT FROM cur_move

  44.         INTO @move

  45.  

  46.         WHILE @@FETCH_STATUS = 0

  47.         BEGIN

  48.             SET @sql_move = @sql_move + @move + char(13)

  49.            

  50.             FETCH NEXT FROM cur_move

  51.             INTO @move

  52.         END

  53.         CLOSE cur_move

  54.         DEALLOCATE cur_move

  55.  

  56.   

  57.     END

  58.     ELSE IF @backup_type = 'N'

  59.     BEGIN

  60.         SET @sql = 'RESTORE DATABASE ' + @name + char(13)

  61.                  + 'FROM DISK =''''' + char(13)

  62.                  + 'WITH NORECOVERY' + char(13)

  63.  

  64.         DECLARE cur_move CURSOR FOR

  65.                 SELECT ',MOVE ''' + name + ''' TO  ''' + filename + ''''

  66.                 FROM sys.sysaltfiles  WHERE dbid = db_id(@name)

  67.                 ORDER BY fileid

  68.         OPEN cur_move

  69.         FETCH NEXT FROM cur_move

  70.         INTO @move

  71.  

  72.         WHILE @@FETCH_STATUS = 0

  73.         BEGIN

  74.             SET @sql_move = @sql_move + @move + char(13)

  75.            

  76.             FETCH NEXT FROM cur_move

  77.             INTO @move

  78.         END

  79.         CLOSE cur_move

  80.         DEALLOCATE cur_move

  81.     END

  82.  

  83.     SET @sql = @sql + @sql_move + char(13)

  84.  

  85.     print @sql

  86.  

  87.     FETCH NEXT FROM cur_restore

  88.     INTO @name

  89. END

  90.  

  91. CLOSE cur_restore

  92. DEALLOCATE cur_restore

  93.  

이 글은 스프링노트에서 작성되었습니다.

'T-SQL' 카테고리의 다른 글

데이터베이스 사이즈  (0) 2010.06.04
T-SQL::Removing Duplication Data  (1) 2010.06.03
T-SQL::Convert hex value to String 32bit  (0) 2010.06.03
T_SQL::CONSTRAINT조사  (1) 2010.06.03
2010. 6. 3. 23:55

T-SQL::Convert hex value to String 32bit

 Convert Hex value to Signed 32-bit int

 

  1. /* ======================================================================== *
     *   Function Name:    fnHexToInt                                           *
     *                                                                          *
     * -- Description --------------------------------------------------------- *
     * Convert, up to 8 nibbles, hex string ('FFFFFFFF'...'00000000')           *
     *      to                                                                  *
     *  Signed 32-bit int (-2147483648..2147483647).                            *
     * -- History ------------------------------------------------------------- *
     * 1.0.0 28.Oct.2001, Ofer Bester                                           *
     * ======================================================================== */
    PRINT '* CREATE FUNCTION: fnHexToInt, Ver. 1.0.0 (28.Oct.2001).'
    SETUSER 'dbo'
    go
  2. IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'fnHexToInt' AND type IN ('FN', 'IF', 'TF'))
    BEGIN
        PRINT '  + DROP FUNCTION dbo.fnHexToInt.'
        DROP FUNCTION dbo.fnHexToInt
    END
    PRINT '  + CREATE FUNCTION dbo.fnHexToInt.'
    go
  3. CREATE FUNCTION dbo.fnHexToInt (@Hex varchar(8))
    RETURNS int
    -- Ver. 1.0.0 28.Oct.2001, Ofer Bester
    AS BEGIN
        DECLARE @i      tinyint,
                @Nibble tinyint,
                @ch     char(1),
                @Result int
  4.     SET @i      = 1                                                     -- Init nibble counter
        SET @Result = 0                                                     -- Init output parameter
  5.     SET @Hex     = UPPER( LTRIM( RTRIM( @Hex ) ) )                      -- Convert to uppercase
  6.     WHILE (@i <= LEN(@Hex))
        BEGIN
            SET @ch = SUBSTRING(@Hex, @i, 1)
  7.         IF      (@ch >= '0' AND @ch <= '9') SET @Nibble = ASCII(@ch) - ASCII('0')
            ELSE IF (@ch >= 'A' AND @ch <= 'F') SET @Nibble = ASCII(@ch) - ASCII('A') +10
            ELSE RETURN NULL
  8.         IF( @Result > 0x7FFFFFF)                                        -- 134217727 = 0x7FFFFFF
            BEGIN
                SET @Result = @Result & 0x7FFFFFF                           -- Set MSB, of 7 nibbles, OFF
                SET @Result = @Result * 16 + @Nibble +0x80000000            -- Shift left 4Bits, Add last nibble and convert to negetive number.
            END
            ELSE BEGIN
               SET @Result = @Result *16 +@Nibble                           -- Shift left 4Bits, Add nibble.
            END
  9.         SET @i = @i +1                                                  -- Next nibble.
        END -- While
  10.     RETURN ( @Result )
    END -- Function
    go
  11. -- Check create result -------------------------------------------------------
    IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'fnHexToInt' AND type IN ('FN', 'IF', 'TF'))
    BEGIN
        PRINT 'DONE (CREATE FUNCTION: fnHexToInt).'
    END
    ELSE BEGIN
        PRINT '****** FAILES (CREATE FUNCTION: fnHexToInt) ******'
    END
    PRINT ''
    go
  12. SETUSER
    go

이 글은 스프링노트에서 작성되었습니다.

'T-SQL' 카테고리의 다른 글

T-SQL::Removing Duplication Data  (1) 2010.06.03
T-SQL::DB_Restore_move_to  (0) 2010.06.03
T_SQL::CONSTRAINT조사  (1) 2010.06.03
SQL Server 2005 and 2008 Ranking Functions  (0) 2010.04.05
2010. 6. 3. 23:55

T_SQL::CONSTRAINT조사

CONSTRAINT 조사

 

  1. select * from sys.default_constraints 
  2. select * from sys.key_constraints 
  3. select * from sys.check_constraints 

  4. --=================
  5. -- 2005용
  6. --=================
  7. -- FK
  8. SELECT  object_name(fk.constraint_object_id) AS fk_name, 
  9.     fk.constraint_column_id AS fk_clolum,
  10.     object_name(fk.parent_object_id) AS parent_name,
  11.     (select name from sys.columns where object_id =  fk.parent_object_id and column_id = fk.parent_column_id) as parent_column,
  12.     object_name(fk.referenced_object_id) AS referenced_name,
  13.     (select name from sys.columns where object_id =  fk.referenced_object_id and column_id = fk.referenced_column_id) as referenced_column
  14. FROM sys.foreign_key_columns  as fk
  15. WHERE fk.parent_object_id  = object_id('<@table_name, @sysname,@table_name>') 
  16.         or  fk.referenced_object_id = object_id('<@table_name, @sysname,@table_name>')

  17. -- DEFAULT
  18. SELECT object_name(parent_object_id) as table_name,
  19.        name,
  20.        (select name from sys.columns where object_id =  df.parent_object_id and column_id = df.parent_column_id) as column_name, 
  21.        definition
  22. FROM sys.default_constraints   as df
  23. WHERE object_name(parent_object_id) = object_id('<@table_name, @sysname,@table_name>')

  24. -- DEFAULT
  25. SELECT
  26.     object_name(parent_object_id) as table_name,
  27.        name,
  28.        (select name from sys.columns where object_id =  ck.parent_object_id and column_id = ck.parent_column_id) as column_name, 
  29.        definition
  30. FROM sys.check_constraints  as ck
  31. WHERE object_name(parent_object_id) = object_id('<@table_name, @sysname,@table_name>')



  32. --=================
  33. -- 2000용
  34. --=================
  35. --FK
  36. select object_name(fkeyid) , object_name(constid),object_name(rkeyid)  
  37. from sys.sysforeignkeys where fkeyid = object_id ('<@table_name, @sysname,@table_name>')


  38. select object_name(id), object_name(constid) ,status from sys.sysconstraints  
  39. where status&5 = 1 --FK 
  40. where constid  = object_id('<@table_name, @sysname,@table_name>')
  41. order by object_name(constid)



  42. --=========================== 
  43. -- DROP
  44. --===========================
  45. -- FK 2005용
  46. SELECT 'ALTER TABLE ' + bject_name(fk.parent_object_id) + ' DROP CONSTRAINT ' +  object_name(fk.constraint_object_id)
  47. FROM sys.foreign_key_columns
  48. WHERE fk.parent_object_id  = object_id('<@table_name, @sysname,@table_name>') 

  49. -- DEFULT 2005용
  50. SELECT 'ALTER TBLE ' + object_name(parent_object_id)  + ' DROP CONSTRAINT ' + name
  51. FROM sys.default_constraints 
  52. WHERE object_name(parent_object_id) = object_id('<@table_name, @sysname,@table_name>')

  53. -- CK 2005용
  54. -- DEFULT 2005용
  55. SELECT 'ALTER TBLE ' + object_name(parent_object_id)  + ' DROP CONSTRAINT ' + name
  56. FROM sys.default_constraints  
  57. WHERE object_name(parent_object_id) = object_id('<@table_name, @sysname,@table_name>')


  58. -- FK 2000용
  59. SELECT 'ALTER TABLE ' + object_name(fkeyid) + ' DROP CONSTRAINT ' +  object_name(constid) 
  60. FROM sys.sysforeignkeys
  61. WHERE fkeyid = object_id ('<@table_name, @sysname,@table_name>')

  62. -- INDEX 2000용
  63. select  'ALTER TABLE ' + object_name(id) + ' DROP INDEX '  + name 
  64. from sys.sysindexes where  object_name(id) in ('GOODSDAQ_BANNER_POSITION')
  65. order by id, name




    

        


이 글은 스프링노트에서 작성되었습니다.

'T-SQL' 카테고리의 다른 글

T-SQL::DB_Restore_move_to  (0) 2010.06.03
T-SQL::Convert hex value to String 32bit  (0) 2010.06.03
SQL Server 2005 and 2008 Ranking Functions  (0) 2010.04.05
DMV::Index  (0) 2010.04.05
2010. 6. 3. 23:55

T_SQL::SP_WHO2 + DBCC

SQL SERVER 2005 이상
  1. --===================================

  1. -- SP_WHO2와명령어동시확인하기

  1. --===================================

  1.  

  1. SELECT

  1.        ISNULL(D.text, '') AS SQLStatement,

  1.        object_name(D.objectid) AS 'sp_ame',

  1.        A.Session_ID SPID,

  1.        ISNULL(B.status,A.status) AS [Status],

  1.        A.login_name AS [Login],

  1.        ISNULL(A.host_name, '  .') AS HostName,

  1.        ISNULL(CAST(C.BlkBy AS varchar(10)), '  .') AS BlkBy,

  1.        DB_NAME(B.Database_ID) AS DBName,

  1.        B.command,

  1.        ISNULL(B.cpu_time, A.cpu_time) AS CPUTime,

  1.        ISNULL((B.reads + B.writes),(A.reads + A.writes)) AS DiskIO,

  1.        A.last_request_start_time AS LastBatch,

  1.        ISNULL(A.program_name, '') AS ProgramName,

  1.        ISNULL(A.client_interface_name, '') AS ClientInterfaceName

  1. FROM sys.dm_exec_sessions A

  1.        LEFT OUTER JOIN sys.dm_exec_requests B ON A.session_id = B.session_id

  1.        LEFT OUTER JOIN (

  1.              SELECT A.request_session_id SPID, B.blocking_session_id BlkBy

  1.              FROM sys.dm_tran_locks A

  1.                     INNER JOIN sys.dm_os_waiting_tasks B ON A.lock_owner_address = B.resource_address

  1.        ) C ON A.Session_ID = C.SPID

  1.        OUTER APPLY sys.dm_exec_sql_text(B.sql_handle) D

  1. go


 

이 글은 스프링노트에서 작성되었습니다.


'Monitoring' 카테고리의 다른 글

T-SQL:: Default Trace로 DB 증가량 확인  (1) 2011.04.15
Admin::Tempdb 의 작업, 모니터링  (0) 2011.01.30
CPU에 할당된 Task 보기  (1) 2010.03.14
DBCC FLUSHPROCINDB  (0) 2010.02.04