How do I clear the transaction log when it is full?

[copperkitten]

Hello,
Does anyone know how can I clear the transaction log when it is full.Because everytime the transaction log is full,The user's cannot processed any transactions such as editing or adding some values to a table.I am currently using MS SQL Server 2000.My Server name is PCPI_NT.My Database name is Quality_Stat.

Please help me ASAP.Thanks.

 

[SonOfEmidec1100]

I would set the recovery mode to simple, unless you have a good reason not to.

You should get some other ideas from thread962-968733

 

[copperkitten]

Mr. SonOfEmidec thanks for the help.But I already done that before but there are some problems that occur.Well first I detach the database from the server then.I rename the .ldf file of the database,then I attach the database to the server.But when I attached to the database to the server,other built-in functions of the database doesn't work such as the FREETEXT() that i used to search for certain word in a field.
Why does it happened and How will the function FREETEXT() be recognize again.? I only have a little knowledge in this MS SQL Server because i only inherit these system from the previous programmer.Thanks.

 

[SQLSister]

For Freetext to work, you need to have full text indexing turned on. Fulltext indexes are kind of comlicated and if your database uses them, then you need to read up onthe subject in BOL.

Transaction logs. I do not recommend simple recovery mode if your database is transactional in nature. If you turn off logging, you cannot recover to a point in time.

Transaction logs fill for a couple fo main reasons. First is that you are not doing transaction log backups. These are differnet from database backups. If you do not backup the transaction log , it will grow until you use the entire hard drive. ANother reason the log fills is if you either do not allow growth or do not allow it to grow fast enough. Check your database properties to see if this is happening. Typically it happens when you do a large data import of some type.

Some useful information on Transaction logs is contained inteh following FAQ:
Database and Log Files
Shrinking Databases and Logs - SQL 7 and Higher
faq183-1534


Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief