Solved

Sybase Customized sp_thresholdaction

Posted on 2013-11-21
2
701 Views
Last Modified: 2013-12-12
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

0
Comment
Question by:motioneye
[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
2 Comments
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 500 total points
ID: 39670926
You could hard code it with more clauses like the one for dbccdb, but I think at that point you might be better off having a table somewhere which defines which databases get which sort of dump tran.
0
 

Author Closing Comment

by:motioneye
ID: 39715713
Thanks Joe.
0

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

This article was originally published on Monitis Blog, you can check it  here . If you have responsibility for software in production, I bet you’d like to know more about it. I don’t mean that you’d like an extra peek into the bowels of the sour…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…

730 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