another logging question

We're using SS2000. We do a lot of ETL (extract, transform, load) work and
don't really need logs. From advice received here and from reading the BOL,
it looks like using the simple recovery plan would work best for us. I found
this in the BOL:
a.. If the database is using the simple recovery model, an automatic
checkpoint is generated whenever the number of log records reaches the
lesser of these two values:
a.. The log becomes 70 percent full.
b.. The number of log records reaches the number SQL Server estimates it
can process during the time specified in the recovery interval option.
Automatic checkpoints truncate the unused portion of the transaction log if
the database is using the simple recovery model.
Long-Running Transactions
The active portion of the log must include every part of all uncommitted
transactions. An application that starts a transaction and does not commit
it or roll it back prevents SQL Server from advancing the MinLSN. This can
cause two types of problems:
a.. If the system is shut down after the transaction has performed many
uncommitted modifications, the recovery phase of the subsequent restart can
take considerably longer than the amount of time specified in the recovery
interval option.
b.. The log may grow very large because the log cannot be truncated past
the MinLSN. This happens even if the database is using the simple recovery
model, in which the transaction log is normally truncated on each automatic
checkpoint.
Since the checkpoint is issued when the size of the log becomes 70 percent
full, I'm thinking that if I create a small log file (1MB) and do not let it
grow then maybe the checkpoints will come quickly and so the log will never
grow very big. I'm assuming that doing this will mean the value for this
will be smaller than the value of "the number of log records reaches the
number SQL Server estimates it can process during the time specified in the
recovery interval option".
But if I do this I'm wondering what will happen in long transactions. BOL
says "The log may grow very large because the log cannot be truncated past
the MinLSN. This happens even if the database is using the simple recovery
model, in which the transaction log is normally truncated on each automatic
checkpoint." Do you think the log file will continue to grow even though
I've specified in database properties for it NOT to grow? If it does grow
for a large transaction it's ok. I'm assuming it will truncate when the
transactioin is finished. I just don't want the transaction to end before
it's finished because I've told it not to grow the transaction log.
One of the databases that we have has a table with about 55 million rows in
it. We add about 800,000 rows each month using a "select into" statement.
This is the table:
CREATE TABLE [tblDNCNational] (
[PHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATE_FLAG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATE_ENTERED] [smalldatetime] NULL
I don't know how big the transaction log would get by adding 800,000 rows
but I'm guessing that it would certainly exceed the 1MB size of the
tranaction log. So, I just want to be sure that the system will grow the log
file even though I've told it not to or it will truncate the file and keep
going. I could break the one transaction up into more transactions if
someone thinks that would help.
Any advice is appreciated,
DanI tested this and found out that I can't set the log file to 1M and then not
grow. A large transaction fills up the log. So, I have to let it grow.
However, under the simple recovery plan, the log file does truncate at the
end of every transaction. I'll probably do a shrinkfile every so often to
free up some storage room.
Dan
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:ecv4FhCPEHA.2996@.TK2MSFTNGP12.phx.gbl...
> We're using SS2000. We do a lot of ETL (extract, transform, load) work and
> don't really need logs. From advice received here and from reading the
BOL,
> it looks like using the simple recovery plan would work best for us. I
found
> this in the BOL:
> a.. If the database is using the simple recovery model, an automatic
> checkpoint is generated whenever the number of log records reaches the
> lesser of these two values:
> a.. The log becomes 70 percent full.
> b.. The number of log records reaches the number SQL Server estimates it
> can process during the time specified in the recovery interval option.
> Automatic checkpoints truncate the unused portion of the transaction log
if
> the database is using the simple recovery model.
> Long-Running Transactions
> The active portion of the log must include every part of all uncommitted
> transactions. An application that starts a transaction and does not commit
> it or roll it back prevents SQL Server from advancing the MinLSN. This can
> cause two types of problems:
> a.. If the system is shut down after the transaction has performed many
> uncommitted modifications, the recovery phase of the subsequent restart
can
> take considerably longer than the amount of time specified in the recovery
> interval option.
>
> b.. The log may grow very large because the log cannot be truncated past
> the MinLSN. This happens even if the database is using the simple recovery
> model, in which the transaction log is normally truncated on each
automatic
> checkpoint.
> Since the checkpoint is issued when the size of the log becomes 70 percent
> full, I'm thinking that if I create a small log file (1MB) and do not let
it
> grow then maybe the checkpoints will come quickly and so the log will
never
> grow very big. I'm assuming that doing this will mean the value for this
> will be smaller than the value of "the number of log records reaches the
> number SQL Server estimates it can process during the time specified in
the
> recovery interval option".
> But if I do this I'm wondering what will happen in long transactions. BOL
> says "The log may grow very large because the log cannot be truncated past
> the MinLSN. This happens even if the database is using the simple recovery
> model, in which the transaction log is normally truncated on each
automatic
> checkpoint." Do you think the log file will continue to grow even though
> I've specified in database properties for it NOT to grow? If it does grow
> for a large transaction it's ok. I'm assuming it will truncate when the
> transactioin is finished. I just don't want the transaction to end before
> it's finished because I've told it not to grow the transaction log.
> One of the databases that we have has a table with about 55 million rows
in
> it. We add about 800,000 rows each month using a "select into" statement.
> This is the table:
> CREATE TABLE [tblDNCNational] (
> [PHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [STATE_FLAG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [DATE_ENTERED] [smalldatetime] NULL
> I don't know how big the transaction log would get by adding 800,000 rows
> but I'm guessing that it would certainly exceed the 1MB size of the
> tranaction log. So, I just want to be sure that the system will grow the
log
> file even though I've told it not to or it will truncate the file and keep
> going. I could break the one transaction up into more transactions if
> someone thinks that would help.
> Any advice is appreciated,
> Dan
>|||Dan,
Your load will fail if SQL Server cannot write to the log because its full.
You will get a transaction log full error and the transaction will get
rolled back. Why not just size the log to the biggest you think you will
need and leave it at that? Shrinking and growing the log will just fragment
the log file.
If you can do your work in smaller transactions, then all the better for
your log size. You could use an INSERT...SELECT statement with an
appropriate WHERE clause in a WHILE loop to do your batch inserts in smaller
steps.
However, bear in mind that you will lose transactional consistency. i.e. if
the load fails half way through, then only uncommitted transactions get
rolled back. My preferred approach would be to set your log file to a larger
size, you then retain transactional consistency. You say that you don't need
the logs, but you really do, so that SQL Server knows what to roll
back/forward in the event of a server failure.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:ecv4FhCPEHA.2996@.TK2MSFTNGP12.phx.gbl...
> We're using SS2000. We do a lot of ETL (extract, transform, load) work and
> don't really need logs. From advice received here and from reading the
BOL,
> it looks like using the simple recovery plan would work best for us. I
found
> this in the BOL:
> a.. If the database is using the simple recovery model, an automatic
> checkpoint is generated whenever the number of log records reaches the
> lesser of these two values:
> a.. The log becomes 70 percent full.
> b.. The number of log records reaches the number SQL Server estimates it
> can process during the time specified in the recovery interval option.
> Automatic checkpoints truncate the unused portion of the transaction log
if
> the database is using the simple recovery model.
> Long-Running Transactions
> The active portion of the log must include every part of all uncommitted
> transactions. An application that starts a transaction and does not commit
> it or roll it back prevents SQL Server from advancing the MinLSN. This can
> cause two types of problems:
> a.. If the system is shut down after the transaction has performed many
> uncommitted modifications, the recovery phase of the subsequent restart
can
> take considerably longer than the amount of time specified in the recovery
> interval option.
>
> b.. The log may grow very large because the log cannot be truncated past
> the MinLSN. This happens even if the database is using the simple recovery
> model, in which the transaction log is normally truncated on each
automatic
> checkpoint.
> Since the checkpoint is issued when the size of the log becomes 70 percent
> full, I'm thinking that if I create a small log file (1MB) and do not let
it
> grow then maybe the checkpoints will come quickly and so the log will
never
> grow very big. I'm assuming that doing this will mean the value for this
> will be smaller than the value of "the number of log records reaches the
> number SQL Server estimates it can process during the time specified in
the
> recovery interval option".
> But if I do this I'm wondering what will happen in long transactions. BOL
> says "The log may grow very large because the log cannot be truncated past
> the MinLSN. This happens even if the database is using the simple recovery
> model, in which the transaction log is normally truncated on each
automatic
> checkpoint." Do you think the log file will continue to grow even though
> I've specified in database properties for it NOT to grow? If it does grow
> for a large transaction it's ok. I'm assuming it will truncate when the
> transactioin is finished. I just don't want the transaction to end before
> it's finished because I've told it not to grow the transaction log.
> One of the databases that we have has a table with about 55 million rows
in
> it. We add about 800,000 rows each month using a "select into" statement.
> This is the table:
> CREATE TABLE [tblDNCNational] (
> [PHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [STATE_FLAG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [DATE_ENTERED] [smalldatetime] NULL
> I don't know how big the transaction log would get by adding 800,000 rows
> but I'm guessing that it would certainly exceed the 1MB size of the
> tranaction log. So, I just want to be sure that the system will grow the
log
> file even though I've told it not to or it will truncate the file and keep
> going. I could break the one transaction up into more transactions if
> someone thinks that would help.
> Any advice is appreciated,
> Dan
>|||Hi Dan,
It is not advisable to restrict the growth of the Log file.
If you restrict the growth of the log file then, the active transactions
would be rolled back when the Log gets full, due to long running
transactions, and it is not able to expand it self to accomodate more
transaction.
Allow the log file to grow and you may set the recovery to simple, so that
the log is truncated on checkpoints.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.