How to turn off transaction log in SQL 2000

You cannot turn off the transaction log, but you can keep it's size down.

In Enterprise Manager, try:
Right Click the db -> Properties -> Options tab -> Set Recovery Model to "Simple"
This should help to keep the log file down in size.

Backup ur Log Files in a period.. This ll Automaticly empty ur Log File..

Once A day if needed twice or 3rd times a  day ..

i try to remove the log file which 2GB size and the daabase can't startup ... currently, i need to re-create the log file when get full of size.

non_zero, from your explaination that when i perform backup, the transaction log file will be empty. Please re-confirm on this.

how about the solution from adathelad ? IS this can solve my problem ?

Setting Simple Recovery is not a good idea for Failover Recovery..

and ... Deleting any files that SQL server uses is not a good idea...

first we must recover ur db..

open Query Analyzer..

and

sp_detach_db UrDatabasename
go
sp_attach_db UrDatabaseNAme,<FullPath of Database File like 'c:\Program files\Ms SQL server\Data\Databasename_Data.mdf'>
go

this ll recreate ur Log file..

>> Setting Simple Recovery is not a good idea for Failover Recovery.. <<
As the question was how to turn off transaction logging altogether, I wouldn't see this to be an issue - if this was a production critical system then I'd agree, but it def doesn't sound like it is

Transaction Logs are used for Recovery ..

Simple Recovery option is used normally  for non Transactional Databases.
Cuz if an Error Occured and ur database is lost . U must do all changes manually since ur last backup.

Thats why for Transactional Databases( If u do inserts and updates more then Selects) Simple recovery is not recomended..

as i understand , cuz of ur transaction log is full , ur using a Transactional Database .. so its not a good idea to use simple Recovery


Melih SARICA

cyseng - please see here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4l83.asp

This explains the recovery modesl we have been talking about.
Here's a small excerpt:
<QUOTE >
In the Simple Recovery model, data is recoverable only to the most recent full database or differential backup. Transaction log backups are not used, and minimal transaction log space is used. After the log space is no longer needed for recovery from server failure, it is reused.

The Simple Recovery model is easier to manage than the Full or Bulk-Logged models, but at the expense of higher data loss exposure if a data file is damaged.

Important  Simple Recovery is not an appropriate choice for production systems where loss of recent changes is unacceptable.

When using Simple Recovery, the backup interval should be long enough to keep the backup overhead from affecting production work, yet short enough to prevent the loss of significant amounts of data.
</QUOTE>

I'd argue that simple recovery model is best for your needs

so, which is better way to solve my problem. since i need to re-create log file everytime its get full of size and error raise to out on this.

from the answer, i can see that i need to do backup in order to remove the transaction log file. is there any other solution beside backup the database ?