Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Sybase Customized sp_thresholdaction

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Aerodynamic noise is the cause of the majority of the noise produced by helicopters. The inordinate amount of noise helicopters produce is a major problem in the both a military and civilian setting. To remedy this problem the use of an aerogel coat…
Following on from our article on "The Murky World of Consent and opt in", we thought we would issue some helpful guidance, not only on consent itself but knowing what information you are capturing, what you are doing with this data and how you can p…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

824 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