Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2012
    Posts
    12

    Unanswered: Dump trans doesn't free space

    Bonjour community,
    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).

    Thanks for your help.

  2. #2
    Join Date
    Mar 2012
    Posts
    12
    Ok, I found out why the transaction log doesn't get freed when dumping it. Only completed transactions can be flushed to disk.

    Now, this leaves me whit my main task, how can I move my 80mo table from tempdb to mydb with only 55 mo available in transaction log?

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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
    Last edited by pdreyer; 03-27-12 at 11:51.

  4. #4
    Join Date
    Mar 2012
    Posts
    12
    Thanks pdreyer,
    but I am not sure I quite understand your statement.

    First, why do you select from sysobjects (or could it be replaced by my table_name)?

    And where does the id%3 come from?

    P.S. Meanwhile, I'll query the net to find those informations but would really appreciate if you could drop a reply.

    Best Regards,

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Yes, replace sysobjects with your table name.
    Run the select on sysobjects to understand what it produce
    SELECT id%3, count(*) FROM sysobjects group by id%3

    id%3 = divide by 3 and return the remainder

  6. #6
    Join Date
    Mar 2012
    Posts
    12
    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) ?

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You can add an identity column to your temp table but
    Depending on your data
    Use the datetime to split the data by year
    e.g.
    insert1 where thedate < '20050101'
    insert2 where thedate >= '20050101'

    For a char key
    e.g. insert1 where thecar < 'L'
    insert2 where thecar >= 'L'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •