Our environment is development and data warehousing with many non logged
trasactions. Our recover model is simple and our backups are always full
backups.
My question is:
Is there anything in sql that is comparable to the archive flag of a file?
We do have some databases that might not change in a week. It would be nice
not to have to back them up
Robert Alexander
Robert.Alexander@.cca-audit.comHave you looked at differential backups? They only backup the pages that
have changed in the DB, no matter if you are in full, simple or bulk load
mode.
This assumes that you have access to the last full backup.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
> Our environment is development and data warehousing with many non logged
> trasactions. Our recover model is simple and our backups are always full
> backups.
> My question is:
> Is there anything in sql that is comparable to the archive flag of a file?
> We do have some databases that might not change in a week. It would be
nice
> not to have to back them up
> Robert Alexander
> Robert.Alexander@.cca-audit.com
>|||No. But I will. I thought differential just backed up the full log.
Thanks for the tip.
Rob
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:um2yVONyEHA.3368@.TK2MSFTNGP15.phx.gbl...
> Have you looked at differential backups? They only backup the pages that
> have changed in the DB, no matter if you are in full, simple or bulk load
> mode.
> This assumes that you have access to the last full backup.
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
> news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
>> Our environment is development and data warehousing with many non logged
>> trasactions. Our recover model is simple and our backups are always
>> full
>> backups.
>> My question is:
>> Is there anything in sql that is comparable to the archive flag of a
>> file?
>> We do have some databases that might not change in a week. It would be
> nice
>> not to have to back them up
>> Robert Alexander
>> Robert.Alexander@.cca-audit.com
>>
>
Showing posts with label logged. Show all posts
Showing posts with label logged. Show all posts
Thursday, March 29, 2012
Sunday, February 19, 2012
Backup logged time different
Hi,
I'm confused with what's being reported by SQL Server for the date of a
transacation log backup.
The backup had been taken on Jun 29, 2006, 12:14:20 p.m. This can be seen in
the Archive SQL Server Logs. However, when I double-click that entry, I get
the following result:
Log backed up: Database: ABC, creation date(time): 2006/05/31(09:21:36),
first LSN: 15407:479:1, last LSN: 15769:73834:1, number of dump devices: 1,
device information: (FILE=1, TYPE=DISK: {'R:\MSSQL.DATA\ABC.bak'}).
Note that the date-time for this entry is different. Any ideas why? I'm so
confused.
TIA.
Regards,The creation date in the log is the database creation date
and time, not the backup date and time.
-Sue
On Wed, 9 Aug 2006 07:50:02 -0700, Rob
<Rob@.discussions.microsoft.com> wrote:
>Hi,
>I'm confused with what's being reported by SQL Server for the date of a
>transacation log backup.
>The backup had been taken on Jun 29, 2006, 12:14:20 p.m. This can be seen in
>the Archive SQL Server Logs. However, when I double-click that entry, I get
>the following result:
>Log backed up: Database: ABC, creation date(time): 2006/05/31(09:21:36),
>first LSN: 15407:479:1, last LSN: 15769:73834:1, number of dump devices: 1,
>device information: (FILE=1, TYPE=DISK: {'R:\MSSQL.DATA\ABC.bak'}).
>Note that the date-time for this entry is different. Any ideas why? I'm so
>confused.
>TIA.
>Regards,
I'm confused with what's being reported by SQL Server for the date of a
transacation log backup.
The backup had been taken on Jun 29, 2006, 12:14:20 p.m. This can be seen in
the Archive SQL Server Logs. However, when I double-click that entry, I get
the following result:
Log backed up: Database: ABC, creation date(time): 2006/05/31(09:21:36),
first LSN: 15407:479:1, last LSN: 15769:73834:1, number of dump devices: 1,
device information: (FILE=1, TYPE=DISK: {'R:\MSSQL.DATA\ABC.bak'}).
Note that the date-time for this entry is different. Any ideas why? I'm so
confused.
TIA.
Regards,The creation date in the log is the database creation date
and time, not the backup date and time.
-Sue
On Wed, 9 Aug 2006 07:50:02 -0700, Rob
<Rob@.discussions.microsoft.com> wrote:
>Hi,
>I'm confused with what's being reported by SQL Server for the date of a
>transacation log backup.
>The backup had been taken on Jun 29, 2006, 12:14:20 p.m. This can be seen in
>the Archive SQL Server Logs. However, when I double-click that entry, I get
>the following result:
>Log backed up: Database: ABC, creation date(time): 2006/05/31(09:21:36),
>first LSN: 15407:479:1, last LSN: 15769:73834:1, number of dump devices: 1,
>device information: (FILE=1, TYPE=DISK: {'R:\MSSQL.DATA\ABC.bak'}).
>Note that the date-time for this entry is different. Any ideas why? I'm so
>confused.
>TIA.
>Regards,
Backup logged time different
Hi,
I'm confused with what's being reported by SQL Server for the date of a
transacation log backup.
The backup had been taken on Jun 29, 2006, 12:14:20 p.m. This can be seen in
the Archive SQL Server Logs. However, when I double-click that entry, I get
the following result:
Log backed up: Database: ABC, creation date(time): 2006/05/31(09:21:36),
first LSN: 15407:479:1, last LSN: 15769:73834:1, number of dump devices: 1,
device information: (FILE=1, TYPE=DISK: {'R:\MSSQL.DATA\ABC.bak'}).
Note that the date-time for this entry is different. Any ideas why? I'm so
confused.
TIA.
Regards,The creation date in the log is the database creation date
and time, not the backup date and time.
-Sue
On Wed, 9 Aug 2006 07:50:02 -0700, Rob
<Rob@.discussions.microsoft.com> wrote:
>Hi,
>I'm confused with what's being reported by SQL Server for the date of a
>transacation log backup.
>The backup had been taken on Jun 29, 2006, 12:14:20 p.m. This can be seen i
n
>the Archive SQL Server Logs. However, when I double-click that entry, I get
>the following result:
>Log backed up: Database: ABC, creation date(time): 2006/05/31(09:21:36),
>first LSN: 15407:479:1, last LSN: 15769:73834:1, number of dump devices: 1,
>device information: (FILE=1, TYPE=DISK: {'R:\MSSQL.DATA\ABC.bak'}).
>Note that the date-time for this entry is different. Any ideas why? I'm so
>confused.
>TIA.
>Regards,
I'm confused with what's being reported by SQL Server for the date of a
transacation log backup.
The backup had been taken on Jun 29, 2006, 12:14:20 p.m. This can be seen in
the Archive SQL Server Logs. However, when I double-click that entry, I get
the following result:
Log backed up: Database: ABC, creation date(time): 2006/05/31(09:21:36),
first LSN: 15407:479:1, last LSN: 15769:73834:1, number of dump devices: 1,
device information: (FILE=1, TYPE=DISK: {'R:\MSSQL.DATA\ABC.bak'}).
Note that the date-time for this entry is different. Any ideas why? I'm so
confused.
TIA.
Regards,The creation date in the log is the database creation date
and time, not the backup date and time.
-Sue
On Wed, 9 Aug 2006 07:50:02 -0700, Rob
<Rob@.discussions.microsoft.com> wrote:
>Hi,
>I'm confused with what's being reported by SQL Server for the date of a
>transacation log backup.
>The backup had been taken on Jun 29, 2006, 12:14:20 p.m. This can be seen i
n
>the Archive SQL Server Logs. However, when I double-click that entry, I get
>the following result:
>Log backed up: Database: ABC, creation date(time): 2006/05/31(09:21:36),
>first LSN: 15407:479:1, last LSN: 15769:73834:1, number of dump devices: 1,
>device information: (FILE=1, TYPE=DISK: {'R:\MSSQL.DATA\ABC.bak'}).
>Note that the date-time for this entry is different. Any ideas why? I'm so
>confused.
>TIA.
>Regards,
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]
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]
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.
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...
>
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...
>
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...
>> 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
>|||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...
>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
>|||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...
>>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
>>
>|||> 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.
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...
> 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...
>>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
>>
>>
>|||> 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...
>> 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...
>>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
>>
>>
>>
>|||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...
>>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
>>
>|||AFAIK, the CHECKPOINT command is database specific regarding the log truncation. I believe that
there's a trace flag you can turn on to get an entry in the errorlog every time 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...
>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...
>>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
>>
>>
>
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...
>> 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
>|||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...
>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
>|||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...
>>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
>>
>|||> 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.
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...
> 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...
>>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
>>
>>
>|||> 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...
>> 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...
>>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
>>
>>
>>
>|||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...
>>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
>>
>|||AFAIK, the CHECKPOINT command is database specific regarding the log truncation. I believe that
there's a trace flag you can turn on to get an entry in the errorlog every time 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...
>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...
>>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
>>
>>
>
Subscribe to:
Posts (Atom)