Transaction log grows too large when imported records

[Bmbscr]

Folks, I have an access database with 100,000 records in it and I want to import it into SQL Server 2000. The problem is that our server only has 3Gb of space left on it and when I do the import the transaction log grows to such a size that it eats up all the space left. Is there any way of doing an import that bypasses the transaction logs or has anybody got any other ideas.

Thanks

 

[mitchadams]

Pehaps import in stages, maybe 20,000 at a time.

Smaller imports won't use as much Log space.

If using Enterprise Manager, import using query and do: select top 20000 * from mytable

then delete the top 20000 from the original table,
truncate the transaction log
and repeat until you have finished.

 

[SQLSister]

Don't use the above method for import to a production system as truncating the transaction log is a bad idea in a production system. What you reallly want to do is set up transaction log backups and then backup and shrink the log after each batch. An access database with 1000,000 records should import easily without using up all your hard drive space. Perhaps the problem is that you are not currently set up to do transaction log backups? The transaction log will never clear on it's own until you do a backup or run a statement telling it to clear the uncommitted records. Therefore it just keeps growing until it eats all your hard drive space.

 

[NoCoolHandle]

Depending on table/index structures space can be used up very quickly in sql server.

The issue of course is logging. 1 row for each row in the table and one row for each row in each index...

In my experience the Bulk Insert command or BCP will remove these problems. But it comes at a trade off in recoverability and requires good house keeping practices.

Any time you execute a Bulk Insert or BCP the rows are added to the table and indexes, but the only thing being recorded in SQL is the allocation of space not the individule inserts. So after one of these "transfers" it is critical that you execute a full backup of your database, or at the very least a differential backup.

If you can't afford to perform non-logged opps, at the very least you might want to evaluate dropping and rebuilding indexes (especially if you have a few)


HTH

Rob