How to temporarily disable the log in SQL2000/2005?

views:

298

answers:

How to temporarily disable the log in SQL2000/2005?

Is there a way to stop the log file from growing (or at least from growing as much) in SQL2000/2005?

I am running a very extensive process with loads of inserts and the log is going to the roof.

EDIT: please note I am talking about an batch-import process not about everyday update of live-data.

Cheers!

+1 A:

You can't disable the log, but you could perform your inserts in batches and backup/truncate the log in between batches.

If the data originates from outside your database you could also consider using BCP.

Ed Guiness 2009-02-19 11:59:49

if you switch to SIMPLE mode wouldn't the log grow but not as much?

JohnIdol 2009-02-19 12:29:09

You can also put the logfile on a different drive than the main database. This would help in the short run, because you can reduce the frequencey of the truncations/shrinks.

2009-02-19 14:00:50

BCP operations are still logged. SIMPLE recovery causes the log to be truncated as soon as the transactions are committed.

mrdenny 2009-03-09 08:46:02

Zaagmans 2009-02-19 12:03:45

What happens if autogrowth is turned off and the log size is exceeded?

Ed Guiness 2009-02-19 12:09:03

a mess - I guess

JohnIdol 2009-02-19 12:26:52

You'll receive error 1105: "Can't allocate space for object syslogs in database dbname because the logsegment is full. If you..." (http://support.microsoft.com/kb/110139/en-us)

Zaagmans 2009-02-19 13:07:52

I know my log is gonna grow A LOT so this wouldn't help

JohnIdol 2009-02-19 13:55:59

ok, good luck in finding a solution that fits your needs. Please keep us updated in your findings...

Zaagmans 2009-02-19 14:16:47

Changing the recovery mode to SIMPLE causes the log to grow not as much.

What's people opinion about this solution?

JohnIdol 2009-02-20 08:51:27

I am running an import - you can downmod all you want but I am fine with switching temporarily to simple mode (in fact I did it).

JohnIdol 2009-04-03 23:44:21

Have you tested restores afterwards? ;)

Mark Brackett 2009-04-03 23:52:24

it works like charm :)

JohnIdol 2009-04-07 15:28:01

+1 A:

Remember that setting the recovery mode to SIMPLE only allows you to recover the database to the point of your most recent backup. Pending transaction which have not been committed to the database - after the backup has been created - will be lost.

Zaagmans 2009-02-20 08:58:18

yeah I know - I am importing big batches of stuff so I don't plan on taking backups during the import - I can switch back recovery mode afterwards

JohnIdol 2009-02-20 09:31:23

If it's just a one time import then go for it! Good luck! :-)

Zaagmans 2009-02-20 09:58:57

Switching back still breaks the log chain - after your switch, you need to run a full backup.

Mark Brackett 2009-04-03 23:51:46

Changing the recovery model will cause your old log backups to be of no use if you need to restore as this will change the log chain.

If you need full recovery normally you'll want to increase your log backup frequency during the load process. This can be done by changing the job schedule for the log backup via the sp_update_jobschedule procedure in the msdb database both before and after the load process.

mrdenny 2009-03-09 08:41:24

Your batch may make too much use of temporary tables. Try the suggestions in this article

Andyh 2009-09-16 19:56:58

ansaurus

tags:

views:

answers:

How to temporarily disable the log in SQL2000/2005?

related questions