Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sybase Customized sp_thresholdaction

Posted on 2013-11-21
2
Medium Priority
?
756 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 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Are you an Exchange administrator employed with an organization? And, have you encountered a corrupt Exchange database due to which you are not able to open its EDB file. This article will explain all the steps to repair corrupt Exchange database.
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

715 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