transaction log growing unexpectedly 1

[shaminda]

We have SQL Server 2000 with about 40 databases. All databases are set to recovery model of full. In one of our databases the transaction log is growing 0.10MB every 30 seconds. We shrinked the transaction log this morning but now it is 137.73MB. The biggest other transaction log we have is 10.00MB. We can't figure out why it is growing. Does any body know why it is growing and a have solution to stop the transaction log from growing other than shrinking the transaction log file every day?

And this database is not our biggest database either. We have about 6 - 8 databases bigger than this database.

 

[gmmastros]

Are you backing up your transaction logs? Instead of shrinking the transaction logs, you should be backing them up. This will recover the space in them and prevent them from growing too big.

In addition to that, you should find out what is causing it to continually grow. I would recommend you run [!]sp_who2[/!] in a query window and try to determine what is running against this database, and then investigate why it's causing the transaction log to grow.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[shaminda]

Yes we backup up the transaction log every Sunday. By Thursday just one file ate up all our disk space. This is the second week this happened.

 

[ramam1]

What's the growth rate of the DBs themselves?
I backup transaction logs every five to sixty minutes depending on the DB in question.
The transaction logs have a copy of every transaction committed; that means if you change the same one field over and over each and every change will be in the transaction log.

 

[shaminda]

Yesterday because of the snow storm production was down so I reindexed the database and the transaction log file grow by 4126.12 MB’s. Is there something wrong with my indexes?

 

[ramam1]

What's the growth rate of the DBs themselves?

 

[mrdenny]

You should backup the transaction log every few minutes. The amount of time between log backups should correspond to the amount of data which the company can afford to loose in the event of a database or system failure.

If you are only backing up the transaction log weekly, you should just change the database recovery mode from FULL to SIMPLE and backup the databases daily.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[shaminda]

The data file grows on average 24 MB a day.

We had the recovery model set to simple. One day the transaction log file was 201 GB. The server ran out of disk space because of this one transaction log file and it shut our whole plant down.

 

[shaminda]

We are backing up the transaction log file twice a day only on this database. The rest of the databases once a week. There are databases bigger than this one but its transaction log is only about 1MB.

 

[mrdenny]

If the transaction log is growing there is a transaction which is being left open, or is taking a long time to run.

If you don't need point it time recovery change the log back to simple. This will cause the transaction log to automatically overwrite committed transactions as soon as the database checkpoints the committed transaction to disk. They key word there is committed.

If for example someone starts a transaction, and updates a single row then goes home without committing the transaction the log will continue to grow until that transaction is committed or until it is rolled back. This could also be caused by a bug within your application code where it is starting a transaction, doing some stuff getting an error message, and the error handling within the application doesn't trigger a commit or rollback and work just continues. I've seen stuff like this a few times.

How big is your database? How big is your largest table? Do you do reindex or index defrag operations on a regular basis?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[shaminda]

The size of the database is 4292 MB. The largest table is 800MB. We reindex the database once a week on sundays.

 

[mrdenny]

With the largest table being 800 Megs the transaction log will need to be at least that large for when the reindex process runs.

If you aren't backing up the log frequently the log will continue to grow. As every row is inserted, updated or deleted the change will be logged into the transaction log. If one value of a row is updated 100 times, there will be 200 rows in the transaction log. 100 with the old version, 100 with the new version (I'm over simplifying here, but you get the idea).

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog