Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Dumping logs in threshold and cron to filesystem

6 posts in Backup and Recovery Last posting was on 2001-04-02 23:53:37.0Z
Justin_Vallon Posted on 2001-03-28 17:12:01.0Z
From: Justin_Vallon
Date: Wed, 28 Mar 2001 13:12:01 -0400
Newsgroups: sybase.public.sqlserver.backup+recovery
Subject: Dumping logs in threshold and cron to filesystem
Message-ID: <EE97F6AF33EA804A005E7C0285256A1D.005E7C1185256A1D@webforums>
Lines: 26
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.backup+recovery:355
Article PK: 1060872

I am trying to get tran log dumping working via thresholds. At the
moment, we are using cron (periodic) dumping. I believe this is
unsatisfactory because it does not handle high-load conditions well.

The problem is that I am not sure how to implement a threshold sp
and cron job sp that are without race conditions. For example, if
you use "tranlog-HHMMSS", then the cron job (1:00pm) and threshold
(50%) could both fire simultaneously. Hopefully, one would not
overwrite the other (though the dumps would be serialized).

Worse, the date-stamp does not strictly imply the order of the dumps.
One could get a stamp of "dump-130000" and the other "dump-130001".
The "130001" dump could start before the "130000" dump. The dumps
would then be out of sequence.

Modification time of the log is not helpful, since a small dump
following (blocked by) a large dump would probably have the same
modification time.

What would be nice is a sequence-number string in the filename.
For example, "db-tranlog-%s".

Unfortunately, it seems that you cannot append to a file (only a
tape).

Any tips?


Anthony Mandic <amandic Posted on 2001-03-29 04:18:55.0Z
Date: Thu, 29 Mar 2001 14:18:55 +1000
From: Anthony Mandic <amandic@*t.com.au>
Organization: Mandic Consulting Pty. Ltd.
X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.14-5.0 i686)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Dumping logs in threshold and cron to filesystem
References: <EE97F6AF33EA804A005E7C0285256A1D.005E7C1185256A1D@webforums>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <hGRygmAuAHA.169@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.backup+recovery
Lines: 17
NNTP-Posting-Host: 210.9.51.46
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.backup+recovery:350
Article PK: 1060867


Justin_Vallon wrote:
>
> I am trying to get tran log dumping working via thresholds. At the
> moment, we are using cron (periodic) dumping. I believe this is
> unsatisfactory because it does not handle high-load conditions well.
>
> The problem is that I am not sure how to implement a threshold sp
> and cron job sp that are without race conditions. For example, if
> you use "tranlog-HHMMSS", then the cron job (1:00pm) and threshold
> (50%) could both fire simultaneously. Hopefully, one would not
> overwrite the other (though the dumps would be serialized).

There is no race condition. You can only do one dump at a
time.

-am © 2001


Jeff Schlaver Posted on 2001-03-28 18:15:19.0Z
Reply-To: "Jeff Schlaver" <jeff.schlaver@starzeencore.com>
From: "Jeff Schlaver" <jeff.schlaver@starzeencore.com>
References: <EE97F6AF33EA804A005E7C0285256A1D.005E7C1185256A1D@webforums>
Subject: Re: Dumping logs in threshold and cron to filesystem
Date: Wed, 28 Mar 2001 11:15:19 -0700
Lines: 16
Organization: Starz Encore Media
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
Message-ID: <eVM2tW7tAHA.169@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.backup+recovery
NNTP-Posting-Host: 204.97.85.2
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.backup+recovery:353
Article PK: 1060869


<Justin_Vallon> wrote in message >
> What would be nice is a sequence-number string in the filename.
> For example, "db-tranlog-%s".
>

Ok had another thought ....
Create a dump table with an identity column, datetime, dbname.
Before the dump is to happen insert the datetime and dbname into this table,
then select out the identity number and append that to the file name.
Identity columns by definition is a sequential number that can not be
duplicated, & and precision of 10 digits.
If you peruse this I would recommend setting the identity burn to a low
number like 10. So if the server crashes you don't lose a block of numbers.


Justin_Vallon Posted on 2001-03-28 19:08:27.0Z
From: Justin_Vallon
Date: Wed, 28 Mar 2001 15:08:27 -0400
Newsgroups: sybase.public.sqlserver.backup+recovery
Subject: Re: Dumping logs in threshold and cron to filesystem
Message-ID: <EB51BAD37017F661006924CC85256A1D.0068940D85256A1D@webforums>
References: <EE97F6AF33EA804A005E7C0285256A1D.005E7C1185256A1D@webforums> <eVM2tW7tAHA.169@forums.sybase.com>
Lines: 18
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.backup+recovery:352
Article PK: 1060871

The problem with not dumping via cron is that you could lose
an indefinite period of time if the server crashes. For
example, on an empty log, I make a change. I do nothing
else for a day. The transaction log will not be dumped.

If I need to failover to a standby (updated via tran logs),
the standby is missing the last tran log. In the average case,
if my threshold fires at N%, I would be missing N/2% of the
most recent transactions. That might be an entire day of
activity (depending on the threshold setting) if the database
has a low transaction rate.

I still think there are race conditions in the procedure that
you outline. Even if we get unique file names (including the
sequence number), your check for "DUMP" commands still has a
race condition, where each sees no "DUMP" command executing,
but then both start the dump. You would then not know which
sequence number won the race.


"Mark A. Parsons" <pegasys Posted on 2001-04-02 23:53:37.0Z
Message-ID: <3AC91100.83E0017D@_internet.co.nz>
Date: Tue, 03 Apr 2001 11:53:37 +1200
From: "Mark A. Parsons" <pegasys@_internet.co.nz>
Organization: Pegasys (2000) Limited
X-Mailer: Mozilla 4.76 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Dumping logs in threshold and cron to filesystem
References: <EE97F6AF33EA804A005E7C0285256A1D.005E7C1185256A1D@webforums> <eVM2tW7tAHA.169@forums.sybase.com> <EB51BAD37017F661006924CC85256A1D.0068940D85256A1D@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.backup+recovery
Lines: 85
NNTP-Posting-Host: ip-210-48-25-181.asiaonline.net.nz 210.48.25.181
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.backup+recovery:344
Article PK: 1060861

I've implemented thresholds and cron job dumps on the same database.
The keys to remember are:

1) include a date/time stamp in the dump file name to make it unique
2) before each process (threshold/cron job) fires first verify that
another dump is not currently taking place in the database, if there is
another dump in process, then exit this process ... logging a message as
necessary/required ... possibly rescheduling this process (if it's a
cron job) for a later date/time.

Anthony is correct that only one dump can take place at a time. And
you're correct that you could get a 'race' condition (ie, confusion) in
terms of the naming of the resultant dump files if multiple dumps are
kicked off at nearly the same time.

'course, once you've gotten this logic worked out ... you then need to
decide how to handle the situation where the 'first' dump command is
hung (not going anywhere) ... or what happens if the log is not being
truncated after each dump (ie, the dump files are getting bigger and
bigger, the log is filling up, etc.) ... etc. ... lots of what-if's to
address makes the DBA's life un-boring! <g>

Oh, what I ended up doing was:

1) defined cron jobs to kick off at every 15 minutes (pick an interval
in which you're sure the log won't fill up in; may need to increase log
size)
2) defining multiple thresholds (see Jeff's post), JUST IN CASE #1 isn't
good enough, which dumped messages to the errorlog; a cron job
periodically scanned the errorlog for threshold messages and would
page/notify production support if there were any issues (eg, if the
second/third threshold fired, or any threshold firing didn't see an
appreciable reduction in log size, then there's a good chance of a
problem)
3) additional logic was added to each of the jobs (cron and threshold)
to check for 'old' (eg, greater than 30 minutes) transactions in
syslogshold; with the appropriate warning message being sent to the
errorlog and/or production support folks

The whole idea was to put in several trip wires by which a problem could
be recognized before it became a major problem ... while at the same
time allowing me to to address regular backups as well as the need for
backups in the case of 'surges'.

Justin_Vallon wrote:

> The problem with not dumping via cron is that you could lose
> an indefinite period of time if the server crashes. For
> example, on an empty log, I make a change. I do nothing
> else for a day. The transaction log will not be dumped.
>
> If I need to failover to a standby (updated via tran logs),
> the standby is missing the last tran log. In the average case,
> if my threshold fires at N%, I would be missing N/2% of the
> most recent transactions. That might be an entire day of
> activity (depending on the threshold setting) if the database
> has a low transaction rate.
>
> I still think there are race conditions in the procedure that
> you outline. Even if we get unique file names (including the
> sequence number), your check for "DUMP" commands still has a
> race condition, where each sees no "DUMP" command executing,
> but then both start the dump. You would then not know which
> sequence number won the race.

--
Mark A. Parsons

Iron Horse, Inc. iron_horse@compuserve.com
Pegasys (2000), Ltd pegasys@internet.co.nz


Jeff Schlaver Posted on 2001-03-28 18:02:15.0Z
Reply-To: "Jeff Schlaver" <jeff.schlaver@starzeencore.com>
From: "Jeff Schlaver" <jeff.schlaver@starzeencore.com>
References: <EE97F6AF33EA804A005E7C0285256A1D.005E7C1185256A1D@webforums>
Subject: Re: Dumping logs in threshold and cron to filesystem
Date: Wed, 28 Mar 2001 11:02:15 -0700
Lines: 55
Organization: Starz Encore Media
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
Message-ID: <tR1TbP7tAHA.47@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.backup+recovery
NNTP-Posting-Host: 204.97.85.2
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.backup+recovery:354
Article PK: 1060870

I basically had the same problem you are asking about, when I started at
this position.

First if you implement an sp_threshold action you don't need the cron to
dump the logs. I removed the cron job that dumped the logs and build 3
threshold action the first fires off 45% free space left, then 30% free
left, 15% free left, and the system provided last chance. Now I don't have
to worry about the race conditions. I also added a check to see if a dump
was in progress. If it was I did not start another one.
if exists
(select 1 from master..sysprocesses where cmd like 'DUMP%'
and dbid = db_id(@dbname))
begin
print "%1! dump already in progress", @dbname
return
end

Have not had any problemw for a couple of years now.

Note:
As for naming the log dump if you don't provide a name it will generate a
name. When you dump a database or transaction log, Backup Server creates a
default file name for the dump by concatenating the:

a.. Last 7 characters of the database name
b.. 2-digit year number
c.. 3-digit day of the year (1-366)
d.. Number of seconds since midnight, in hexadecimal
You can override this default using the file = file_name option. The file
name cannot exceed 17 characters and must conform to the file naming
conventions for your operating system. (I don't buy this because my file
name is 30 characters)

You can specify a file name for each dump device. You can also specify a
file name for all devices in the with clause. File names specified for
individual devices take precedence over those specified in the with clause.
The following examples dump the transaction log for the publications
database without specifying a file name. The default file name,
cations930590E100, identifies the database and the date and time the dump
was made:

cations = las 7 chars' of publications
93 = 2 digit year
095 = day of the year
0E100 = number of seconds since midnight.



<http://manuals.sybase.com/onlinebooks/group-as/asg1200e/asesag/@Generic__Bo
okView;pt=76616?DwebQuery=seconds+since+midnight>