Re: backup Job

Hi Joh,

Even if you do a transaction log backup there are chances to expand the LDF
file. This can happend due to a batch operation. 20 MB is just a
small growth. Even if you do transaction log backup the grown physical file
will not be shrinked automatically but the space used inside the file will
be cleared
and can be used for new transactions.

One more thing is do not shrink the LDF to 1MB. Always keep 100 MB minimum
size. Otherwise the LDF FILE will grow on each transaction and will take
additional overhead for file growth.

Thanks
Hari
SQL Server MVP



"Joh" <joh@xxxxxxxxxxxx> wrote in message
news:O1wr$p8WFHA.3876@xxxxxxxxxxxxxxxxxxxxxxx
> Hari, I am agree with you with but when I check my log file size after 2
> days through this sp_helpdb command. It shows 20 MB but when I shrink it
> then it comes at 1 MB.
>
> Any idea ?
>
> "Hari Prasad" <hari_prasad_k@xxxxxxxxxxx> wrote in message
> news:eY6q3O8WFHA.3188@xxxxxxxxxxxxxxxxxxxxxxx
>> Hi,
>>
>> Why do you want to shrik the file every time? If you take a transaction
> log
>> backup the LDF file will never grow. Incase if you find the file is
> growing
>> then
>> increase the log backup frequency to 30 minutes rather than 1 hour.
>>
>> Doing the transaction log backup will clear the transaction log file
>> automatically. This will ensure that the LDF file will not grow.
>>
>> Thanks
>> Hari
>> SQL Server MVP
>>
>> "Joh" <joh@xxxxxxxxxxxx> wrote in message
>> news:er9RPR7WFHA.4032@xxxxxxxxxxxxxxxxxxxxxxx
>> >I have created two jobs..
>> >
>> > First Job... take the full backup daily at 12:00 AM
>> > Second Job...take the log backup every 1 hour till 11:PM
>> >
>> > The question is that if I include log shrinking command in the first
>> > job
>> > like
>> >
>> > First Job :-
>> >
>> > Step 1:-
>> > Declare @SQLStatement VARCHAR(1000)
>> > SET @SQLStatement = 'Backup Database Health to Disk =
>> >
> ''c:\Backup\FullBackup\Health_full_db_backup_'+convert(varchar(20),getdate()
>> > ,110)+'.BAK'' with init'
>> > EXEC (@SQLStatement)
>> >
>> > Step 2:-
>> > backup log Health with truncate_only
>> > dbcc shrinkfile('Health_log',truncateonly)
>> >
>> > Is Step 2 would impact on the second job like log backup would be take
>> > place
>> > after every 1 hour... is that okie or I have to change my strategy....
>> > I want to shrink my log file daily.....
>> >
>> > Please guide me..
>> >
>> >
>> >
>>
>>
>
>


.

Relevant Pages

  • Re: Just to confirm
    ... If you are taking the Transaction log backup every one hour, ... This is because the LDF file willl be ... If you have a big LDF FILE you could shrink the file after the transaction ...
    (microsoft.public.sqlserver.clients)
  • Re: Just to confirm
    ... > If you are taking the Transaction log backup every one hour, ... > required to shrink the LDF file. ... This is because the LDF file willl be ... > each transaction log backup. ...
    (microsoft.public.sqlserver.clients)
  • RE: Log shipping on Secondary cannot load large log file
    ... The filename for the transaction log backup does not change through the ... > Thank you for using Microsoft newsgroups. ...
    (microsoft.public.sqlserver.server)
  • Re: Restoring File Backups
    ... Now you can restore the full backup to a new database with NORECOVERY ... AFter the restore of FULL backup rectore the transaction log backup took ...
    (microsoft.public.sqlserver.server)
  • Re: Database Maintenance Plan
    ... To clarify, though, the .BAK file created every night in this plan is a fully restorable DATA and LOG file from that moment - right? ... I like the idea of the hourly transaction log backup - does that mean that I still keep with this one maintenance plan, but have the TRANSACTION LOG part run hourly? ...
    (microsoft.public.sqlserver.server)
Loading