Questions on simple recovery model

Hello. What is the rational behind not allowing a transaction log to be
backed up on a DB that is using the simple recovery model and how does
xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
mode?
As we know if left unchecked logs can become huge even in simple recovery
mode. They still need backups. For that matter if they're not deemed reliable
for point in time recovery why do they even exist? We run a reporting system
with most activity at night. We're happy to use differential backups to get
us back to yesterday. Why can't the simple model not use logs at all and not
make us deal with their growth spurts? TIA
Ken Trock1) There is no benefit to backing up a tlog that is in simple mode because
nothing useful remains there.
2) Not sure what you are asking about xp_sqlmaint and single-user mode.
3) I wasn't aware that logs grow unchecked if not backed up if the database
is in simple recovery mode. Checkpoints flush committed transactions from
the tlog in that scenario, freeing space to be reused by new transactions.
4) Tlogs exist even in simple recovery mode because sql server is a
write-to-tlog-first engine and this cannot be disabled. No database
modification (insert, update, delete) can occur unless it is first written
to log.
5) I would like to see tlogging be able to be disabled, but not for the log
growth issue. Rather it would provide a fairly significant performance
increase.
TheSQLGuru
President
Indicium Resources, Inc.
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
> Hello. What is the rational behind not allowing a transaction log to be
> backed up on a DB that is using the simple recovery model and how does
> xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
> mode?
> As we know if left unchecked logs can become huge even in simple recovery
> mode. They still need backups. For that matter if they're not deemed
> reliable
> for point in time recovery why do they even exist? We run a reporting
> system
> with most activity at night. We're happy to use differential backups to
> get
> us back to yesterday. Why can't the simple model not use logs at all and
> not
> make us deal with their growth spurts? TIA
> Ken Trock
>|||Thanks for the quick response SQLGuru . On 3), we've indeed had our logs grow
large. Not sure where these checkpoints have been. Also several others here
have posted the same sizing issue. So we (try to) back them up only to keep
their size manageable (maybe that executes a checkpoint). In 2000 if you do
it thru DB maintenance plans in SEM and schedule it you'll notice that the
resultant job calls master.xp_sqlmaint extended proc which is a .dll. I
haven't been able to script a successful tlog backup under simple recovery
model.
Ken
"TheSQLGuru" wrote:
> 1) There is no benefit to backing up a tlog that is in simple mode because
> nothing useful remains there.
> 2) Not sure what you are asking about xp_sqlmaint and single-user mode.
> 3) I wasn't aware that logs grow unchecked if not backed up if the database
> is in simple recovery mode. Checkpoints flush committed transactions from
> the tlog in that scenario, freeing space to be reused by new transactions.
> 4) Tlogs exist even in simple recovery mode because sql server is a
> write-to-tlog-first engine and this cannot be disabled. No database
> modification (insert, update, delete) can occur unless it is first written
> to log.
> 5) I would like to see tlogging be able to be disabled, but not for the log
> growth issue. Rather it would provide a fairly significant performance
> increase.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
> news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
> > Hello. What is the rational behind not allowing a transaction log to be
> > backed up on a DB that is using the simple recovery model and how does
> > xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
> > mode?
> >
> > As we know if left unchecked logs can become huge even in simple recovery
> > mode. They still need backups. For that matter if they're not deemed
> > reliable
> > for point in time recovery why do they even exist? We run a reporting
> > system
> > with most activity at night. We're happy to use differential backups to
> > get
> > us back to yesterday. Why can't the simple model not use logs at all and
> > not
> > make us deal with their growth spurts? TIA
> >
> > Ken Trock
> >
> >
>
>|||The transaction log exists for two reasons. It's primary purpose is to act
as a transaction integrity mechanism. The ltransaction log acts in a
write-ahead fashion. That is, the transaciton officially begins when the
first log entry is written. The relevant data is modified (either in memory
or in the data file) and then the transaction is committed by writing a
second log entry. Finally, a checkpoint process notes when committed
transactions are written to the data file. This allows for transactionally
consistent recovery even on an instantaneous failure of any type. SiMPLE
recovery alows the system to auto-truncate logs once a segment is
checkpointed. Without the separate log file, there would be no way to
guarantee transactional consistency on a hardware or system failure.
The other purpose is to allow for restore (different from recovery!) up to a
specific point in time or to a named transaction. This requires FULL
recovery so that log truncation only occurs after a segment is both
checkpointed and backed up.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
> Hello. What is the rational behind not allowing a transaction log to be
> backed up on a DB that is using the simple recovery model and how does
> xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
> mode?
> As we know if left unchecked logs can become huge even in simple recovery
> mode. They still need backups. For that matter if they're not deemed
> reliable
> for point in time recovery why do they even exist? We run a reporting
> system
> with most activity at night. We're happy to use differential backups to
> get
> us back to yesterday. Why can't the simple model not use logs at all and
> not
> make us deal with their growth spurts? TIA
> Ken Trock
>|||On Wed, 15 Aug 2007 09:34:01 -0700, ktrock
<ktrock@.discussions.microsoft.com> wrote:
>As we know if left unchecked logs can become huge even in simple recovery
>mode.
The log has to be at least large enough for an entire transaction. To
be more precise, it has to hold everything from the start of the
oldest transaction up to the present moment. So it really has to be
large enough for the longest running transaction plus all the other
transactions happening at the same time. If you run really big
transactions you will need a really big log, even in Simple recovery
mode.
In Simple recovery mode you can not back up the log, SQL Server will
not accept the command.
Roy Harvey
Beacon Falls, CT