BACKUP LOG WITH TRUNCATE_ONLY is deprecated in 2005

In my backup program (originally for 2000), for databases not in Full, or
Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
TRUNCATE_ONLY'.
Now I originally copied this from RealSQLGuy's site and never thought to
question need to backup the log for a database in Simple recovery mode - I
supose I've learned a little since then.
So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY' in
the backup script (only for Simple mode DB's) should even be there for
backing up 2000, or 2005 databases.
JayI think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb
WITH TRUNCATE_ONLY' in his script.
In SQL Server 2000, the system will only remove the completed transactions
from a Simple mode database's log file after a checkpoint and that
checkpoint is only guaranteed to fire when the log is 70% full, otherwise it
won't fire until the system reaches the (rather fuzzy) recovery interval.
So, the goal looks like it was to guarantee a regular flush of the completed
transactions in a simple mode transaction log file, which will minimize the
chance that the file will auto-grow.
What I don't understand is that 2005 seems to function the same in reguard
to checkpoints and the truncation of Simple mode transaction log files, so
the original purpose of backing up a Simple mode log with truncate_only
would still apply. Yet Microsoft has deprecated the WITH TRUNCATE_ONLY
option to the backup log statement and they write messages to the SQL Server
log every time you use it.
Confused.
"Jay" <nospam@.nospam.org> wrote in message
news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
> In my backup program (originally for 2000), for databases not in Full, or
> Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
> TRUNCATE_ONLY'.
> Now I originally copied this from RealSQLGuy's site and never thought to
> question need to backup the log for a database in Simple recovery mode - I
> supose I've learned a little since then.
> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'
> in the backup script (only for Simple mode DB's) should even be there for
> backing up 2000, or 2005 databases.
> Jay
>|||TRUNCATE_ONLY would break the log chain that's why it'll be deprecated.
Also, checkpoint does not come into action only when log's 70% is full.
There are also other reasons for checkpoint. They are listed in BOL. You can
take a look at the following link to learn more about these triggers.
http://msdn2.microsoft.com/en-us/library/ms189573.aspx
Ekrem nsoy
"Jay" <nospam@.nospam.org> wrote in message
news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb
>WITH TRUNCATE_ONLY' in his script.
> In SQL Server 2000, the system will only remove the completed transactions
> from a Simple mode database's log file after a checkpoint and that
> checkpoint is only guaranteed to fire when the log is 70% full, otherwise
> it won't fire until the system reaches the (rather fuzzy) recovery
> interval.
> So, the goal looks like it was to guarantee a regular flush of the
> completed transactions in a simple mode transaction log file, which will
> minimize the chance that the file will auto-grow.
> What I don't understand is that 2005 seems to function the same in reguard
> to checkpoints and the truncation of Simple mode transaction log files, so
> the original purpose of backing up a Simple mode log with truncate_only
> would still apply. Yet Microsoft has deprecated the WITH TRUNCATE_ONLY
> option to the backup log statement and they write messages to the SQL
> Server log every time you use it.
> Confused.
> "Jay" <nospam@.nospam.org> wrote in message
> news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>|||Jay,
I think you will achieve the same functionality by scheduling the CHECKPOINT
command in tempdb. This
shouldn't be needed, but if you encounter a case where tempdb log seems to b
e growing even though it
shouldn't be it is something to consider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...[vbc
ol=seagreen]
>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb W
ITH TRUNCATE_ONLY' in
>his script.
> In SQL Server 2000, the system will only remove the completed transactions
from a Simple mode
> database's log file after a checkpoint and that checkpoint is only guarant
eed to fire when the log
> is 70% full, otherwise it won't fire until the system reaches the (rather
fuzzy) recovery
> interval.
> So, the goal looks like it was to guarantee a regular flush of the complet
ed transactions in a
> simple mode transaction log file, which will minimize the chance that the
file will auto-grow.
> What I don't understand is that 2005 seems to function the same in reguard
to checkpoints and the
> truncation of Simple mode transaction log files, so the original purpose o
f backing up a Simple
> mode log with truncate_only would still apply. Yet Microsoft has deprecate
d the WITH TRUNCATE_ONLY
> option to the backup log statement and they write messages to the SQL Serv
er log every time you
> use it.
> Confused.
> "Jay" <nospam@.nospam.org> wrote in message news:u4RMKZkIIHA.6068@.TK2MSFTNG
P05.phx.gbl...
>[/vbcol]|||So, it would be completly OK to remove the code that backs up the
transaction logs in databases that are set to Simple recovery mode.
I need to also find out how often the servers are doing checkpoints too, but
I'll find it.
Thanks,
jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
> Jay,
> I think you will achieve the same functionality by scheduling the
> CHECKPOINT command in tempdb. This shouldn't be needed, but if you
> encounter a case where tempdb log seems to be growing even though it
> shouldn't be it is something to consider.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>|||> So, it would be completly OK to remove the code that backs up the transaction logs in data
bases
> that are set to Simple recovery mode.
Probably. I'm vague, because we know that all programs has bugs in them, and
I don't want to rule
out the possibility that there might exist some bugs regarding the automatic
truncation of log
handling...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:OyzkgQuIIHA.2480@.TK2MSFTNGP05.phx.gbl...[vbcol
=seagreen]
> So, it would be completly OK to remove the code that backs up the transact
ion logs in databases
> that are set to Simple recovery mode.
> I need to also find out how often the servers are doing checkpoints too, b
ut I'll find it.
> Thanks,
> jay
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
>[/vbcol]|||
> Probably. I'm vague, because we know that all programs has bugs in them,
> and I don't want to rule
> out the possibility that there might exist some bugs regarding the
> automatic truncation of log handling...
Do you think that is why RealSQLGuy put the TRUNCATE in his code? I supose I
should fire him an email as he seems to have inactive for about 6 months
now.

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:OyzkgQuIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>|||I looked up the CHECKPOINT in 2000 BOL and the scope of the command is
vague. While some of the references imply its scope is database specific
(specifcally the thing about recovery time), since it is dealing with the
buffer cache (a resources shared by all databases in an instance), it makes
more sense that the scope of a checkpoint would be global.
Also, I'm still looking for something to tell me when the checkpoints are
actually happening. I'm guessing the logs, but havn't been able to check
them yet.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
> Jay,
> I think you will achieve the same functionality by scheduling the
> CHECKPOINT command in tempdb. This shouldn't be needed, but if you
> encounter a case where tempdb log seems to be growing even though it
> shouldn't be it is something to consider.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>|||AFAIK, the CHECKPOINT command is database specific regarding the log truncat
ion. I believe that
there's a trace flag you can turn on to get an entry in the errorlog every t
ime a checkpoint occurs.
Google can probably help you find it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:%23WEZIAxIIHA.3940@.TK2MSFTNGP05.phx.gbl...[v
bcol=seagreen]
>I looked up the CHECKPOINT in 2000 BOL and the scope of the command is vagu
e. While some of the
>references imply its scope is database specific (specifcally the thing abou
t recovery time), since
>it is dealing with the buffer cache (a resources shared by all databases in
an instance), it makes
>more sense that the scope of a checkpoint would be global.
> Also, I'm still looking for something to tell me when the checkpoints are
actually happening. I'm
> guessing the logs, but havn't been able to check them yet.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
>[/vbcol]