sql transaction log

[phaldisa]

Sql server 7 transaction log grows at a rate of 2 gigs daily
but the datbase is only 3 gigs big.what can cause this

Phaldisa

 

[silverballs]

It sounds simple and obvious but worth checking if you haven't - check the properties for the database, on the transaction log tab a limit can be set for the rate of growth, it may be that this has been set to 2000 Mb as well as being set for unrestricted growth. You may want to reduce the size of this or perhaps set it to grow automatically by 5 - 10%

 

[handlewithcare]

We experienced this problem when our SQL backups were failing due to file locking problems on the server, solved by deleting the old backups. You might also want to try increasing the number of transaction backups if this doesn't work.

 

[Inwoner]

The problem might also be in huge transactions that do rollbacks.

Constantly check
select trancount from master..sysprocesses where trancount>0

 

[miq]

hi,
set some limit to the transaction log file growth. but remember if your transaction log get full then the users will not be able to do anything and will be locked out. So, do backup transactio log on regular basis.
You can select simple recovery model as well but its recommanded not to use it.
Also, truncate log on checkpoint option can be set to automatically remove completed transactions from log file. Its also not recommnaded but can be used with simple recovery model.

best of the best is that you backup log file regularly and also set an alert whcih can backup transaction log if it reaches certain percentage like backup when 75% full. for this a job has to be attached with alert.

bye
miq

 

[tlbroadbent]

Many things can cause transaction log growth. Inserts, deleted and updates all contribute. In addition, creating or rebuilding indexes causes log growth. Regular, frequent transaction log backups can reduce the growth.

It is not often advisable to limit growth on a production system if high availablity is the goal. A better practice is to perform more frequent log backups. Frequent help in many ways. They use less system resources and complete faster. They provide better recovery.

For more info on transaction logs, check out a couple Tek-Tips FAQs and the additional articles referenced in the FAQs.

faq183-1534 - Shrinking Databases and Logs - SQL 7 and Higher

faq183-1784 - How Do I Develop a Backup and Restore Strategy?

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.