Stored Procedure to return free space (KB) in a Database ?

Hi,

I need to write a stored procedure to calculate the free space remaining (as a percent %) of a Database.

SO I need to .....
a> Size of the DB (to be used for data) i.e DB Size - log.
b> Reserved do far.

(b % a) * 100 = %left in DB.

Cheers,
Brian.
brianonAsked:
Who is Participating?
 
amitpagarwalConnect With a Mentor Commented:

It was not a store proc, it was normal sql.

You can create the store proc as

create procedure mystoredproc as
begin
declare @used_pgs float
declare @pct_used float
declare @db_size  float
declare @scale  float /* for overflow */

set nocount on

select @db_size = sum(size)
       from master.dbo.sysusages u
               where u.dbid = db_id()
               and   u.segmap != 4

select id,doampg,ioampg into #t from sysindexes

select @used_pgs = sum(reserved_pgs(id, doampg) + reserved_pgs(id, ioampg))
from #t
where id != 8

drop table #t

/* @scale is number way to convert from pages to K  */
/* for example -> normally 2K page size so @scale=2 and multipled results */
select  @scale=d.low/1024
from    master.dbo.spt_values d
where   d.number = 1 and d.type = "E"
having  d.number = 1 and d.type = "E"

select @pct_used=(@used_pgs*100)/@db_size

select  Name             = convert(char(12),db_name()),
       "Data MB"  = str((@db_size*@scale)/1024, 13, 0),
       "Used MB"  = str((@used_pgs*@scale)/1024, 14, 1),
       Percent    = str(@pct_used, 7, 2)
end
0
 
amitpagarwalCommented:

you can use the standard sp library of sybase.

sp_helpdb "dbname"

and

sp_spaceused
0
 
brianonAuthor Commented:
I've tried to use these but I'm doing this in a C program using 'ctlib' and they are causing to much hassle (its getting complicated messy).

How could I write my own Stored Procedure to just return a single value that is the % free ?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
amitpagarwalCommented:
ok that should be simple.

you may look into the code of the above proc in sybsystemproc

just say

sp_helptext "sp_spaceused" ..

so get an idea from teh code.

HTH
0
 
brianonAuthor Commented:
when I try this (sp_helptext 'sp_spaceused') I get .......

Msg 17461, Level 16, State 1
Procedure 'sp_helptext', line 48:
Object does not exist in this database.

???
0
 
amitpagarwalCommented:
are you in the sybsystemprocsd database ?

use sybsystemprocs
go
sp_helptext sp_spaceused
go
0
 
amitpagarwalCommented:
are you in the sybsystemprocsd database ?

use sybsystemprocs
go
sp_helptext sp_spaceused
go
0
 
amitpagarwalCommented:
brian - any luck ?
0
 
brianonAuthor Commented:
I'll let you know in the  morning when I'm back in work :)
0
 
brianonAuthor Commented:
ok, now I've looked .... and they make no sense to me :(
0
 
brianonAuthor Commented:
I've now decided to just use sp_spaceused.
The app will know the database size(data part).

Using sp_spaceused how would I calculate the percentage of the database used ?
??( reserved / database size(data only) ) * 100 ?????
0
 
brianonAuthor Commented:
Would it be possible to find out the size in KB of Reserved that is being used by rows in the tables of the DB and NOT by the rows + logs ?
0
 
amitpagarwalCommented:
I have done a defncopy of the sp text ( in case you don't have defncopy) and pasted the text here. You can just modify the select statment in the last to get the results you want.

create procedure sp_spaceused
@objname varchar(92) = null,            /* the object we want size on */
@list_indices int = 0                  /* don't sum all indices, list each */
as

declare @type      smallint            /* the object type */
declare @msg      varchar(250)            /* message output */
declare @dbname varchar(30)             /* database name */
declare @tabname varchar(30)            /* table name */
declare @length      int


if @@trancount = 0
begin
      set chained off
end

set transaction isolation level 1

/*
**  Check to see that the objname is local.
*/
if @objname is not null
begin
      /*
        ** Get the dbname and ensure that the object is in the
        ** current database. Also get the table name - this is later
        ** needed to see if information is being requested for syslogs.
        */
        execute sp_namecrack @objname,
                             @db = @dbname output,
                             @object = @tabname output
        if @dbname is not NULL
      begin
            /*
            ** 17460, "Object must be in the current database."
            */
            if (@dbname != db_name())
            begin
                  raiserror 17460
                  return (1)
            end
      end

      /*
      **  Does the object exist?
      */
      if not exists (select *
                        from sysobjects
                                where id = object_id(@objname))
      begin
            /*
            ** 17461, "Object does not exist in this database."
            */
            raiserror 17461
            return (1)
      end

      /* Get the object type */
        select @type = sysstat & 7
                from sysobjects
                        where id = object_id(@objname)
      /*
      **  See if it's a space object.
      **  types are:
      **      0 - trigger
      **      1 - system table
      **      2 - view
      **      3 - user table
      **      4 - sproc
      **      6 - default
      **      7 - rule
      */
      if not exists (select *
                  from sysindexes
                        where id = object_id(@objname)
                              and indid < 2)
      begin
            if @type in (0, 4, 6, 7)
            begin
                  /*
                  ** 17830, "Object is stored in 'sysprocedures' and
                  **          has no space allocated directly."
                  */
                  raiserror 17830
                  return (1)
            end

            if @type = 2
            begin
                  /*
                  ** 17831, "Views don't have space allocated."
                  */
                  raiserror 17831
                  return (1)
            end
      end

end

/*
**  If @objname is null, then we want summary data.
*/
set nocount on
if @objname is null
begin
      declare @slog_res_pgs numeric(20, 9),        /* number of reserved pgs. in syslogs */
            @slog_dpgs numeric(20, 9)       /* number of data pages in syslogs */
      
      select distinct database_name = db_name(), database_size =
            ltrim(str(sum(size) / (1048576 / d.low), 10, 1)) + " MB"
            from master.dbo.sysusages, master.dbo.spt_values d
                  where dbid = db_id()
                        and d.number = 1
                        and d.type = "E"
                  having dbid = db_id()
                        and d.number = 1
                        and d.type = "E"

      /*
      ** Obtain the page count for syslogs table.
      **
      ** The syslogs system table has only data (no index does exist).
      ** Built-in functions reserved_pgs(8, doampg) and data_pgs(8, doampg)
      ** will always return the same value.  This is due to the fact that
      ** syslogs pages are allocated an extent worth at a time and all log
      ** pages in this extent are set as in use.  This is why we aren't able
      ** to determine the amount of unused syslogs pages by simply doing
      ** reserved_pgs - data_pgs.
      **
      ** Also note that syslogs table doesn't have OAM pages.  However,
      ** builtin functions reserved_pgs() and data_pgs() handle syslogs
      ** as a special case.
      */
      declare @doampg int
      select @doampg = doampg from sysindexes where id = 8
      select @slog_res_pgs = convert(numeric(20, 9), reserved_pgs(8, @doampg)),
             @slog_dpgs = convert(numeric(20, 9), data_pgs(8, @doampg))

      /*
      ** Obtain the page count for all the objects in the current
      ** database; except for 'syslogs' (id = 8). Store the results
      ** in a temp. table (#pgcounts).
      **
      ** Note that we first retrieve the needed information from
      ** sysindexes and we only then apply the OAM builtin system
      ** functions on that data.  The reason being we want to relax
      ** keeping the sh_int table lock on sysindexes for the duration
      ** of the command.
      */
      select distinct
            s.name,
            s.id,
            res_pgs = 0,
            low = d.low,
            dpgs = convert(numeric(20, 9), s.doampg),
            ipgs = convert(numeric(20, 9), s.ioampg),
            unused = convert(numeric(20, 9), 0)
      into #pgcounts
      from sysindexes s, master.dbo.spt_values d
            where s.id != 8
                  and d.number = 1
                  and d.type = "E"
            having d.number = 1
                  and d.type = "E"

      update #pgcounts set
            res_pgs = reserved_pgs(id, dpgs) + reserved_pgs(id, ipgs),
            dpgs = convert(numeric(20, 9), data_pgs(id, dpgs)),
            ipgs = convert(numeric(20, 9), data_pgs(id, ipgs)),
            unused = convert(numeric(20, 9),
                    (reserved_pgs(id, dpgs) + reserved_pgs(id, ipgs)) -
                       (data_pgs(id, dpgs) + data_pgs(id, ipgs)))

      /*
      ** Compute the summary results by adding page counts from
      ** individual data objects. Add to the count the count of
      ** pages for 'syslogs'.  Convert the total pages to space
      ** used in Kilo bytes.
      */
      select distinct reserved = convert(char(15), convert(varchar(11),
            convert(numeric(11, 0), (sum(res_pgs) + @slog_res_pgs) *
                  (low / 1024))) + " " + "KB"),
            data = convert(char(15), convert(varchar(11),
                  convert(numeric(11, 0), (sum(dpgs) + @slog_dpgs) *
                  (low / 1024))) + " " + "KB"),
            index_size = convert(char(15), convert(varchar(11),
                  convert(numeric(11, 0),  sum(ipgs) * (low / 1024)))
                  + " " + "KB"),
            unused = convert(char(15), convert(varchar(11),
                  convert(numeric(11, 0), sum(unused) * (low / 1024)))
                  + " " + "KB")
      from #pgcounts
end

/*
**  We want a particular object.
*/
else
begin
      if (@tabname = "syslogs") /* syslogs */
      begin
            /*
            ** 17832, "Not avail."
            */
            exec sp_getmessage 17832, @msg output

            select @length = max(datalength(o.name))
                from sysobjects o, sysindexes i
                  where i.id = object_id(@objname)
                        and o.id = object_id(@objname)

            if (@length > 20)
                select name = o.name,
                  rowtotal = convert(char(11), @msg),
                  reserved = convert(char(15), convert(varchar(11),
                        convert(numeric(11, 0), convert(numeric(20, 9),
                        reserved_pgs(i.id, i.doampg) * (d.low / 1024))))
                        + " " + "KB"),
                  data = convert(char(15), convert(varchar(11),
                          convert(numeric(11, 0), convert(numeric(20, 9),
                        data_pgs(i.id, i.doampg) * (d.low / 1024))))
                        + " " + "KB"),
                  index_size = convert(char(15), convert(varchar(11), 0)
                          + " " + "KB"),
                  unused = convert(char(11), @msg)
                  from sysobjects o, sysindexes i,
                       master.dbo.spt_values d
                        where i.id = object_id(@objname)
                              and o.id = object_id(@objname)
                              and d.number = 1
                              and d.type = "E"
            else
                select name = convert(char(20), o.name),
                  rowtotal = convert(char(10), @msg),
                  reserved = convert(char(15), convert(varchar(11),
                        convert(numeric(11, 0), convert(numeric(20, 9),
                        reserved_pgs(i.id, i.doampg) * (d.low / 1024))))
                        + " " + "KB"),
                  data = convert(char(15), convert(varchar(11),
                          convert(numeric(11, 0), convert(numeric(20, 9),
                        data_pgs(i.id, i.doampg) * (d.low / 1024))))
                        + " " + "KB"),
                  index_size = convert(char(15), convert(varchar(11), 0)
                          + " " + "KB"),
                  unused = convert(char(11), @msg)
                  from sysobjects o, sysindexes i,
                       master.dbo.spt_values d
                        where i.id = object_id(@objname)
                              and o.id = object_id(@objname)
                              and d.number = 1
                              and d.type = "E"
      end
      else
      begin
            select  name = o.name,
                  iname = i.name,
                  low = d.low,
                  rowtotal = rowcnt(i.doampg),
                  reserved = convert(numeric(20, 9),
                        (reserved_pgs(i.id, i.doampg) +
                        reserved_pgs(i.id, i.ioampg))),
                  data = convert(numeric(20, 9), data_pgs(i.id, i.doampg)),
                  index_size =  convert(numeric(20, 9),
                        data_pgs(i.id, i.ioampg)),
                  unused = convert(numeric(20, 9),
                        ((reserved_pgs(i.id, i.doampg) +
                           reserved_pgs(i.id, i.ioampg)) -
                           (data_pgs(i.id, i.doampg) +
                             data_pgs(i.id, i.ioampg))))
            into #pagecounts
            from sysobjects o, sysindexes i, master.dbo.spt_values d
                        where i.id = object_id(@objname)
                              and o.id = object_id(@objname)
                              and d.number = 1
                              and d.type = "E"

          if (@list_indices = 1)
          begin
            select @length = max(datalength(iname))
                  from #pagecounts
            if (@length > 20)
                    select  index_name = iname,
                        size = convert(char(10), convert(varchar(11),
                                          convert(numeric(11, 0),
                                          index_size / 1024 *
                                            low)) + " " + "KB"),
                            reserved = convert(char(10),
                                 convert(varchar(11),
                                               convert(numeric(11, 0),
                                       reserved / 1024 *
                                              low)) + " " + "KB"),
                            unused = convert(char(10), convert(varchar(11),
                                    convert(numeric(11, 0), unused / 1024 *
                                          low)) + " " + "KB")
                    from #pagecounts
            else
                  select  index_name = convert(char(20), iname),
                        size = convert(char(10), convert(varchar(11),
                                          convert(numeric(11, 0),
                                          index_size / 1024 *
                                            low)) + " " + "KB"),
                            reserved = convert(char(10),
                                 convert(varchar(11),
                                               convert(numeric(11, 0),
                                       reserved / 1024 *
                                              low)) + " " + "KB"),
                            unused = convert(char(10), convert(varchar(11),
                                    convert(numeric(11, 0), unused / 1024 *
                                          low)) + " " + "KB")
                    from #pagecounts

          end

          select @length = max(datalength(name))
            from #pagecounts

          if (@length > 20)
              select distinct name,
                rowtotal = convert(char(11), sum(rowtotal)),
                reserved = convert(char(15), convert(varchar(11),
                             convert(numeric(11, 0), sum(reserved) *
                         (low / 1024))) + " " + "KB"),
                data = convert(char(15), convert(varchar(11),
                             convert(numeric(11, 0), sum(data) * (low / 1024)))
                         + " " + "KB"),
                index_size = convert(char(15), convert(varchar(11),
                            convert(numeric(11, 0), sum(index_size) *
                        (low / 1024))) + " " + "KB"),
                unused = convert(char(15), convert(varchar(11),
                            convert(numeric(11, 0), sum(unused) *
                        (low / 1024))) + " " + "KB")
              from #pagecounts
          else
              select distinct name = convert(char(20), name),
                rowtotal = convert(char(11), sum(rowtotal)),
                reserved = convert(char(15), convert(varchar(11),
                             convert(numeric(11, 0), sum(reserved) *
                         (low / 1024))) + " " + "KB"),
                data = convert(char(15), convert(varchar(11),
                             convert(numeric(11, 0), sum(data) * (low / 1024)))
                         + " " + "KB"),
                index_size = convert(char(15), convert(varchar(11),
                            convert(numeric(11, 0), sum(index_size) *
                        (low / 1024))) + " " + "KB"),
                unused = convert(char(15), convert(varchar(11),
                            convert(numeric(11, 0), sum(unused) *
                        (low / 1024))) + " " + "KB")
              from #pagecounts
      end
end
0
 
brianonAuthor Commented:
'reserved' is the amount of space used in the DB right ?
But this includes the Logs right ?

Well, I need to know what is reserved by the data (rows in the tables) only.

Sorry, I should have made that clear.
0
 
amitpagarwalCommented:
do u wnat to find the space taken by a table ..

then just say

sp_spaceused "tableName"

or to get a detailed view

sp_spaceused "tableName", 1
0
 
brianonAuthor Commented:
I need to know what space is used up in a Database.
However, I need to be able to calculate what of the space used is because of the data in the tables and not because of the logs.
0
 
amitpagarwalCommented:

I think I have the solution for you now.


declare @used_pgs float
declare @pct_used float
declare @db_size  float
declare @scale  float /* for overflow */
 
set nocount on
 
select @db_size = sum(size)
        from master.dbo.sysusages u
                where u.dbid = db_id()
                and   u.segmap != 4
 
select id,doampg,ioampg into #t from sysindexes
 
select @used_pgs = sum(reserved_pgs(id, doampg) + reserved_pgs(id, ioampg))
from #t
where id != 8
 
drop table #t
 
/* @scale is number way to convert from pages to K  */
/* for example -> normally 2K page size so @scale=2 and multipled results */
select  @scale=d.low/1024
from    master.dbo.spt_values d
where   d.number = 1 and d.type = "E"
having  d.number = 1 and d.type = "E"
 
select @pct_used=(@used_pgs*100)/@db_size
 
select  Name             = convert(char(12),db_name()),
        "Data MB"  = str((@db_size*@scale)/1024, 13, 0),
        "Used MB"  = str((@used_pgs*@scale)/1024, 14, 1),
        Percent    = str(@pct_used, 7, 2)
0
 
brianonAuthor Commented:
I don't know much about creating stored procedures(syntax etc...).
Is this stored proc u give above ok to create exactly as is ? or is it psuedo ?
0
 
brianonAuthor Commented:
Thats great.
Thanx for your help.
0
 
amitpagarwalCommented:
oh finally...
great .. please close this question
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.