Need Transaction Log Clarification (SQL 7)

[Daurkin]

I've been reading many post about transaction logs that have grown out of control. I'm now in charge of our SQL server with a database that's about 5 GB and that's normal. I just noticed my transaction log is very large in comparison as in 42 GB. Last weekend I created a new log file and within a week it's already 1 GB. This log normally doesn't exceed a couple hundred KB. I'm not sure was was done recently to make this jump.

1. I'm looking for the option to change recovery to Simple but maybe it's only in SQL2000 because I don't have it.

2. The maintenance schedule only backups the database and not the log file. Should I change this, and what is recommendation?

3. My transaction log is set with option to grow automatically and at 10% each time. What negative things could happen if I don't let it grow?

I understand everyone's system is used differently, but my old administrator no long works here. I'm also struggling to figure out what has been changed to cause it to grow so much. If there is a way to troubleshoot the log, please let me know.

 

[mrdenny]

1. The option for simple recovery is there in SQL 7, they just changed the names for SQL 2000. If you set the database to simple recovery then you won't be able to backup or restore from the transaction logs.

2. Transaction logs should be backed up on a regular schedule. If for nothing else but to clear out the transaction logs.

3. If your transaction log fills up you will not be able to do any insert/update or delete statements.

The reason for backing up transaction logs is to decrease the time for backups through out the day. Common practice is to do full backups daily, and transaction log backups on a schedule from every 5 minutes to every 12 hours. You select how often you need to backup the log based on how much impact to the company loosing that much data will have. If loosing 1 hours worth of data will cost the company $100,000 then you backup the log every 5 minutes to reduce the potential loss to $8,333.00. If loosing 1 hours worth of data will cost the company nothing, then you start the thought process over using 6 hours as the base line.

Odds are the massive growth is caused by the fact that the old admin had transaction log backups happening and they were disabled for some reason.

If you don't backup the transaction log the log will continue to grow until the drive runs out of space.

Denny

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