Resizing Transaction Log

Question

Post reply

Resizing Transaction Log

Frank Rueff

SSC Enthusiast

Points: 155
More actions
May 10, 2005 at 12:13 pm

#88394

First off, I would like to say thank you for everyone's help in the past. This is a truly outstanding place you guys have setup.

On to the current issue. The transaction log grows from 2MBs to 34GBs within a day, eats up all disk space, crashes the DBs, and I get a call. This is a problem with the vendor application. One of them set the size of the transaction log to 11GBs to start. I do not have any backups that do not have this setting defined. We are running SQL2K SP3a. I have run a script that wraps the log back around to reduce the size, this has been sucessful on all the other logs I haved used it against.

Shrinking does not work either. Anytime I try anything it returns a message of cannot go below preset size. The vendor wants to set the DB to simple mode and restore from that to reset the initial size.

Is there a different way to do it on a running DB?

Thanks

Frank

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

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

What is your database recovery mode currently?

If it is in FULL recovery mode, you have to setup a job to backup the transaction regularly.

You can also change it to SIMPLE as your vendor suggested and let system to truncate the log itself if you do not need to recover your database to the point of failure.

The large transaction log could also be generated by a single transation, you need to find out monitor log growth behave and try to link to any possible activities that may cause.

Did you use dbcc shrinkdataabse or dbcc shrinkfile? Run backup the log or backup log with truncate few time the start to shrink. It works all the time for me.

Frank Rueff SSC Enthusiast Points: 155 More actions · Answer 2

Currently it is FULL. I have a job that backs the log up every 15mins. It is mulitple transactions, data is being fed into this database constantly. I used shrinkfile against the log.

My problem is that it shrinks down to 11GBS. Since the initial size is 11GBs in the properties it won't go below that size. When I try to change the properties, it states that I can't go below the current size of the log. I'm in a catch-22.

Thanks

Frank

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

Allen Cui-55137

SSC Guru

Points: 51650

May 10, 2005 at 12:52 pm

#557870

DBCC Shrinkfile can shrink the log file to any size you want.

Frank Rueff SSC Enthusiast Points: 155 More actions · Answer 4

Frank Rueff

SSC Enthusiast

Points: 155

May 10, 2005 at 12:54 pm

#557872

I give that a try again.

Thanks

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 5

First of all the reason for increase in the size of trnascation log is beacuse of Full Recovery. Are you running a bulk insert or optimizer on the database ? Do you have a backup if not then the transaction log will increase. I do not know your complete the purpose of your database so I will not recommend a backup policy but to reduce the size of log this time.

Backup log "DBNAME" with Truncate_Only

DBCC Shinkfile('Name of Log File')

Backup the Database (this is important)

Note: Personally I do not like to schedule a job to shrink file daily.

Amit

Kindest Regards,

Amit Lohia

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 6

The only time I would think about shrinking the transaction log is when someone had done something stupid to fill it up (occasionally even me).

So long as you make sure it's sized right to begin with and have regular backups taking place then there's not going to be that much call to shrink it.

Don't expect the DBCC Shrinkfile to shrink the file immediately, it can take quite a while to do it depending on where the data was being written to in the file.