Checking space

Two weeks ago i ran this on all our databases
exec sp_helpDB 'database'

I wrote down the size of the DATA and the LOG into an excel spreadsheet i get around to automation of this in to a table one day.

Then after two weeks i did it again and the sizes remained the same.............i was hoping it go up.........or no one doing anything but i find that hard to do as its our ERP database.

Am i running the best check
LVL 7
TRACEYMARYAsked:
Who is Participating?
 
imran_fastCommented:
then run
select * from dbo.sysfiles
check for the size column today
run the same query after one week and check the size column
and see the difference (which will be the amout of space utilized for one week)

the size column is calculated as (sized currently used by database in KB)/ 8
0
 
Anthony PerkinsCommented:
The size you are recording is the Free + Used size, so even though you have added data the total size remains the same.  In other words, since the Used size has not reached the configured threshhold the database size has not increased.
0
 
TRACEYMARYAuthor Commented:
is the best command i should run.

When set up database i do not put any restrictions ...put automatically grow..........does sql automatically allocate free space.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Anthony PerkinsCommented:
>>does sql automatically allocate free space.<<
Only you know the answer to that question.  It depends how you defined the database.  By default the database increases in size in 10% increments, but this can be changed.
0
 
iloyaCommented:
Use this query against Master Database. It will help you to get the actual sizes of Databases and Log files along with growth factor.  The sizes are in KB.

Select  Name, FileName, Growth,  Size from master..sysaltfiles
0
 
imran_fastCommented:
<<Then after two weeks i did it again and the sizes remained the same>>
The size of the files will only increase when it needs them by default  data and log file will increase by 10% so i think in your case there is still available free space in the data and log file and it will increase by 10% when it ran out of it.

you can check the available free space in your file by simply

in enterprise manager Right Click Database ----> All Tasks --> Shirnk Database --> space Free
0
 
TRACEYMARYAuthor Commented:
I see the 10 % .............i must have missed that always looked at whether it was ticked to unrestricted file growth .....for the main erp databases...
Got it the size of file will have 10% used space....................when that 10% space has been used up it will then grew the database size to the size of the database + 10%.

So if i have 27 gig i work out 10% of 27 gigs ...........

If i check space free................and measure that each week............................when it is nearly used i can determine that the new file size will become 27 gigs * 10%

Then i can ensure the file server has enough space to accomodate it.

Does that sound right.........

0
 
TRACEYMARYAuthor Commented:
i ran the command
Select  Name, FileName, Growth,  Size from master..sysaltfiles
 Is there a way for me to see Growth ,  free , used
0
 
iloyaCommented:
See one way is to query the database itself using this statement
SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) <> 0

Another way is by modifying the sp_spaceused stored procedure in the master database. I have named the sp db_spaceused and you can pass database name as parameter  
It will give you the output in the following format
reserved           data               index_size         unused            
------------------ ------------------ ------------------ ------------------
736 KB             280 KB             304 KB             152 KB

alter procedure db_spaceused
@objname nvarchar(776) = null            -- The object we want size on.
as
declare @id      int                  -- The object id of @objname.
declare @type      character(2) -- The object type.
declare      @pages      int                  -- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15,0)
declare @bytesperpage      dec(15,0)
declare @pagesperMB            dec(15,0)

declare @database_name nvarchar(776)
declare @database_size varchar(30)
declare @unallocated_space varchar(30)

create table #spt_space
(
      rows            int null,
    database_name    nvarchar(776),
    database_size    varchar(30),
    unallocated_space varchar(30),
      reserved      dec(15) null,
      data            dec(15) null,
      indexp            dec(15) null,
      unused            dec(15) null
)

if @objname IS NOT NULL
begin
      select @dbname = parsename(@objname, 3)
      if @dbname is not null and @dbname <> db_name()
            begin
                  raiserror(15250,-1,-1)
                  return (1)
            end

      if @dbname is null
            select @dbname = db_name()

      /*
      **  Try to find the object.
      */
      select @id = null
      select @id = id, @type = xtype
            from sysobjects
                  where id = object_id(@objname)

      /*
      **  Does the object exist?
      */
      if @id is null
            begin
                  raiserror(15009,-1,-1,@objname,@dbname)
                  return (1)
            end


      if not exists (select * from sysindexes
                        where @id = id and indid < 2)

            if      @type in ('P ','D ','R ','TR','C ','RF') --data stored in sysprocedures
                        begin
                              raiserror(15234,-1,-1)
                              return (1)
                        end
            else if @type = 'V ' -- View => no physical data storage.
                        begin
                              raiserror(15235,-1,-1)
                              return (1)
                        end
            else if @type in ('PK','UQ') -- no physical data storage. --?!?! too many similar messages
                        begin
                              raiserror(15064,-1,-1)
                              return (1)
                        end
            else if @type = 'F ' -- FK => no physical data storage.
                        begin
                              raiserror(15275,-1,-1)
                              return (1)
                        end
end

/*
**  Update usages if user specified to do so.
*/

set nocount on

/*
**  If @id is null, then we want summary data.
*/
/*      Space used calculated in the following way
**      @dbsize = Pages used
**      @bytesperpage = d.low (where d = master.dbo.spt_values) is
**      the # of bytes per page when d.type = 'E' and
**      d.number = 1.
**      Size = @dbsize * d.low / (1048576 (OR 1 MB))
*/
if @id is null
begin
      select @dbsize = sum(convert(dec(15),size))
            from dbo.sysfiles
            where (status & 64 = 0)

      select @logsize = sum(convert(dec(15),size))
            from dbo.sysfiles
            where (status & 64 <> 0)

      select @bytesperpage = low
            from master.dbo.spt_values
            where number = 1
                  and type = 'E'
      select @pagesperMB = 1048576 / @bytesperpage

      select  @database_name = db_name(),
            @database_size =
                  ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),
            @unallocated_space =
                  ltrim(str((@dbsize -
                        (select sum(convert(dec(15),reserved))
                              from sysindexes
                                    where indid in (0, 1, 255)
                        )) / @pagesperMB,15,2)+ ' MB')

      /*
      **  Now calculate the summary data.
      **  reserved: sum(reserved) where indid in (0, 1, 255)
      */
      insert into #spt_space (database_name, database_size, unallocated_space, reserved)
            select @database_name, @database_size, @unallocated_space, sum(convert(dec(15),reserved))
                  from sysindexes
                        where indid in (0, 1, 255)

      /*
      ** data: sum(dpages) where indid < 2
      **      + sum(used) where indid = 255 (text)
      */
      select @pages = sum(convert(dec(15),dpages))
                  from sysindexes
                        where indid < 2
      select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
            from sysindexes
                  where indid = 255
      update #spt_space
            set data = @pages


      /* index: sum(used) where indid in (0, 1, 255) - data */
      update #spt_space
            set indexp = (select sum(convert(dec(15),used))
                        from sysindexes
                              where indid in (0, 1, 255))
                      - data

      /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
      update #spt_space
            set unused = reserved
                        - (select sum(convert(dec(15),used))
                              from sysindexes
                                    where indid in (0, 1, 255))

      select 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, master.dbo.spt_values d
            where d.number = 1
                  and d.type = 'E'
end

/*
**  We want a particular object.
*/
else
begin
      /*
      **  Now calculate the summary data.
      **  reserved: sum(reserved) where indid in (0, 1, 255)
      */
      insert into #spt_space (reserved)
            select sum(reserved)
                  from sysindexes
                        where indid in (0, 1, 255)
                              and id = @id

      /*
      ** data: sum(dpages) where indid < 2
      **      + sum(used) where indid = 255 (text)
      */
      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


      /* index: sum(used) where indid in (0, 1, 255) - data */
      update #spt_space
            set indexp = (select sum(used)
                        from sysindexes
                              where indid in (0, 1, 255)
                                    and id = @id)
                      - data

      /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
      update #spt_space
            set unused = reserved
                        - (select sum(used)
                              from sysindexes
                                    where indid in (0, 1, 255)
                                          and id = @id)
      update #spt_space
            set rows = i.rows
                  from sysindexes i
                        where i.indid < 2
                              and i.id = @id

      select name = object_name(@id),
            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, master.dbo.spt_values d
            where d.number = 1
                  and d.type = 'E'
end

return (0) -- sp_spaceused


0
 
imran_fastCommented:
<<file size will become 27 gigs * 10% >>
27 + 27 * 0.1 =  29.7 gb

why wont you increase you database be 512 mb instead of 10% that would be good.


/*run this on your current database and you will see the existing used space of the file*/
select * from dbo.sysfiles

the size column is calculated as (sized currently used by database in KB)/ 8
0
 
iloyaCommented:
The above sp needs some more modifications though but I guess you can use the idea and get whatever you want if you just go thru the code.
0
 
TRACEYMARYAuthor Commented:
I not sure on how much space is being used weekly........if i can work that out then i could set it to 512mb instead of 10% but i do not want to give 512 and it be greater than that.



0
 
TRACEYMARYAuthor Commented:
Let me run some commands over here..........and see

Thanks i appreciate your help
0
 
imran_fastCommented:
check this command as well
exec sp_spaceused
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.