?
Solved

How to turn off transaction log in SQL 2000

Posted on 2005-05-09
9
Medium Priority
?
1,868 Views
Last Modified: 2012-05-05
i having problem that my transaction log file get full of size. how to turn off this service ? or any way to solve the problem.

0
Comment
Question by:cyseng10
  • 4
  • 3
  • 2
9 Comments
 
LVL 23

Expert Comment

by:adathelad
ID: 13958064
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.


0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 13958079

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



0
 
LVL 1

Author Comment

by:cyseng10
ID: 13958103
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 ?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 19

Expert Comment

by:Melih SARICA
ID: 13958157
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..

0
 
LVL 23

Expert Comment

by:adathelad
ID: 13958179
>> 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
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 13958267
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
0
 
LVL 23

Expert Comment

by:adathelad
ID: 13958323
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
0
 
LVL 1

Author Comment

by:cyseng10
ID: 13959748
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 ?
0
 
LVL 19

Accepted Solution

by:
Melih SARICA earned 150 total points
ID: 13959824
to create ur Log File and Reactivate 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


and

better way depends on what u want.. and what ur db does..
they r all explained above .. by me and adathelad  

Melih SARICA
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question