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....

 

[royjimenez]

assign more space to log file

The life is too short to cry and long enough to try it... God bless us.

 

[SQLSister]

So just when was the last time you backed up the transaction log?

 

[SonOfEmidec1100]

Which version of SQL sounds like 6.5, later versions allow the log file to grow automatically - If so on the database/properties/options tab try checking the settings 'Select Into Bulk Copy' and 'Truncate log on checkpoint'.

 

[jazerr]

Its SQL2000. The key is that I dont want it to grow. Company's too cheap to buy me drives, so I need to keep the transaction log as small as possible, and still let the mdf files grow.

 

[mrdenny]

Set your recovery mode to Simple. The Log will still need space to work for large transactions.

You may need to break out with the email to the boss saying that if it doesn't get an upgrade it's going to break, and there isn't going to be anything that I can do about it, unless you spend a few hundred - couple thousand dollars for an upgrade. With the upgrade it will work this much better, and last for this much longer. With out the upgrade it's doing to die in this time frame. How important is it?

If fibbing gets you want you need to keep the system up and running, then don't feel to bad about it. Just be sure you can back it up. See Thread thread717-730401 in the IT Ethics in the Workspace forum.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLSister]

I wouldn't set the recovery mode to simple on any production database unless it relies soley on imported data that was easily reproduced. Simple recovery will not let you recover anything past your last full backup. This isnot an acceptable scenario for most companies. Again, I ask when did you last back up your log (not the database, the log.) the best way to keep the log small and still have the dataavailable for recovery is to back it up frequently. We back ours up every half hour. Could be you have plenty of space for the transaction log, you just never clear it out.

However the log needs to be big enough to handle the largest transaction you will run on it. SO if you run large data imports it will need to be larger. You can use bulk recovery method to not log bulk transactions which can help in this case. Look in Books Online for info on recovery options that you can set the database to. Do not opt for full recovery unless you fully understand that you will NOT be able to fully recover the data if a problem occurs.