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
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...
> 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...
>
|||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...
>
|||
> 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...
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment