Transaction log problems

From: Peter The Spate (anonymous_at_discussions.microsoft.com)
Date: 09/29/04 

Date: Wed, 29 Sep 2004 08:50:25 -0700

Hello Robert,

Have a look at the link
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;272318, which maybe able to help.

Here is an idea though, just before you backup the
database (not log file) change the recovery model to
Simple, shrink the log file then change it back to Full,
note that its you will not be able to backup the log file
with anything useful until you do your next backup of the
database.

Have a look at
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_bkprst_4l83.asp to see what
a recovery model does.

Peter

"You can always count on Americans to do the right thing -
after they've tried everything else."
Winston Churchill

>-----Original Message-----
>We are using SQL Server 2000 on Windows Server 2003
Standard Edition, all
>updates applied.
>
>We use this server to host schema databases for a third
party business
>intelligence platform.
>
>One of the databases has recently begun showing large
growth of the
>transaction log and it is causing us some problems.
NOTHING has changed
>which would cause this problem. The data loads are
consistent and have not
>been changed at all.
>
>Because the database is used by the third party software,
one of the edicts
>is that the database must be set to FULL recovery mode so
I can't change it
>to SIMPLE. The database is set to autogrow. The
transaction log is also set
>to autogrow and restricted to 1 GB. I do NOT have
autoshrink turned on
>because so many of the MVP's say it causes too much
overhead and
>fragmentation.
>
>We used the database maintenance wizard to create a
backup plan that backed
>up the database and the transaction log once daily--and
that had worked
>successfully up until 2 weeks ago. At that point the
transaction log began
>filling up rapidly forcing us to use the command BACKUP
LOG <db> WITH
>TRUNCATE_ONLY and subsequently command DBCC SHRINKFILE
(<db_log>, 20) in order
>for the backup to run successfully. Sometimes, this has
to be done several
>times per day.
>
>So, today, I changed the database maintenance plan to
backup the transaction
>log every hour thinking that it would automatically
truncate and shrink the
>transaction log file and I would not have to monitor it
so closely. It runs
>successfully but immediately after it finishes, I check
the size of the file
>and it has grown(!) and not shrunk at all.
>
>I have run DBCC OPENTRAN and no open transactions are
reported.
>
>I have run DBCC LOGFILE and received 167 rows of data
that I do not
>understand.
>
>sp_helpfile shows only one data file and one log file.
>
>I want this to stop and go back to the way it was 2 weeks
ago when
>everything worked and I didn't have to monitor it all day
long.
>
>I should mention that I have no training in SQL SERVER
just what I have
>learned as I go along. And there will be no training
forthcoming. I am
>familiar with the term stored procedure and think that I
could write one but
>I have no idea WHERE to store it or how to invoke it
and/or schedule it. If
>I did, I would be happy to schedule the TRUNCATE+SHRINK
commands to run every
>5 minutes.
>
>Can anyone shed some light on this situation for me or
point me toward a
>source for some information? The Books On Line just
really suck IMHO and I
>have difficulty learning anything from them. I have
already searched thru
>the other posts concerning the transaction logs and
viewed the web sites that
>people recommended but they just don't make sense to me
(they assume that the
>DBA's are fully Microsoft certified--not the case here).
Can you sense the
>frustration level?
>
>Any information and assistance is greatly appreciated.
>
>Regards,
>Robert
>
>.
>

Relevant Pages

  • Re: Large Transaction Log Backup after Database Backup
    ... script and see for yourself that the database backup doesn't truncate the ... ALTER DATABASE steve SET RECOVERY FULL ... But my understanding was that the transaction log was> truncated after a full database backup. ...
    (microsoft.public.sqlserver.server)
  • Re: Still dont understand log backups
    ... The transaction log, whether or not it is one or many files, is a serial, ... until the end of the physical log file is reached. ... backup is executed, the active "virtual" logs are backed up, but the ... Using BULK LOGGED, does the online log file contain EVERY transaction for all time -- given that I don't truncate it manually? ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction Log Size
    ... Backup database does not empty the transaction log files. ... How to Shrink the SQL Server 7.0 Transaction Log ...
    (microsoft.public.sqlserver.server)
  • Disaster Averted?
    ... Some process, unknown at this point, flooded a transaction log to the point ... the .mdf file was 0 bytes according to EM. ... Following the backup, I ... the database was now Suspect! ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction Log Size
    ... We currently use a 3rd party backup agent to backup our SQL databases and ... transaction logs (Commvault Galaxy iDataAgent for SQL) and the database is ... Because we are using a 3rd party agent to backup the database, ... > here are some articles on how to shrink your transaction log. ...
    (microsoft.public.sqlserver.server)
Loading