Post reply

Stop logging transactions? TempDB?

  • December 29, 2003 at 10:18 pm

    #81767

    Greetings all.

    I have a query which is shuffling about 12.5 million rows around.

    My problem is the logfile grows to the point of filling the disks.

    Now I am sure there is no way to off logging but I am almost sure I heard that tempDB does not log. Correct?

    The query takes around 14 hours - to fall over.

    Thanks.

    Cheers,

    Crispin

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • December 30, 2003 at 3:17 am

    #488149

    Unfortunatly you are not right.

    Even the tempdb logs.

    You have (at least) 2 possibilities

    • BCP out the data you want to keep, then truncate the table, after BCP in the data. Be shure to have the recovery mode set to Bulk logged. Almost no logging will be done
    • Build your job in smaller pieces (10K rows) and truncate the log in between

    Bye

    Gabor



    Bye
    Gabor

  • December 30, 2003 at 8:08 am

    #488188

    You probably want to take a full backup, switch to simple recovery, run the job, switch back to full recovery and then take another full backup. All of this can be scripted.

    You can BCP or truncate the log as nyulg suggests, but you'll still need a full backup at the end.

  • December 30, 2003 at 8:22 am

    #488192

    Even though you change recovery mode to 'simple', any activeties such as insert/delete/update in your query will still be loged in transaction log file during the query execution.

    You have to break one large update from single transaction into mutilple small batches to make transaction log as small as you can afford, either backup the log or truncate the log, or set database in 'simple' recovery to let system to truncate log itself.

  • December 30, 2003 at 10:11 am

    #488224

    But do not forget that the simple recovery mode is using a full logging but afterwards on checkpoint it is "autotruncating" itself.

    In a bulk logged recovery mode BCP, bulk inserts, create indexes etc are not fully logged (it means that only the modified pages are logged not each and every rows.

    That's why I'm suggesting the usage of BCP out/truncate table/BCP in process.

    But of cours it depends on your environment. You cannot do this in an OLTP environment as you cannot afford any data change during this process.

    Bye

    Gabor



    Bye
    Gabor

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply