Calculating disk space please!!!!

  • I am a newbie to SQL server, I can do basics.

    I have been taked to calculate the space needed for a new SQL Server. How on earth can I do this with 12 database and hundreds of tables? I need a simple calculation method or a tool I can do this with.

    Can anyone help me please

  • Unfortunatly I know of no easy way to figure this out.  If you go to SQL books online, and search for "Calculating database size", there is a lengthy formula there.  That is the only way I know how, still kind of new to this myself.

  • If the databases already exist, take a sample of how many records appear in a timeframe of say, a year for each table (get the year with the most records). This will tell you your biggest growth to date.

    Then use that figure to project future growth.

    Is a tiresome task, but will save headaches in the future.

    If you're expected to size databases that don't exist just yet, that is

    a question that can only be answered once the business has made up its mind

    ---------------------------------------------
    If data can screw you, it will; never assume the data are correct.

  • Would check database and LOG file sizes periodically and use the following proc to learn what individual tables are doing i.e, what size each table is using within the data base.  A point of caution it is possible that depending upon additions / updates / deletions made to data to have the log file grow faster than the database so if it is disc space you are worried about - check both

    Also Books on Line has some great discussion of database and log file size prediction.  What I get from BOL is predicting size of a non existent database and associated log file is not much more than a guessing game

    To track table size and space each table uses within the database try this proceedure

    (Disclaimer - I owe this procedure to another DBA whose name I have forgotten, and who I probably forgot to thank, for sharing his knowledge with us here on the forums, but I use it frequently) 

    CREATE procedure space_used

    @TablePattern varchar(128) = '%',

    @updateusage varchar(5) = false

    as

    declare @id    int 

    declare @type  character(2)

    declare   @pages    int 

    declare @dbname sysname

    declare @dbsize dec(15,0)

    declare @bytesperpage    dec(15,0)

    declare @pagesperMB      dec(15,0)

    declare @objname nvarchar(776) 

    create table #spt_space (     id        int null, 

    rows      int null,  reserved  dec(15) null,    

    data      dec(15) null, indexp         dec(15) null,  

    unused         dec(15) null )

    if @updateusage is not null    

        begin   

          select @updateusage=lower(@updateusage)   

          if @updateusage not in ('true','false')    

              begin                    raiserror(15143,-1,-1,@updateusage)       

                return(1)        

             End    

        End

    select @dbname = db_name()

    declare cur cursor for          select TABLE_NAME = convert(sysname,o.name)           from sysobjects o          where o.type in ('U')           

     and convert(sysname,o.name) like @TablePattern         

    order by 1

    OPEN cur

       FETCH NEXT FROM cur INTO @objname

          WHILE @@FETCH_STATUS = 0

             begin

               select @id = null

               select @id = id,  @type = xtype         

               From sysobjects               where id = object_id(@objname)    

             

              if @id is null         

                begin              

                   raiserror(15009,-1,-1,@objname,@dbname) 

                   return (1)

               End

           if @updateusage = 'true'         

              dbcc updateusage(0,@objname) with no_infomsgs    

              set nocount on

              insert into #spt_space (id, reserved)

              select @id, sum(reserved)   From sysindexes  where indid in (0, 1, 255)  and id = @id   

              select @pages = sum(dpages)  From sysindexes  Where indid < 2 and id = @id

              select @pages = @pages + isnull(sum(used), 0)   From sysindexes  Where indid = 255 and id = @id

             update #spt_space 

             set data = @pages where id = @id

             update #spt_space

             set indexp = (select sum(used) From sysindexes where indid in (0, 1, 255)   and id = @id)                   - data         

             where id = @id    

             update #spt_space         

             Set unused = reserved

          - (select sum(used)  From sysindexes  where indid in (0, 1, 255) and id = @id) 

            where id = @id   

            update #spt_space

            Set Rows = I.Rows  from #spt_space s  join sysindexes i on i.id = s.id   Where I.indid < 2   and i.id = @id    

            FETCH NEXT FROM cur INTO @objname

        End

        Close cur

        DEALLOCATE cur    

        select name = substring(object_name(id), 1, 30),  rows = convert(char(11), rows),  reserved = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'), 

        data = ltrim(str(data * d.low / 1024.,15,0) +   ' ' + 'KB'),   

        index_size = ltrim(str(indexp * d.low / 1024.,15,0) +  ' ' + 'KB'),         

        unused = ltrim(str(unused * d.low / 1024.,15,0) +   ' ' + 'KB')   

        from #spt_space s, master.dbo.spt_values d     Where d.Number = 1  and d.type = 'E'    

        order by s.reserved * d.low desc    

        select name = cast('TOTAL' as char(30)),   

        rows = convert(char(11), SUM(rows)),        

         reserved = ltrim(str(SUM(reserved * d.low) / 1024.,15,0) + ' ' + 'KB'),   

        data = ltrim(str(SUM(data * d.low) / 1024.,15,0) +  ' ' + 'KB'),         

        index_size = ltrim(str(SUM(indexp * d.low) / 1024.,15,0) + ' ' + 'KB'),  

        unused = ltrim(str(SUM(unused * d.low) / 1024.,15,0) + ' ' + 'KB') 

        from #spt_space s, master.dbo.spt_values d     Where d.Number = 1      

    and d.type = 'E'

    GO

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • sp_spaceused is another option...

    Mike

  • Assuming these databases already exist, you can use sp_spaceused as someone already mentioned. Fairly accurate.

    If these databases exist on another platform or are not yet implemented, it is better to take an educated guess as opposed to using sizing formulas out of BOL (would take too long). Better choice - if you have time and the software, use a data modeling tool such as CA ERwin to reverse engineer the data model (good exercise anyway) and from there you can use its volumetrics feature to estimate the size of a database for any of its supported platforms. This will give you a more accurate picture, and you only need to estimate your LARGEST tables to get in the ballpark.

    Whatever you do, don't be afraid to add extra disk space to your estimate. Disk space is relatively cheap. You will need extra space to do most DBA tasks. Good luck.

  • You can use the following to get at each db size on the server and then sum for a total.  This will give you a record for the db (fileID=1) and a record for the log (fileID=2).

    insert into TableName (db, fileID, filesize)

     exec sp_MSforeachdb @command1 =

      'select db_name(dbid), sf.fileid, sf.size

      from sysdatabases sd, ?..sysfiles sf

      where db_name(dbid) = ''?'''

  • Thanks to all for your responses. I am very greatful

    xydon

  • I am a newbie to T-SQL so please bear with me. I am trying to understand and get your query to work for me. I created a 'tablename' with (db,fileID, filesize) and nothing happen. Please tell me what I am doing wrong. thanks

  • greene

    try this

    --insert into TableName (db, fileID, filesize)

    Use Master

     exec sp_MSforeachdb @command1 =

      "select db_name(dbid), sf.fileid, sf.size

      from sysdatabases sd, ?..sysfiles sf

      where db_name(dbid) = '?' "

    Note the differences ...

    Removed need to create a table in the master database. Used Query Analyzer and just displayed results in a grid format.

    Added Use Master

    subsituted " (quotation mark) for ' (single quote before word select)

    removed extra ' (single quotation mark) before and after last ? (question mark)

    Added " double quote after ' (single quote) as the last character of the TSQL statement ... then it worked.

    Remember to use BOL - to understand what size is being reported - it is number of 8K pages ..

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • ok, thanks I will try it.

  • Use Books on Line (BOL) - it is the help file when clicking help for Query Analyzer - look up "Setting Data Base Options" then scroll down to "Quoted Identifier" - might help you understand why I did what I did

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • --what DBs not to include in searched criteria

    select name

    into #db

    from master..sysdatabases

    where name not in ('master','tempdb','model','msdb')

    --#tB base table to store results of one database

    create table #tB(name varchar(200), rows int, reserved varchar(20), data varchar(20), index_size varchar(20), unused varchar(20))

    --#tR result table to store all output results (all DBs)

    create table #tR(db varchar(200), name varchar(200), rows int, KBreserved int, KBdata int, KBindex_size int, KBunused int)

    declare @dbName varchar(100), @sql varchar(3000)

    DECLARE cs CURSOR FOR

    SELECT name FROM #db order by name

    OPEN cs

    FETCH NEXT FROM cs into @dbName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    print '> > '+@dbName

    truncate table #tB

    --store table sizes into #tB

    select @sql='use '+@dbName+' exec sp_MSforeachtable "insert into #tB exec sp_spaceused ''?''"'

    exec(@sql)

    --store table sizes of 1 DB into #tR

    insert into #tR select @dbName,name,rows,convert(int,replace(reserved,' KB','')),convert(int,replace(data,' KB','')),convert(int,replace(index_size,' KB','')),convert(int,replace(unused,' KB','')) from #tB

    FETCH NEXT FROM cs into @dbName

    END

    CLOSE cs

    DEALLOCATE cs

    --> display results

    select * from #tR order by db,rows desc

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply