not filling up the transaction log

not filling up the transaction log

Post by roygut » Thu, 04 Jul 2002 01:00:00



Hi there,

I'm importing a lot of data to a sql server 2000 database. I'm doing this
import thru a program, basically the program just sends sql insert commands
to the sql server. Now the problem is that I don't want to fill up the
transaction log. I want to do everything non-logged. What is the best way to
do this? I read some about the bulk copy and that it's non-logged but this
is using bcp. Or is there a command that I can run to empty the transaction
log at different intervals in the program so that it doesn't fill up the
transaction log?

Thanks

 
 
 

not filling up the transaction log

Post by Russell Field » Thu, 04 Jul 2002 01:17:55


If you set your database recovery model to:

Simple - the transaction log is truncated regularly, so it will not grow.
Bulk Insert - the transaction log must be backed up, but for BULK INSERTs
and BCPs the logging is minimized from row level logging to space allocation
(pages, extents) logging.

With either Bulk Insert or Full recovery models, schedule plenty of log
backups.  These can also be triggered by how full the log is.

One note:  None of this works if the whole process is wrapped in an open
transaction.

Russell Fields

Quote:> Hi there,

> I'm importing a lot of data to a sql server 2000 database. I'm doing this
> import thru a program, basically the program just sends sql insert
commands
> to the sql server. Now the problem is that I don't want to fill up the
> transaction log. I want to do everything non-logged. What is the best way
to
> do this? I read some about the bulk copy and that it's non-logged but this
> is using bcp. Or is there a command that I can run to empty the
transaction
> log at different intervals in the program so that it doesn't fill up the
> transaction log?

> Thanks


 
 
 

1. Transaction log not filling up

Most messages with the transaction logs seem to be concerned about
stopping it from growing, but what if the transaction log seems to
have stopped filing up?  It is acting as if "truncate log on
checkpoint" is turned on, but  "select databasepropertyex ('DB',
'recovery')" shows it as full.

Any suggestions as what to look for?  

Thanks

Scott Foley
SQL 2000 SP3
W2K SP3

2. Q: LIST in Screen

3. transaction log filling up and transaction replication

4. Un-install issue - Sql-server 7.0

5. transaction log fills every 30 days log file size is 500MB is this normal

6. Trans log not filling up when not Trunc On Check

7. Transaction Log Filling Up...HELP!!

8. Help - Queries filling transaction log

9. transaction log backup filling drive

10. Transaction log fills up