use sybsystemprocs
go
checkpoint
go
set nocount on
go
print 'Generic stored procedure used by Sybase Job Scheduler Template
jobs'
go
-- Remove the sp_jst_get_freespace if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_get_freespace'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_get_freespace
go
/*
** sp_jst_get_freespace
** Version: 3.0
**
** This version of this sproc is compatible with the ASE 15.0 release as
** it uses the new reserved_pages and data_pages builtin functions.
**
** This procedure calculates the current size of the specified database
** and the amount of space in use by that database and returns the
** difference as freespace.  The code here is taken from the spaceused
** stored procedures, which prints a report of similar data.
**
** This stored procedure were developed as part of the ASE Job Scheduler
** templates provided by Sybase. It must be installed on the target
** ASE prior to running jobs created from Sybase templates that
** specify its use.
**
** This stored proc is the called at job execution.  It is a support
** stored procedure and all values for it come from other stored
** procedures.
**
** Parameter definitions
** @db_name   - name of database to check for freespace
** @freespace - out parameter to hold the amount of freespace, in
**              KB, in the database
*/
create procedure sp_jst_get_freespace( @db_name varchar(30),
                                       @freespace int output )
as
begin

    print 'sp_jst_get_freespace( @db_name = %1! )', @db_name

    declare @sp_name varchar(30)
          , @slog_res_pgs numeric(20, 9)
          , @slog_dpgs numeric(20, 9)
          , @dbid int


    --initializations
    select @sp_name = 'sp_jst_get_freespace'
         , @dbid = db_id(@db_name)

    /*
    ** Obtain the page count for syslogs table.
    **
    ** The syslogs system table has only data (no index does exist).
    ** Built-in functions reserved_pages and data_pages will always
    ** return the same value for syslogs.
    ** 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_pages - data_pages.
    **
    ** Also note that syslogs table doesn't have OAM pages.  However,
    ** builtin functions reserved_pages() and data_pages() handle syslogs
    ** as a special case.
    */
    select @slog_res_pgs = convert(numeric(20, 9)
         , reserved_pages(@dbid,8) )
         , @slog_dpgs = convert(numeric(20, 9)
         , data_pages(@dbid,8) )

    select distinct s.id,
                    s.indid,
                    res_pgs=0,
                    dpgs = convert(numeric(20, 9), 0),
                    ipgs = convert(numeric(20, 9), 0),
                    unused = convert(numeric(20, 9), 0)
      into #pgcounts
      from sysindexes s
     where s.id != 8

    /* Calculate the reserved pages, data pages, index pages and
    ** unused pages. Note that we take care of the special case
    ** of indid = 1, 0 in later steps. For indid = 1 case we need
    ** to get the data pages and index pages in one step and the unused pages
    ** in another step.
    */
    update #pgcounts 
       set res_pgs = reserved_pages(@dbid, id, indid),
           ipgs = convert(numeric(20, 9), data_pages(@dbid, id, indid)),
           unused = convert( numeric(20, 9),
                             ( reserved_pages(@dbid, id, indid) -
                                          data_pages(@dbid, id, indid) ) ) 
     where indid > 1

    /* get data pages for indid = 0 */
    update #pgcounts 
       set res_pgs = reserved_pages(@dbid, id, indid),
           dpgs = convert(numeric(20, 9), data_pages( @dbid, id, indid)),
           unused = convert( numeric(20, 9),
                            ( reserved_pages(@dbid, id, indid) -
                                         data_pages(@dbid, id, indid) ) ) 
     where indid = 0

    /* For the clustered index case, calculate the data and reserved pages
    ** by passing in indid of 0 to the builtins. Note, for indid = 1
    ** the data pages are accounted for in ipgs.
    */
    update #pgcounts 
       set ipgs = ipgs + convert(numeric(20, 9), data_pages(@dbid, id, indid)),
           dpgs = dpgs + convert(numeric(20, 9), data_pages(@dbid, id, 0)),
           res_pgs = res_pgs + reserved_pages(@dbid, id, 0)
                             + reserved_pages(@dbid, id, indid)
     where indid = 1

    /* Calculate the unused count for the special case of indid  = 1 */
    update #pgcounts 
       set unused = convert(numeric(20, 9), (res_pgs - dpgs - ipgs))
     where indid = 1

    /*
    ** 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.
    */
    declare @unused int
    select @unused = sum(unused) * (@@maxpagesize/1024) from #pgcounts
    select @freespace = @unused 

    --debug stuff
    --print 'reserved data pages '
    --select (sum(res_pgs) + @slog_res_pgs) * (@@maxpagesize/1024) 'reserved space KB ' from #pgcounts
    --select sum(dpgs) * (@@maxpagesize/1024) 'data space KB ' from #pgcounts
    --select sum(ipgs) * (@@maxpagesize/1024) 'index space KB ' from #pgcounts
    --select sum(unused) * (@@maxpagesize/1024) 'unused space KB ' from #pgcounts
end
go
if exists( select 1 from sysobjects
           where name = 'sp_jst_get_freespace'
             and type = 'P'
             and (sysstat & 7) = 4 )
begin
    print 'sp_jst_get_freespace has been created'
    grant execute on sp_jst_get_freespace to public
end
go

