Re: how to shrink the .LDF ? -- spanish subtitled

Hi.

1.
DBCC SHRINKFILE is not a truncate command. This command actually shrink the
physical MDF or LDF files based on the contgeous free space in the file.
Commmand to truncate the Log is BACKUP LOG command.

Note:

Before executing DBCCSHRINKFILE; Issue the BACKUP LOG command to clear the
Transaction log.

2) what is the recomended size for the Log

That depends on the number of transaction in your system, amount of
batch/bulk process. As a Thump rule for a OLTP system you can create
the LDF between 20 - 30% of the MDF.
Still ensure that you do a frequent transaction log backup based on the
volume of transaction.

Thanks
Hari
SQL Server MVP


"bajopalabra" <bajopalabra@xxxxxxxxxxx> wrote in message
news:Oy5%231iqnFHA.3540@xxxxxxxxxxxxxxxxxxxxxxx
> ok
> go to read about BACKUP LOG (sql2000)
>
> 2 more questions
>
> 1) is this right for truncation ??
> Dbcc ShrinkFile( 'MyDB_Log', 100) Truncate
>
> 2) what is the recomended size for the Log
> (what percent over de Data file)
>
> thanks
>
> --
> atte, Hernán
>
> "Kalen Delaney" <replies@xxxxxxxxxxxxxxxxxxxxx> escribió en el mensaje
> news:uFfrsYpnFHA.2472@xxxxxxxxxxxxxxxxxxxxxxx
>> Hernan
>>
>> DUMP TRAN has been deprecated and is no longer suggested for versions
> after
>> SQL 7. Since you refer to your .ldf file, I assume you have either
>> version
> 7
>> or 2000. In the future, please always tell us the exact version you are
>> using.
>>
>> The completed transactions in the log need to marked as no longer needed.
>> You can do this by either backing up the log, or simply truncating it.
>> The
>> BACKUP LOG command will work for either. Please read about the details
>> of
>> BACKUP LOG in the Books Online.
>>
>> I presume that by 'fail', the other posters mean that the .ldf does not
>> actually shrink. Sometimes, depending on your version (7 or 2000) you
>> have
>> to run this sequence of commands more than once.
>>
>> 1. Backup or truncate
>> 2. shrinkfile
>>
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>>
>> "bajopalabra" <bajopalabra@xxxxxxxxxxx> wrote in message
>> news:OybIjEpnFHA.860@xxxxxxxxxxxxxxxxxxxxxxx
>> > why , for example
>> > dbcc would fail ?
>> >
>> > ------------------
>> > spanish: por qué fallaría el dbcc ?
>> > qué cosa podría hacerlo fallar ?
>> >
>> > --
>> > atte, Hernán
>> >
>> > "Mauro" <msbrizuela@xxxxxxxxx> escribió en el mensaje
>> > news:uAEfimonFHA.3288@xxxxxxxxxxxxxxxxxxxxxxx
>> >> HERNAN, si falla el dbcc utiliza
>> >> dump tran [dbname] with no_log
>> >> y luego de nuevo el dbcc
>> >>
>> >> if dbcc fails, use
>> >> dump tran [dbname] with no_log
>> >> then again dbcc
>> >>
>> >> "bajopalabra" <bajopalabra@xxxxxxxxxxx> wrote in message
>> >> news:uPRuBconFHA.1088@xxxxxxxxxxxxxxxxxxxxxxx
>> >> > how can i view the contents of the Ldf ?
>> >> >
>> >> > do i have to use TRUNCATE / NOTRUNCATE ??
>> >> >
>> >> > dbcc shrinkfile( 'MyDB_Log', 10)
>> >> >
>> >> > why to do a backup ?
>> >> >
>> >> > dbcc shrinkfile may fail ?
>> >> >
>> >> > --
>> >> > atte, Hernán
>> >> >
>> >> > "Steen Persson (DK)" <spe@xxxxxxxxxxxxxx> escribió en el mensaje
>> >> > news:OjoL0xnnFHA.1444@xxxxxxxxxxxxxxxxxxxxxxx
>> >> > > Hi
>> >> > >
>> >> > > First you'll have to backup your logfile and then run DBCC
> SHRINKFILE
>> >> > > (YourLogicalFileName).
>> >> > >
>> >> > > You can look up the exact syntax in Books On Line.
>> >> > >
>> >> > > Regards
>> >> > > Steen
>> >> > >
>> >> > > bajopalabra wrote:
>> >> > > > hi
>> >> > > > i need to reduce the size of the log file LDF
>> >> > > > it has currently more than 1 GB
>> >> > > > i need to empty it and then set a limit
>> >> > > > i know where i have to go to limit the size
>> >> > > > but i don't know how to reduce the log
>> >> > > > thanks
>> >> > >
>> >> > >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>


.

Relevant Pages

  • Re: how to shrink the .LDF ? -- spanish subtitled
    ... Dbcc ShrinkFile('MyDB_Log', 100) Truncate ... > BACKUP LOG command will work for either. ...
    (microsoft.public.sqlserver.setup)
  • Re: Transaction Log Shrink
    ... > Backup with truncate only command, and then again the Shrinkfile ...
    (microsoft.public.sqlserver.msde)
  • Re: what is the command to cut down the size of log file
    ... See books online for more information on this ... command. ... If you need to truncate the log (not the same as reducing ... backup the log using backup log which is also ...
    (microsoft.public.sqlserver.server)
  • Re: [Info-Ingres] commit question
    ... The next block has been turned into a rollback ... hence the next command executed is the rollback and not the commit. ... The situation we had was that the "modify to truncate" command succeeded ...
    (comp.databases.ingres)
  • Re: best "drop table if exists" idiom?
    ... drop and creates and replace the create with a truncate. ... The temporary table method is best when multiple user sessions might ... owner that performs the truncate command via 'execute immediate' (PL/ ...
    (comp.databases.oracle.misc)