Transaction logs Bigger Than Data files

Question

Transaction logs Bigger Than Data files

victord

Valued Member

Points: 71
More actions
September 5, 2006 at 7:15 pm

#61727

Hello,

I have been handed over a database built by external consultants for a microsoft sharepoint project. The database is configured with the transaction logs almost 100% bigger than the data files. What is the best way to resolve this problem.

Thanks

vdavid

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

You don't provide sizes, so it's hard to give you a good answer. If the database/log was 5MB/10MB, my answer would be different than if it was 5GB/10GB.

Bottom line, every transaction is 'logged'. If your database is heavy on transactions, then a large log file is handy. Only you can decide this. We can help with the decision, but only if we have enough info to do so. What are the sizes and what type of transactions will occur? Is the db going to be read-only? Will there be lots of updates? Will there be lots of inserts?

Do the vendors have any 'builtin' maintenance plans? If they are frequently doing index rebuilds, then you will need lots of log space.

-SQLBill

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 2

why was the transaction log build that way? Need to find the reason from the "consultants". Do you have long running Xaction? How is the Xaction log set up. If you know the answers for all that we can SHRINK" Xaction log if it was accidentally setup that way.

Thanks

Sreejith

Peter Kuzniewski SSC-Addicted Points: 418 More actions · Answer 3

If your question is 'How do I shrink the logfile?', you can do that with 2 commands:

Backup Log dbname_here with TRUNCATE_ONLY

DBCC SHRINKFILE ('logfilename_here', new_size_in_mb_here)

If you have log shipping turned on, this will break the sequence of log numbers, and that's another issue, but easily fixable.

This shrinks the logfile to 200 mb (you can set any size you want). If you set autogrow, and the logfile has been properly sized initially, this should take care of it.

Peter

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

If your databases aren't using simple recovery mode, you must have a regular maintenance plan that backs up transaction logs or the log files will continue to grow. This is in addition to a full backup maintenance plan. You can check this with:

SELECT

name, DATABASEPROPERTYEX(name, 'recovery') FROM master.dbo.sysdatabases

All databases that aren't listed as SIMPLE must be included in a maintenance plan with a log backup job, and the log backup job must be scheduled, enabled, and not failing (check the job history).

If replication was enabled for a database but has been stopped or broken, the transaction log will continue to grow no matter how many backups you do.

Shawn Wilson Ten Centuries Points: 1271 More actions · Answer 5

You really need to find out if it is a problem or not. We have transaction logs that can under certain conditions grow 1GB / minute.

It may be that large because of the initial setup / loads by the consultants.

Is your transaction log under a backup plan?