ADSM-L

Re: TDP SQL Transaction log not truncating

2004-04-06 11:10:31
Subject: Re: TDP SQL Transaction log not truncating
From: Joe Crnjanski <JCrnjanski AT INFINITYNETWORK DOT COM>
To: ADSM-L AT VM.MARIST DOT EDU
Date: Tue, 6 Apr 2004 11:09:44 -0400
I don't have a SQL doc. That was pure experience. And I'm not DBA.
First we thought that we can do : reindex, full, log, log, log...
But still full and first log backup were same size. After first log backup , 
second and every other log backup was "smaller" .
So doesn't really matter where you place full in your time sequence. The rule 
is: after reindex first log backup will be "huge", even if you have full before 
or after reindex.

And again; this is only experience. I was browsing through help files, but 
couldn't find anything. I don't have any offical source. If you need more 
explanation, you can call me; I'm in the office today, (I got tired of writing)

Regards,

Joe Crnjanski
Infinity Network Solutions Inc.
Phone: 416-235-0931 x26
Fax:     416-235-0265
Web:  www.infinitynetwork.com



-----Original Message-----
From: David Longo [mailto:David.Longo AT HEALTH-FIRST DOT ORG]
Sent: Tuesday, April 06, 2004 10:41 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: TDP SQL Transaction log not truncating


Can you reference me an MS SQL doc or something on this?  My
SQL DBA's are kind of anti-TSM, so I need as much justification
on this as I can get to give to them.

(If this is the  way it works and I dio a FULL daily at 3 am and log
backups every hour, then if they do "reindex" at say midnight,
then log shouldn't grow much before FULL runs at 3am, if I
understand correctly?)

Thanks,

David B. Longo
System Administrator
Health First, Inc.
3300 Fiske Blvd.
Rockledge, FL 32955-4305
PH      321.434.5536
Pager  321.634.8230
Fax:    321.434.5509
david.longo AT health-first DOT org


>>> JCrnjanski AT INFINITYNETWORK DOT COM 04/05/04 05:53PM >>>
I remember having same problem; and yes you are right.
Even if you are doing incremental sql backup; if in between two incremental 
backups you run "reindex", next incremental will be similar in size as full 
backup. Logically this is still incremental only it is too big.
We ended up running reindex weekly ( we run daily incremental and weekly full)

Regards,


Joe Crnjanski
Infinity Network Solutions Inc.
Phone: 416-235-0931 x26
Fax:     416-235-0265
Web:  www.infinitynetwork.com 



-----Original Message-----
From: David Longo [mailto:David.Longo AT HEALTH-FIRST DOT ORG] 
Sent: Monday, April 05, 2004 5:38 PM
To: ADSM-L AT VM.MARIST DOT EDU 
Subject: TDP SQL Transaction log not truncating


I have TSM server 4.2.3.4 on AIX and two clients each running W2K, SP2.

The two clients have SQL server and I use TDP SQL for hot backups
and do log backups of one of the DB's every hour or two.  A FULL
backup of all DB's is done once a day.  I use the sqlfull.smp script
with minimum change for our environment.  Basically:
tdpsql backup dbname log ......

DBA reports that at sporadic times, the log is not truncating after the
log backup and therefore transaction log is getting big.  I see no
errors in tdpsql logs.  Only event DBA could reference to is that 
problem seems to happen after a "reindex" but not always.

I know that "/Truncate" is the default, but I actually added this parameter
to the SQL 2000 system and still has problem at times.

Versions of two systems:

1.  SQL 7.0  SP3;      TSM BA 4.2.1.20;   TDP SQL 2.2.0.01
2.  SQL 2000 SP3a;  TSM BA 5.1.5.0;     TDP SQL 5.1.5.0

I have checked Tivoli site and not found anything.

Any ideas?

Thanks,


David B. Longo
System Administrator
Health First, Inc.
3300 Fiske Blvd.
Rockledge, FL 32955-4305
PH      321.434.5536
Pager  321.634.8230
Fax:    321.434.5509
david.longo AT health-first DOT org 


##############################################################
This message is for the named person's use only.  It may 
contain confidential, proprietary, or legally privileged 
information.  No confidentiality or privilege is waived or 
lost by any mistransmission.  If you receive this message 
in error, please immediately delete it and all copies of it 
from your system, destroy any hard copies of it, and notify 
the sender.  You must not, directly or indirectly, use, 
disclose, distribute, print, or copy any part of this message
if you are not the intended recipient.  Health First reserves
the right to monitor all e-mail communications through its
networks.  Any views or opinions expressed in this message
are solely those of the individual sender, except (1) where
the message states such views or opinions are on behalf of 
a particular entity;  and (2) the sender is authorized by 
the entity to give such views or opinions.
##############################################################

##############################################################
This message is for the named person's use only.  It may 
contain confidential, proprietary, or legally privileged 
information.  No confidentiality or privilege is waived or 
lost by any mistransmission.  If you receive this message 
in error, please immediately delete it and all copies of it 
from your system, destroy any hard copies of it, and notify 
the sender.  You must not, directly or indirectly, use, 
disclose, distribute, print, or copy any part of this message
if you are not the intended recipient.  Health First reserves
the right to monitor all e-mail communications through its
networks.  Any views or opinions expressed in this message
are solely those of the individual sender, except (1) where
the message states such views or opinions are on behalf of 
a particular entity;  and (2) the sender is authorized by 
the entity to give such views or opinions.
##############################################################