backup Job

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_'+con vert(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..
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@.mailcity.com> wrote in message
news:er9RPR7WFHA.4032@.tk2msftngp13.phx.gbl...
>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_'+con vert(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..
>
>
|||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@.hotmail.com> wrote in message
news:eY6q3O8WFHA.3188@.TK2MSFTNGP09.phx.gbl...
> 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[vbcol=seagreen]
> 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@.mailcity.com> wrote in message
> news:er9RPR7WFHA.4032@.tk2msftngp13.phx.gbl...
''c:\Backup\FullBackup\Health_full_db_backup_'+con vert(varchar(20),getdate()
>
|||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@.mailcity.com> wrote in message
news:O1wr$p8WFHA.3876@.tk2msftngp13.phx.gbl...
> 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@.hotmail.com> wrote in message
> news:eY6q3O8WFHA.3188@.TK2MSFTNGP09.phx.gbl...
> log
> growing
> ''c:\Backup\FullBackup\Health_full_db_backup_'+con vert(varchar(20),getdate()
>
|||My DB size is 4 GB
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O7$ISb#WFHA.3840@.tk2msftngp13.phx.gbl...
> 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[vbcol=seagreen]
> 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@.mailcity.com> wrote in message
> news:O1wr$p8WFHA.3876@.tk2msftngp13.phx.gbl...
''c:\Backup\FullBackup\Health_full_db_backup_'+con vert(varchar(20),getdate()[vbcol=seagreen]
take[vbcol=seagreen]
strategy...
>
|||I am talking about your LDF file. Just ensure that your database files
should not grow contineously.
Thanks
Hari
SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:O4GO1l%23WFHA.1796@.TK2MSFTNGP15.phx.gbl...
> My DB size is 4 GB
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O7$ISb#WFHA.3840@.tk2msftngp13.phx.gbl...
> LDF
> file
> ''c:\Backup\FullBackup\Health_full_db_backup_'+con vert(varchar(20),getdate()
> take
> strategy...
>