Hi there

I have created a DTS package which;
Drops a table
Creates it again
Copies data from a text file into the table (simply using the 'copy
column' transformation)

However, my transaction log fills up, and I get an error - 'log file
for database XXXX is full' etc.

I am already looking into ways to reduce logging (from other posts)
but is there a better way to get the data into the table, to minimize
the writing to the transaction log? Would it be better to use bcp or
BULK INSERT? (or is this what the DTS package uses anyway?)

I don't need each insert into the table logging, but I do want to roll
back the whole package if the insert fails. i.e. I want to be able to
roll back the 'drop table'.

I would be very grateful for any suggestions.

Thanks, Lewis

OK

DTS when loading your rows stores them as one big batch so a batch of 10
million rows at 8K per row is relatively large. The log will not be able to
breath even if you set the RECOVERY MODEL to simple.
Use batches.

They are available on the last table (FETCH, INSERT) of the datapump task.
They are also available n the BULK INSERT task (Second Tab)
They are available using TSQL BULK INSERT (BATCHSIZE=)

set the recovery model of your DB to simple as well as it does sound like
you have limited disk space. Depending on your backup requirements you can
either change this back after the package or leave as is.
--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Lewis Veale" <lewis.veale (AT) pdms (DOT) com> wrote

Thanks very much for the promp reply Allan. I will try to use batches.

When you say "set the recovery model of your DB to simple" - how do I do
this? I am using SQL Server 7.

Thanks again

Lewis Veale
lewisdotvealeatpdms.com
www.pdms.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Oh sorry. Recovery models are a 2000 expression.

You want

Exec sp_dboption 'name of database','select','true'
Exec sp_dboption 'name of database','trunc','true'


--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Lewis" <lewisdotvealeatpdms.com> wrote

Excellent! It worked. Thanks Allen

Would I be right in thinking that those two executions of sp_dboption
are equivalent to ticking the 'Truncate log on checkpoint' and 'Select
into/bulk copy' boxes, on the Options tab of the Database Props screen?

Should I leave the database like this, or set either/both of these to
'false' once the import is complete?

Thanks again. It is much appreciated.

Lewis Veale
lewisdotvealeatpdms.com
www.pdms.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

You are correct

Whether to leave them set or not depends on your backup and recovery
requirements. If you are happy to rely on FULL and DIFFERENTIAL backups
then you can leave as is. If you want Point in time Transaction log backups
then you will need to set them back to false.

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Lewis" <lewisdotvealeatpdms.com> wrote

One more thing....

Is

EXEC SP_DBOPTION 'NHS_Car_Leasing', 'select into/bulkcopy', 'true'

the same as

EXEC sp_dboption 'name of database','select','true'

?

Lewis Veale
lewisdotvealeatpdms.com
www.pdms.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thats very useful. Thanks again Allan.

Lewis Veale
lewisdotvealeatpdms.com
www.pdms.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

yes

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Lewis" <lewisdotvealeatpdms.com> wrote

I should have added.

sp_dboption allows you to shortcut the middle parameter if there is no
ambiguity

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Lewis" <lewisdotvealeatpdms.com> wrote