April 30, 2002 at 11:05 pm
I have a database that is used only for a massive bulk insert early in the morning. This db has only five tables, but the text files altogether are 40 Mb large.
Before this insert all rows of the day before are deleted, and the db has no rows on any table.
What recovery model should I use?
This process has to be fully automated (deletion, insert, and most important, getting rid of the log).
No transactions are made (at all) in this database, it's use is read only (except for the import).
Do I have to full backup after the import, so I the USED part of the LOG will shrink ?
The log file itself can be maintained large, but only with enough room for the insert of each next day.
This is the command I use for the insert:
DELETE FROM TELESP..Tabela1
BULK INSERT telesp..tabela1 FROM 'C:\Inex\TELESP\Projeto\tabela1.txt'
WITH (
DATAFILETYPE = 'char',
CODEPAGE = 'RAW',
FIELDTERMINATOR = ';',
ROWTERMINATOR = ';',
KEEPNULLS
)
DELETE FROM TELESP..Tabela2
BULK INSERT telesp..tabela2 FROM 'C:\Inex\TELESP\Projeto\tabela2.txt'
WITH (
DATAFILETYPE = 'char',
CODEPAGE = 'RAW',
FIELDTERMINATOR = ';',
ROWTERMINATOR = ';',
KEEPNULLS
)
Thanks
May 1, 2002 at 3:43 am
If the database is only used for read only and from the information you have supplied my personal approach would be the following.
Have the option 'Truncate log on checkpoint' and 'select into/bulkcopy' options are set on the database.
Instead of using the DELETE statement use TRUNCATE TABLE instead. This will not use the transaction log to log the records deleted.
On your BULK INSERT statement also use the TABLOCK hint option. This will not use the transaction log to log the records inserted.
This method will get your database operational in the fastest possible manner. All you then need to do after the insert is a full database backup with no need to worry about transaction log back ups.
Rick.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply