I'm currently experiencing a very frustrating situation. I have a table moved in a temporary database (tempdb not to name it) and I need to move it back its permanent localtion. The problem is, the table is 78Mo in space and my log space is 55mo.
When I try inserting the data into the table I get a message saying that I need to free up space in the transaction log.
When I try to dump the transaction log, it works but for an obscure reason, it doesn't free up space in the log segment so my insert never get to resume.
Why does my transaction log doesn't free upon dumping it?
P.S. I had the "trunc. log on chkpt" set to true in dboption but whenever I tried to dump tran with truncate or dump trans with no_log I got an error saying that it didn't work and that I needed to have a look at the sysloghold (or something the like).
split the insert into smaller batches e.g. to split sysobjects into 3
INSERT INTO t1 SELECT * FROM sysobjects WHERE id%3=0
INSERT INTO t1 SELECT * FROM sysobjects WHERE id%3=1
INSERT INTO t1 SELECT * FROM sysobjects WHERE id%3=2
Thanks again pdreyer,
I do understand the principle now.
But, to do this, I need an interger field (like ID in sysobjects) in my table which is not the case, all varchar, char and datetime. Is there an other "hidden" field I could use (similar to rownum in Oracle) ?