|
Home > Archive > microsoft.public.sqlserver.server > October 2002 > How can I minimize transaction logging?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How can I minimize transaction logging?
|
|
| Ken Sturgeon 2002-10-05, 9:09 pm |
| I know this seems very out of the ordinary but I would like to know how can
I minimize transaction logging? I have a dataload process that is bringing
legacy data from an AS400, it is doing bulk inserts of GB's of data and the
transaction log size is preventing me from completing the intial load
process. If it were possible, I'd simply turn off the transaction logging
for the initial load and then turn it back on for nightly updates which will
be much smaller in size.
Thanks,
Ken
| |
| Tibor Karaszi 2002-10-05, 9:09 pm |
| Ken,
Seems you want to check out the recovery model in Books Online (see "SIMPLE" and "BULK LOGGED").
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"Ken Sturgeon" <ksturgeon@genelco.com> wrote in message news:OqqwdpXXCHA.3736@tkmsftngp08...
> I know this seems very out of the ordinary but I would like to know how can
> I minimize transaction logging? I have a dataload process that is bringing
> legacy data from an AS400, it is doing bulk inserts of GB's of data and the
> transaction log size is preventing me from completing the intial load
> process. If it were possible, I'd simply turn off the transaction logging
> for the initial load and then turn it back on for nightly updates which will
> be much smaller in size.
>
> Thanks,
> Ken
>
>
| |
| Dan Guzman 2002-10-05, 9:09 pm |
| You can perform minimally-logged operations to reduce log space
requirements and improve performance. From the SQL Server 2000 Books
Online (adminsql.chm::/ad_impt_bcp_9esz.htm), the following conditions
must be met:
The recovery model is simple or bulk-logged.
The target table is not being replicated.
The target table does not have any triggers.
The target table has either 0 rows or no indexes.
The TABLOCK hint is specified.
--
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
"Ken Sturgeon" <ksturgeon@genelco.com> wrote in message
news:OqqwdpXXCHA.3736@tkmsftngp08...
> I know this seems very out of the ordinary but I would like to know
how can
> I minimize transaction logging? I have a dataload process that is
bringing
> legacy data from an AS400, it is doing bulk inserts of GB's of data
and the
> transaction log size is preventing me from completing the intial load
> process. If it were possible, I'd simply turn off the transaction
logging
> for the initial load and then turn it back on for nightly updates
which will
> be much smaller in size.
>
> Thanks,
> Ken
>
>
| |
| Andrew J. Kelly 2002-10-05, 9:09 pm |
| You can't turn off logging completely but you can have what is considered a
minimally logged Bulk Load. See BCP or Minimally Logged in BooksOnLine for
all the details.
--
Andrew J. Kelly SQL MVP
Targitinteractive, Inc.
"Ken Sturgeon" <ksturgeon@genelco.com> wrote in message
news:OqqwdpXXCHA.3736@tkmsftngp08...
> I know this seems very out of the ordinary but I would like to know how
can
> I minimize transaction logging? I have a dataload process that is bringing
> legacy data from an AS400, it is doing bulk inserts of GB's of data and
the
> transaction log size is preventing me from completing the intial load
> process. If it were possible, I'd simply turn off the transaction logging
> for the initial load and then turn it back on for nightly updates which
will
> be much smaller in size.
>
> Thanks,
> Ken
>
>
| |
| Barry McAuslin 2002-10-05, 9:09 pm |
| I have had the same problem. I have reduced a GB load process to several
hundred KB. My I suggest the following.
Use two databases. The first database will be you production database, this
will have full logging. The second database will be a staging database,
this will have simple logging.
Initially load the data into the staging database. If you are using DTS for
this then set the insert batch size to say 1000 rows. Once the data is in
the staging database then you can update your production database from
there.
The next trick is to minimise transaction logging in the production
database. I do this by only updating the rows that NEED to be updated.
This can be achieved by one of two methods. Method one is by doing a direct
compare of the columns, update those that have changed. The second I use if
there are a large number on columns to check. This involves adding a
checksum column (type int) to the tables. This is then compared with a
checksum values calculated in the load process. I use the BINARY_CHECKSUM
function. If they are different then something has changed. Update the row,
including the checksum column, with the new values.
Hope this has helped
Barry
"Ken Sturgeon" <ksturgeon@genelco.com> wrote in message
news:OqqwdpXXCHA.3736@tkmsftngp08...
> I know this seems very out of the ordinary but I would like to know how
can
> I minimize transaction logging? I have a dataload process that is bringing
> legacy data from an AS400, it is doing bulk inserts of GB's of data and
the
> transaction log size is preventing me from completing the intial load
> process. If it were possible, I'd simply turn off the transaction logging
> for the initial load and then turn it back on for nightly updates which
will
> be much smaller in size.
>
> Thanks,
> Ken
>
>
|
|
|
|
|