Link to home
Start Free TrialLog in
Avatar of motioneye
motioneyeFlag for Singapore

asked on

Sybase Customized sp_thresholdaction

We have a customized script below for. I wonder if the script below also doing a log dump for several db we have in the same instance ? for example it define dbccdb and sybsecurity to perform dum transaction with truncate only. But how do I add few more db to do same dum_transaction with truncate_only in this procedure

create procedure sp_thresholdaction
   @dbname                varchar(30),
   @segmentname           varchar(30),
   @space_left            int,
   @status                int
as
declare @devname            varchar(100),
        @before_size        int,

        @after_size         int,
        @dumpfilename       varchar(100) ,
        @logdumpname        varchar(100) ,
        @after_time         datetime,
        @before_time        datetime,
        @atime              char(10),
        @btime
      char(10),
        @error              int,
        @nextslash          int,
        @dev                varchar(100),
        @dumpdir            varchar(100),
        @cuttime            datetime,
        @audit_table_number int

/*************************************************************
 *************
**************************************************************************/

/*
*/

select @before_time = getdate()
select @dumpfilename = "XXX_1_SDS" + @dbname + ".tran." +
                       convert(char(6), @before_time, 12)
 +
                       substring(convert(char(6), @before_time, 8),1,2) +
          substring(convert(char(8), @before_time, 8),4,2) +
                       substring(convert(char(8), @before_time, 8),7,2) +
                       "--THRESHOLD"

select @logdumpname = @dbname + ".tran." +
                       convert(char(6), @before_time, 12) +
                       substring(convert(char(6), @before_time, 8),1,2) +
                       substring(convert(char(8), @before_time, 8),4,2) +

                substring(convert(char(8), @before_time, 8),7,2) +
            "--THRESHOLD"

/*
** If the segment is 2 in syssegments it is the logsegment, dump the log
** otherwise it is a data segment, print a message
*/

if exists (select * from syssegments where segment != 2 and name = @segmentname)
begin
   print "%1!: Threshold Warning: Segment '%2!' has reached '%3!' pages free"
   , @logdumpname, @segmentname, @space_left

   if @dbname = "dbccdb"
   begin
      print 'Truncating dbccdb due to threshold'
      dump tran dbccdb with truncate_only
      select @cuttime = dateadd(dd,-9,getdate())
      execute sp_dbcc_deletehistory  @cuttime
      dump tran dbccdb with truncate_only
      select @cuttime = dateadd(dd,-6,getdate())
      execute sp_
 dbcc_deletehistory  @cuttime
      dump tran dbccdb with truncate_only
      select @cuttime = dateadd(dd,-3,getdate())
      execute sp_dbcc_deletehistory  @cuttime
      dump tran dbccdb with truncate_only
      select @cuttime = dateadd(dd,-1,getdate()
 )
      execute sp_dbcc_deletehistory  @cuttime
   end
/*
   if @dbname = "sybsecurity"
   begin
      select @audit_table_number = value
      from master.dbo.sysconfigures
      where name = "current audit table"

      if @audit_table_number = 1
        tru
 ncate table sysaudits_01
      if @audit_table_number = 2
        truncate table sysaudits_02
      if @audit_table_number = 3
        truncate table sysaudits_03
   end
*/
end
else
begin

   if (@space_left > 0)
      print "%1!: Logspace left in database '%2!'", @log
 dumpname, @space_left

   /*
   ** Get the log size just before the dump starts
   */

   select @before_size = reserved_pgs(id,doampg)
   from sysindexes
   where name = "syslogs"

   /* Try AUTO = DBNAMEdump */

   select @devname = phyname
   from master..sysdevices
   where name = @dbname + "dump"
   select @error= @@rowcount - 1

   if (@error = 0)
   begin
      /* All these rows to get the dumpdirectory */
      select @dev = @devname
      select @nextslash = 0
      select @nextslash = @nexts
 lash + charindex("/", @dev)
      while ((select charindex("/", @dev)) != 0)
      begin
         select @dumpdir = substring (@devname, 1, @nextslash - 1)
         select @dev = substring (@devname, @nextslash + 1, char_length(@dev))
         select @ne
 xtslash = @nextslash + charindex("/", @dev)
      end

      /* Get dumpdevice, dump to dumpdevice, print if went wrong */
      select @devname = @dumpdir + "/" + @logdumpname
      dump transaction @dbname to @devname
      select @error=@@error
      i
 f (@error != 0)
         print "%1!: Failed to dump to '%2!'", @logdumpname, @devname
   end

   /* Try THRESHOLDDUMP */

   if (@error != 0)
   begin

      select @devname = phyname
      from master..sysdevices
      where name = "thresholddump"

     select @error= @@rowcount - 1

      if @error = 0
      begin
         /* All these rows to get the dumpdirectory */
         select @dev = @devname
         select @nextslash = 0
         select @nextslash = @nextslash + charindex("/", @dev)

   while ((select charindex("/", @dev)) != 0)
         begin
            select @dumpdir = substring (@devname, 1, @nextslash - 1)
            select @dev = substring (@devname, @nextslash + 1, char_length(@dev))
            select @nextslash = @nextslash
  + charindex("/", @dev)
         end

      /* Get dumpdevice, dump to dumpdevice, print if went wrong */
         select @devname = @dumpdir + "/" + @logdumpname
         dump transaction @dbname to @devname
         select @error=@@error
         if (@error !=
  0)
            print "%1!: Failed to dump to '%2!'", @logdumpname, @devname
      end
   end

   /* Try DEFAULTDUMP */

   if (@error != 0)
   begin

      select @devname = phyname
      from master..sysdevices
      where name = "defaultdump"
      s
 elect @error= @@rowcount - 1

      if @error = 0
      begin
         /* All these rows to get the dumpdirectory */
         select @dev = @devname
         select @nextslash = 0
         select @nextslash = @nextslash + charindex("/", @dev)
         while ((select charindex
 ("/", @dev)) != 0)
         begin
            select @dumpdir = substring (@devname, 1, @nextslash - 1)
            select @dev = substring (@devname, @nextslash + 1, char_length(@dev))
            select @nextslash = @nextslash + charindex("/", @dev)
         end

         /* Get dumpdevice, dump to dumpdevice, print if went wrong */
         select @devname = @dumpdir + "/" + @logdumpname
         dump transaction @dbname to @devname
         select @error=@@error
         if (@error != 0)
            print "%1!: Failed to dump to '%2!'", @
 logdumpname, @devname
      end
   end

   /* Try DPDUMP */

   if (@error != 0)
   begin

      select @devname = phyname
      from master..sysdevices
      where name = "dp_dump"
      select @error= @@rowcount - 1

      if @error = 0
      begin

      dump transaction @dbname to @devname
         select @error=@@error
         if (@error != 0)
            print "%1!: Failed to dump to '%2!'", @logdumpname, @devname
      end
   end

   /* Try BACKUPDUMP */

   if (@error != 0)
   begin

      sele
 ct @devname = phyname
      from master..sysdevices
      where name = "backupdump"
      select @error= @@rowcount - 1

      if @error = 0
      begin
         /* All these rows to get the dumpdirectory */
         select @dev = @devname
         select @nextsla
 sh = 0
         select @nextslash = @nextslash + charindex("/", @dev)
         while ((select charindex("/", @dev)) != 0)
         begin
            select @dumpdir = substring (@devname, 1, @nextslash - 1)
            select @dev = substring (@devname, @nextslash + 1, char_length(@dev
 ))
            select @nextslash = @nextslash + charindex("/", @dev)
         end

         /* Get dumpdevice, dump to dumpdevice, print if went wrong */
         select @devname = @dumpdir + "/" + @logdumpname
         dump transaction @dbname to @devname
         sel
 ect @error=@@error
         if (@error != 0)
            print "%1!: Failed to dump to '%2!'", @logdumpname, @devname
      end
   end

   /* Truncate log and write what failed */

   if (@error != 0)
   begin
      select @devname = "waste basket"

 dump transaction @dbname with truncate_only
      select @error=@@error
      if (@error != 0)
         print "%1!: Log Dump Error : Could not truncate with truncate only", @logdumpname
      else
         print "%1!: Log Dump Error : Truncated log with t
 runcate only", @logdumpname
   end

   if (@error != 0)
   begin
      print "%1!: Log Dump Error : Truncating log with no_log since syslogs full", @logdumpname
      dump transaction @dbname with no_log
      select @error=@@error
      if (@error != 0)

       begin
         print "%1!: Log Dump Error : Failed truncating log with no_log", @logdumpname
      end
   end

   if (@error != 0)
   begin
      print "%1!: Log Dump Error : Could not dump transaction log", @logdumpname
   end

   /* Error check
 ing */

   select @error = @@error
   if @error != 0
   begin
      print "%1!: Log Dump Error '%2!'", @logdumpname, @error
   end

   /* Get size of log and time after dump */

   select @after_time = getdate(),
          @after_size = reserved_pgs (id,
 doampg)
   from sysindexes
   where name = "syslogs"

   /* Print messages to error log */

   print "%1!: Log dumped to device '%2!'", @logdumpname, @devname

   print "%1!: Log dump sizes: Before: '%2!', After '%3!'",
   @logdumpname, @before_siz
 e, @after_size

   select @btime = convert(char(10), @before_time, 8)
   select @atime = convert(char(10), @after_time, 8)

   print "%1!: Log dump times: %2!, %3!", @logdumpname, @btime, @atime

end /* End of 'if segment = 2' section */



(39 rows affected)
(return status = 0)
1>

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Joe Woodhouse
Joe Woodhouse

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of motioneye

ASKER

Thanks Joe.