Re: Transaction log problems

From: Keith Kratochvil (sqlguy.back2u_at_comcast.net)
Date: 09/29/04 

Date: Wed, 29 Sep 2004 11:06:29 -0500

Books Online are a great resource. There is lots of information in
there....the difficult part (especially for someone new to SQL Server) might
be finding all the information that they need.

Yeah, autoshrink is generally not a good idea. I have watched autoshrink
block a process from running. IF you have to shrink a database or file it
is generally better that you control the "when" so that it does not impact
anything that might start running..

> one of the edicts is that the database must be set to FULL recovery mode <
FULL recovery mode is usually a good idea for production databases so that
transaction log backups can be taken throughout the day and you can use them
to recover (should you have a problem).

>I would be happy to schedule the TRUNCATE+SHRINK commands to run every
5 minutes. <

What is causing your logs to grow? Do they grow during the day (because of
insert/update/delete) statements or do they grow at night because of
"optimizations" that you might be doing within your maintenance plan?

How big is your database? Until you can figure out what might be causing
the logs to grow (and if the large log files are causing a problem) you
might be able to set the recovery model to simple AND backup your database
every <?> (fill in a time period that you feel comfortable with) and make
sure that you get that database backup on tape or to another machine. 

-- 
Keith
"Robert" <Robert@discussions.microsoft.com> wrote in message
news:D610F0FC-920E-4E22-AB55-98E1BFC92A74@microsoft.com...
> 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: Backups have Shadow Copy Problems
    ... with volume Shadow Copy error 800423f4. ... You back up data from a volume that contains a Microsoft SQL Server ... The recovery model of the SQL Server database is configured to use an ... It just ensures backup will continue without reporting the error. ...
    (microsoft.public.windows.server.sbs)
  • 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: 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: backup advise
    ... When I run a backup of a database, does it automatically clean out the ... No. Backing up the database and backing up the transaction log are two ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)