Transaction log file growing quickly

[alebox20]

One of my databases has about 2gb of data file, but suddenly the transaction log file has grown more than 30gb.

How can I identify this growth?

Environment: Windows and SQL Server 2000. Complete backup on Monday. Differencial from Tuesday to Friday.

Thanks.

 

[ptheriault]

you need to backup your log file.

Create schdule job to backup your TLOG. Then after you backup your log you can shrink it back to normal size.

use DBCC SHRINKFILE(TLOG_name, truncateonly)

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

What recovery model is your database set for?

Differential database backups do not effect the transaction log. If your database is in full recovery you need to take Transaction Log backups.

If retaining the log is not importnat change the recovery model to SIMPLE.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

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

http://www.mrdenny.com

 

[SQLBill]

Are you using Maintenance Plans or scripting the maintenance yourself? Either way, check to see if you are doing Reindexing. That will cause the files to grow huge.

-SQLBill

Posting advice: FAQ481-4875

 

[alebox20]

Worked! Started to backup up the tlog and then shrink the file.

thanks

 

[ptheriault]

I would suggest you read the following link to learn a bit about point in time recovery.

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

- Paul

- If at first you don't succeed, find out if the loser gets anything.