?
Solved

Checking space

Posted on 2006-04-15
14
Medium Priority
?
422 Views
Last Modified: 2008-02-07
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
0
Comment
Question by:TRACEYMARY
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 600 total points
ID: 16461940
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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16461954
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16461968
>>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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:iloya
ID: 16463060
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
 
LVL 28

Expert Comment

by:imran_fast
ID: 16463075
<<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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16463849
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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16463875
i ran the command
Select  Name, FileName, Growth,  Size from master..sysaltfiles
 Is there a way for me to see Growth ,  free , used
0
 
LVL 2

Assisted Solution

by:iloya
iloya earned 600 total points
ID: 16464408
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
 
LVL 28

Expert Comment

by:imran_fast
ID: 16464458
<<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
 
LVL 2

Expert Comment

by:iloya
ID: 16464620
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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16468238
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
 
LVL 28

Accepted Solution

by:
imran_fast earned 800 total points
ID: 16468380
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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16468958
Let me run some commands over here..........and see

Thanks i appreciate your help
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16476637
check this command as well
exec sp_spaceused
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question