Recovery Model backup

I'm currently performing a database backup at noon and 8pm
every evening, along with a transacation log backup at
10am and 2pm. My Recoery Model is set to "Full" and have
realized that the transaction log has every transaction
ever done against the database, getting pretty large.
Considering the backups that I'm doing, is it better to
set the Recovery Model to "Simple" and set the "autogrow"
and "autoshrink" on. I'm not sure what my target size
should be if I perform a dbcc shrinkfile operation, so
autoshrink seems the right thing to do? I need a little
advice on this on.
Thank you,
RandyRandy,
> Considering the backups that I'm doing, is it better to
> set the Recovery Model to "Simple" and set the "autogrow"
> and "autoshrink" on.
Simple recovery model means you cannot take transaction log backups.Also,
the transaction log file would get 'truncated' (emptied not shrinked) after
a log backup.In cases where you have only the database backups, Well, the
database backups just reflect the database when the backup was done.What
about the transactions that were for the interval between 8pm and next noon?
For complete data recovery to the point in time, you need transaction log
backups.
Autogrow should be always on even when you have given considerable space
beforehand itself to the database files.Autoshrink should be OFF for a
production system as this may consume valuable resources during peak time.
>>so
> autoshrink seems the right thing to do?
No for reasons said above.Why do you want to shrink the transaction log
files when its anyways going to grow for the OLTP traffic?Monitor the .ldf
growth for a period of time and in case you find that the transaction log
never crosses x mb/gb, then go ahead and do a DBCC SHRINKFILE .You may find
the below articles useful...
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/default.aspx?scid=kb;EN-US;110139
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/support/kb/Articles/q272/3/18.asp
INF: Considerations for Autogrow and Autoshrink Configuration
http://www.support.microsoft.com/?id=315512
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Rand Monroe" <randmo@.ix.netcom.com> wrote in message
news:00a001c35790$c3fed1f0$a601280a@.phx.gbl...
> I'm currently performing a database backup at noon and 8pm
> every evening, along with a transacation log backup at
> 10am and 2pm. My Recoery Model is set to "Full" and have
> realized that the transaction log has every transaction
> ever done against the database, getting pretty large.
> Considering the backups that I'm doing, is it better to
> set the Recovery Model to "Simple" and set the "autogrow"
> and "autoshrink" on. I'm not sure what my target size
> should be if I perform a dbcc shrinkfile operation, so
> autoshrink seems the right thing to do? I need a little
> advice on this on.
> Thank you,
> Randy