Results 1 to 3 of 3

Thread: Sql Problem

  1. #1
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71

    Unanswered: Sql Problem

    Really guys where does the size of a database being stored, i am getting confused, sp_helpdb 'dbname' shows the size and also table sysfiles also shows the size which is which and how will i get them to be the same

  2. #2
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Not positive what you are asking but sp_spaceused may help, see BOL for syntax.

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  3. #3
    Join Date
    Apr 2003
    Location
    SC, USA
    Posts
    51
    I got the following script from www.sqlservercentral.com but could not find a direct link to the script, so I have pasted it below.

    Very very handy, shows actual space used on a table-by-table basis

    -loach

    Code:
    go
    /*******************************************************************************
    
      Written By  : Simon Sabin
      Date        : 25 October 2002
      Description : Returns the spaceused by all tables in a database
                  : 
      History
      Date       Change
      ------------------------------------------------------------------------------
      25/10/2002 Created
    *******************************************************************************/
    
    SET NOCOUNT ON
    DECLARE @SetOption bit, @databasename varchar(30), @orderCol varchar(30), @numeric bit
    
    /*******************************************************************************
    --Change this to change the way data is ordered
    *******************************************************************************/
    SELECT @orderCol = 'data'
    
    SELECT @DatabaseName = db_name()
    SELECT @numeric = 1
    
    IF @DatabaseName <> 'Master' 
       AND NOT EXISTS (select 1 from master..sysdatabases WHERE name = @DatabaseName AND (status & 4) = 4)
      BEGIN
      exec sp_dboption @databaseName ,'select into/bulkcopy', 'true'
      SELECT @SetOption = 1
      END
    
    IF EXISTS (SELECT 1 FROM master..sysobjects WHERE name = 'space1')
      DROP TABLE master..space1
    CREATE TABLE master..Space1 (name varchar(60), rows varchar(11), reserved varchar(11), data varchar(11), index_size varchar(11), unused varchar(11))
    
    DECLARE @Cmd varchar(255)
    declare cSpace CURSOR FOR
      select 'USE ' +@DatabaseName + ' INSERT into master..space1 EXEC sp_spaceUsed ''[' + u.name + '].[' + o.name + ']'''
      FROM sysobjects o
      join sysusers u on u.uid = o.uid
      WHERE type = 'U' 
      AND o.Name <> 'Space1'
    
    OPEN cSPACE
    FETCH cSpace INTO @Cmd
    WHILE @@FETCH_STATUS =0
      BEGIN
    --  PRINT @Cmd
      EXECUTE (@Cmd)
      FETCH cSpace INTO @Cmd
      END
    DEALLOCATE cSPace
    
    SELECT Description,
           Rows,
           Reserved,
           Data,
           Index_size,
           dataPerRows
    FROM (
      SELECT 3 DataOrder,
             CONVERT(int,CASE @OrderCol WHEN 'Rows' THEN Rows
                              WHEN 'Reserved' THEN SUBSTRING(Reserved, 1,LEN(Reserved)-2)
                              WHEN 'data' THEN SUBSTRING(Data, 1,LEN(Data)-2)
                              WHEN 'index_size' THEN SUBSTRING(Index_size, 1,LEN(index_Size)-2)
                              WHEN 'unused' THEN SUBSTRING(unused, 1,LEN(unused)-2) END) OrderData, 
             name Description, 
             rows, 
             CASE @NUMERIC WHEN 0 THEN reserved ELSE SUBSTRING(reserved, 1, len(reserved)-2) END reserved, 
             CASE @NUMERIC WHEN 0 THEN data ELSE SUBSTRING(data, 1, len(data)-2) END data, 
             CASE @NUMERIC WHEN 0 THEN index_size ELSE SUBSTRING(index_size, 1, len(index_size)-2) END index_size,
             --SUBSTRING(data, 1, len(data)-2) DataPerRows
    --CONVERT(numeric(19,6),SUBSTRING(data, 1, len(data)-2)) /rows dataPerRows
             CASE WHEN Rows = 0 THEN '' ELSE CONVERT(varchar(11),CONVERT(numeric(10,2),CONVERT(numeric,SUBSTRING(reserved, 1, len(reserved)-2)) /rows*1000)) END DataPerRows
        FROM master..Space1 
      UNION ALL
      SELECT 1 DataOrder, 0 OrderData,
             CONVERT(varchar(30),'Total' ) Description,
             CONVERT(varchar(11),SUM(CONVERT(int,Rows))) Rows, 
             CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(Reserved, 1,LEN(Reserved)-2)))) Reserved, 
             CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(Data, 1,LEN(Data)-2)))) Data, 
             CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(index_size, 1,LEN(Index_size)-2)))) Index_Size,
             ''
      FROM master..space1
      UNION ALL
      SELECT 2, 0,
             REPLICATE('-',30),
             REPLICATE('-',11),
             REPLICATE('-',11),
             REPLICATE('-',11),
             REPLICATE('-',11),
             REPLICATE('-',11)
      UNION ALL
      SELECT 4,0,
             REPLICATE('-',30),
             REPLICATE('-',11),
             REPLICATE('-',11),
             REPLICATE('-',11),
             REPLICATE('-',11),
             REPLICATE('-',11)
      UNION ALL
      SELECT 5,0,
             CONVERT(varchar(30),'Total' ) Description,
             CONVERT(varchar(11),SUM(CONVERT(int,Rows))) Rows, 
             CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(Reserved, 1,LEN(Reserved)-2)))) Reserved, 
             CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(Data, 1,LEN(Data)-2)))) Data, 
             CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(index_size, 1,LEN(Index_size)-2)))) Index_Size,
             ''
      FROM master..space1 ) Stuff
    ORDER BY DataOrder, OrderData desc, description
    
    EXECUTE ('DROP TABLE master..space1')
    IF @SetOption = 1
      exec sp_dboption @databasename ,'select into/bulkcopy', 'false'
    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •