IF OBJECT_ID('dbo.sp_thresholdaction') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.sp_thresholdaction
    IF OBJECT_ID('dbo.sp_thresholdaction') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_thresholdaction >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_thresholdaction >>>'
END
go
create proc
---------------------------------------------------------------------------------
--
--  Copyright (c) 1993-2008 Software Gems Pty Ltd  +614 0615 8141
--
--  Derek Asirvadem  24 Sep Jun 08
--
---------------------------------------------------------------------------------
--  N O T    F O R   P R O D U C T I O N   U S E
---------------------------------------------------------------------------------
--  Assumes development db & truncates the log
--  you need to extend this proc before it can be used on a Production Db
---------------------------------------------------------------------------------
--
--  Stripped-down version of standard SG sp_thresholdaction for free distribution:
--      - MINUS dump log based on options
--      - MINUS error escalation
--      - MINUS SG tempdb spaceused monitoring
--  - Any seg: display execution (threshold/segment occurred & pages left)
--  - if logseg: truncate log & escalate to truncate with no_log
--
--------------------------------------------------------------------------------
--
--  sp_thresholdaction for Sybase SQL Server 12.0 to 15.0.x
--  For 11.0 servers, replace the lct_admin(free_pages)
--
--  This Server: 15.0.2 on Windoze
--
---------------------------------------------------------------------------------
    sp_thresholdaction
        @db_name       varchar(30),
        @segment_name  varchar(30),
        @space_left    int,                             -- reported space left (stated)
        @lct           int
as
    declare
    @thresh_type       char(3),
    @error             int,
    @logsegment        varchar(30),                     -- name of
    @segment           smallint,
    @alloc_pgs         int,                             -- seg allocation
    @free_pgs          int,                             -- seg actual unused pages
    @db_status         smallint,                        -- from sysdatabases
    @db_status2        int,
    @db_status3        int,
    @db_options        varchar(50)                      -- constructed list

INITIALISE:                                             -- Initialise ourselves

    if @lct = 1                                         -- frequently used
        select @thresh_type = "LCT"
    else

        select @thresh_type = "Thr"

    select @error = 0

    if @db_name = null
        select @db_name = db_name()

    select @db_name = rtrim(@db_name)                   -- pretty msgs
    select @segment_name = rtrim(@segment_name)

    select @segment = segment                           -- get segment number, validate
        from syssegments
        where name = @segment_name

    IF @segment = NULL
        BEGIN
        PRINT "%1! %2! %3! segment not found",
            @thresh_type, @db_name, @segment_name
        GOTO EXIT_ERR
        END

    select @logsegment = name                           -- get name of logsegment
        from syssegments
        where segment = 2

    -- get actual space left on whatever segment
    select  @alloc_pgs = isnull(sum(size),0),
            @free_pgs  = isnull(sum(curunreservedpgs(u.dbid,lstart,unreservedpgs) ), 0)
        from master..sysusages u,
            master..sysdevices v,
            syssegments s
        where u.vdevno = v.vdevno and
            v.status & 2 = 2 and
            u.dbid = db_id(@db_name) and
            u.segmap & power(2,s.segment) != 0 and
            s.segment = @segment
        group by v.name, s.segment

    IF (@segment_name = @logsegment)                    -- correct it if logsegment
        SET  @free_pgs = lct_admin("logsegment_freepages", db_id(@db_name))   -- actual

    select  @db_status  = status,                       -- get db options
            @db_status2 = status2,
            @db_status3 = status3
        from master..sysdatabases
        where name = @db_name

    if @db_status & 4 != 0                              -- build options str
        select @db_options = @db_options + " SI/BC/PS"
    if @db_status & 8 != 0
        select @db_options = @db_options + " TLC"
    if @db_status & 16 != 0
        select @db_options = @db_options + " NCR"
    if @db_status & 512 != 0
        select @db_options = @db_options + " DDL"
    if @db_status & 1024 != 0
        select @db_options = @db_options + " RO"
    if @db_status & 2048 != 0
        select @db_options = @db_options + " DBO"
    if @db_status & 4096 != 0
        select @db_options = @db_options + " SU"
    if @db_status & 8192 != 0
        select @db_options = @db_options + " Null"
    if @db_status2 & 1 != 0
        select @db_options = @db_options + " ATLF"
    if @db_status2 & 2 != 0
        select @db_options = @db_options + " NFSA"
    if @db_status2 & 4 != 0
        select @db_options = @db_options + " ID"
    if @db_status2 & 8 != 0
        select @db_options = @db_options + " INUI"
    if @db_status2 & 16 != 0
        select @db_options = @db_options + " Off"
    if @db_status2 & 32 != 0
        select @db_options = @db_options + " O-R"
    if @db_status2 & 32768 != 0
        select @db_options = @db_options + " Mix"
    if @db_status3 & 256 != 0
        select @db_options = @db_options + " Tmp"

    if @db_options != null
        select @db_options = "[" + ltrim(rtrim(@db_options)) + "]"
    else
        select @db_options = "[none]"

EXEC_UTE:                                               -- do what we came to do

    print "%1! ==================================================",
        @thresh_type

    print "%1! %2! db_options: %3! triggered at %4! pages",
        @thresh_type, @db_name, @db_options, @space_left

    if (@segment_name = @logsegment)                    -- if it is logsegment
        if (@db_status2 & 32768 = 0)                    -- if seg is discrete not mixed
            print "%1! %2! logsegment has %3! pages allocated, %4! pages free",
                @thresh_type, @db_name, @alloc_pgs, @free_pgs
        else                                            -- not separate
            print "%1! %2! logsegment (not separate) has %3! pages free",
                @thresh_type, @db_name, @free_pgs
    else                                                -- not logsegment
        print "%1! %2! %3! segment has %4! pages free",
            @thresh_type, @db_name, @segment_name, @free_pgs

    if (@segment_name != @logsegment)                   -- if not logsegment
        GOTO EXIT_OK

DUMP_TRAN:                                              -- dump tran properly

    ---------------------------------------------------
    -- Normal Database including Production --
    ---------------------------------------------------
    -- add your code (dump-device-specific or file-path db_name_YYMMDD_HHMM) here
    -- including escalation
    -- use proper error checking as per code block below
    -- goto EXIT_OK or EXIT_ERR
    ---------------------------------------------------

TRUNC_ONLY:                                             -- dump tran with trunc_only

    ---------------------------------------------------
    --  N O T    F O R   P R O D U C T I O N   U S E --
    ---------------------------------------------------

    dump tran @db_name with truncate_only

    select @error = @@error
    if @error = 0
        begin
        print "%1! %2! logfile truncated",
            @thresh_type, @db_name
        goto EXIT_OK
        end
    else
        begin
        print "%1! Error %2! on dump tran %3! with trunc_only",
            @thresh_type, @error, @db_name
        if @error = 4204                                -- we can escalate
            goto TRUNC_NO_LOG
        goto EXIT_ERR
        end

TRUNC_NO_LOG:                                           -- dump tran with no_log

    dump tran @db_name with no_log

    select @error = @@error
    if @error = 0
        begin
        print "%1! %2! logfile truncated with no_log",
        @thresh_type, @db_name
        goto EXIT_OK
        end
    else
        begin
        print "%1! Error %2! on dump tran %3! with no_log",
            @thresh_type, @error, @db_name
        goto EXIT_ERR
        end

EXIT_OK:

    IF (@segment_name = @logsegment)                    -- correct it if logsegment
        BEGIN
        SET  @free_pgs = lct_admin("logsegment_freepages", db_id(@db_name))   -- actual
        PRINT "%1! %2! logsegment now has %3! pages free",
            @thresh_type, @db_name, @free_pgs
        END

    print "%1! --------------------------------------------------",
        @thresh_type

    return 0                                            -- get the frog out of here

EXIT_ERR:

    print "%1! --------------------------------------------------",
        @thresh_type

    return 1
                                            -- tell caller we failed
go
EXEC sp_procxmode 'dbo.sp_thresholdaction','unchained'
go
IF OBJECT_ID('dbo.sp_thresholdaction') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.sp_thresholdaction >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_thresholdaction >>>'
go
