Solved

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

Posted on 2002-04-22
20
761 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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
 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Data source name not found , no default driver specified. ODBC, Excel and SqlAnywhere 3 2,614
How to query constraints in Sybase 3 1,814
SQL Query 2 348
SYBASE ASE HA Configuration 8 417
While it may be true that the internet is a place of possibilities, it is also a hostile environment lurking with many dangers. By clicking on the wrong link, trusting the wrong person or using a weak password, you are virtually inviting hackers to …
Always backup Domain, SYSVOL etc.using processes according to Microsoft Best Practices. This is meant as a disaster recovery process for small environments that did not implement backup processes and did not run a secondary domain controller that ne…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

756 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