Re: Truncate and Shrink

From: Keyboard (anonymous_at_discussions.microsoft.com)
Date: 05/25/04 

Date: Mon, 24 May 2004 20:59:53 -0700

Before I did I BACKUP LOG ... with Truncate_Only the I
checked DBCC OPENTRAN and it said "No active
transaction.."

The Recovery model is set to as "Full". Full Backup is
scheduled every morning. Transaction Backup is set after
every hour.

Keyboard

>-----Original Message-----
>Did you run DBCC OPENTRAN? It doesn't just grow on its
own for the heck of
>it; your system is doing something at some point that is
requiring
>additional log space. Is it possible you might consider
running in simple
>or bulk-logged recovery if you are not planning to use
the transaction log
>for full recovery model? That way, it doesn't just grow
and grow between
>backups. Also, you could consider letting profiler run
in the background
>and send an alert when you hit an autogrow event, then
you can catch the
>system red-handed, so to speak, and determine what is
causing the growth
>events.
>
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Keyboard" <anonymous@discussions.microsoft.com> wrote
in message
>news:11b0a01c44206$54bc2570$a401280a@phx.gbl...
>> The size of the .ldf file was about 9 GB. The Log Space
>> used was 99%. Taking the full-database backup and after
>> BACKUP LOG ... with Truncate_Only the the Log Space
used
>> became 1%. We didn't shrink the size of the
transaction
>> log.
>>
>> After 15 days the size of .ldf became 11 GB and Log
Space
>> used was something above than 1%.
>>
>> Why is that when the space used is just about 1%, the
>> physical size of the .ldf increased in just 15 days.
>>
>> Keyboard..
>
>
>.
>

Relevant Pages

  • RE: Cannot shrink Transaction Log
    ... If you are using Full recovery model then you need to backup the transaction ... > the transaction logs have filled all available disk space. ...
    (microsoft.public.sqlserver.server)
  • Re: Log file wont Shrink in Simple Recovery Mode
    ... What does DBCC OPENTRAN() say? ... "Dave Harper" wrote in message ... As I stated the recovery model is set to simple so it ... I then tried a backup log... ...
    (microsoft.public.sqlserver.server)
  • Re: Ever increasing transaction log size
    ... database after the full backup occurs? ... update to the customer information which swells the transaction log to over ... > I have now tried dbcc opentran and nothing came up. ... >> You might try running a dbcc opentran against the problem database to see ...
    (microsoft.public.sqlserver.server)
  • Re: cut the size of the log
    ... SERVERPROPERTYEX, you can't shrink the log file ... unless you backup the transaction log or truncate it. ... SIMPLE recovery model, you can't shrink it much if there are open ... Need smaller SQL2K backup files? ...
    (microsoft.public.sqlserver.server)
  • Re: Regarding Transaction Log
    ... The T log file is growing with a greater speed. ... > All modifications are logged in the transaction log. ... > the log is truncated when you backup the log. ... database in simple recovery model, ...
    (microsoft.public.sqlserver.server)