Solved

Sybase Customized sp_thresholdaction

Posted on 2013-11-21
2
634 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
2 Comments
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 500 total points
Comment Utility
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
Comment Utility
Thanks Joe.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article explains in simple steps how to renew expiring Exchange Server Internal Transport Certificate.
Veeam Backup & Replication has added a new integration – Veeam Backup for Microsoft Office 365.  In this blog, we will discuss how you can benefit from Office 365 email backup with the Veeam’s new product and try to shed some light on the needs and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now