Solved

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

Posted on 2002-04-22
20
754 Views
Last Modified: 2007-11-27
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.
0
Comment
Question by:brianon
  • 10
  • 10
20 Comments
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6960192

you can use the standard sp library of sybase.

sp_helpdb "dbname"

and

sp_spaceused
0
 

Author Comment

by:brianon
ID: 6960249
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
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6960270
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
 

Author Comment

by:brianon
ID: 6960313
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
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6960320
are you in the sybsystemprocsd database ?

use sybsystemprocs
go
sp_helptext sp_spaceused
go
0
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6960337
are you in the sybsystemprocsd database ?

use sybsystemprocs
go
sp_helptext sp_spaceused
go
0
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6960984
brian - any luck ?
0
 

Author Comment

by:brianon
ID: 6961079
I'll let you know in the  morning when I'm back in work :)
0
 

Author Comment

by:brianon
ID: 6962242
ok, now I've looked .... and they make no sense to me :(
0
 

Author Comment

by:brianon
ID: 6962932
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:brianon
ID: 6962995
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
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6963126
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
 

Author Comment

by:brianon
ID: 6963153
'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
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6963160
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
 

Author Comment

by:brianon
ID: 6963172
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
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6963266

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
 

Author Comment

by:brianon
ID: 6963288
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
 
LVL 5

Accepted Solution

by:
amitpagarwal earned 180 total points
ID: 6963296

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
 

Author Comment

by:brianon
ID: 6963405
Thats great.
Thanx for your help.
0
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6963412
oh finally...
great .. please close this question
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Advantage DB snapshots, replication or mirroring 2 409
detecting a database behind a website 3 358
sybase optimizer statistics 2 33
sybase 3 26
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now