Create DB without Log...Can it be done?

[jazerr]

I have a db with a bad design that cannot change. There are about 4million records in one particular table and every time any update/insert happens, it errors with the 'transaction log full' message.

I'm wondering, in the database creation, can I specify that I NEVER EVER EVER want anything to write to the transaction log? Using t-sql of course?

I have to have this done today if anybody can help....

 

[jcaulder]

SQL Server much have a transaction log but if you don't want to worry about it filling up, change the recovery model of the database to SIMPLE. The transaction log will never fill up using this recovery model.

However, be careful since there is no point in time recovery using this model which means that you will lose any data written to the database between full database backups.

If you backup the transaction log periodically, say once an hour, the transaction log will truncate and be able to reclaim space. This will also prevent the problem you're experiencing but with a much safer recovery model. Using this, you keep the database in FULL recovery model and have full point in time recovery available.

 

[jazerr]

Thanks. Thats helpful. Is there a way to specify this in the DB creation script? All our create are in t-sql.

 

[SQLBill]

FYI-This is a duplicate post and is also at THREAD183-735320. There are more responses on that thread.

-SQLBill

 

[nigelrivett]

exec
sp_dboption dbname, 'trunc. log on chkpt.', 'true'

You might consider setting model to this when each server is installed so that it is the default (as I think it should be).

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[jazerr]

Will there be negative side-effects to this? What are the possible repercussions?