Shrink Transaction Log

Question

Shrink Transaction Log

stefaan

SSChasing Mays

Points: 616
More actions
December 9, 2007 at 5:06 pm

#181223

How can I shrink the transaction logs on SQL 2000, they just keep growing out of control, I have set a limit for them which it doesn't go past but I can't seem to actually compress them?
THanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

Nikolay Chernavsky Mr or Mrs. 500 Points: 573 More actions · Answer 1

It all depends what you want to accomplish. You need to look what is your RPO objective first.

let say that you promise your clients that you will not loose more than let's 1 hour of data. So, you would put your database into Full Recovery mode and take tranaction log backups hourly. After backup is taken, you can shrink tranaction log safely. It will remove all unsused space and keep it relatively small.

If you do not care for tranaction log at all, you can switch database into Simple Recovery mode and shrink transaction log hourly as well. You need to make sure you take backup of your database somewhat on regular basis. Transaction log will not get shrunk unless database was backed up.

Nikolay

stefaan SSChasing Mays Points: 616 More actions · Answer 2

It is set to full and and the maximum size of the log is 80 megs but it simply will never shrink automatically.

I just can't get it to go down apart from manually shrinking it.

Vivien Xing SSChampion Points: 12297 More actions · Answer 3

Transaction log is 80 MB only? It is so small. Why bother to shrink it?

If it is full recovery mode, should have transaction log backup in place.

Nikolay Chernavsky Mr or Mrs. 500 Points: 573 More actions · Answer 4

Create SQL Job and schedule it to run hourly. Add one step to it to shrink transaction log file for your database.

There is also option on each database if you look on properties --> Options. It's called AutoShrink. It's not recommended to use it, but you can turn it on if you like to try. It's on on all MSDE databases since they are most of the time not managed. In your case i would recommend using SQl job and srinking your log hourly.

Nikolay

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 5

Once you set your transaction log to Full Recovery Your transaction log will not shrink automatically. If you want it that way you must choose Simple Mode but like Nikolay Chernavsky said choosing from Full Recovery to Simple Recovery depends on how much transaction log are you willing to put out. Manually shrinking you transaction log is also different from truncating your transaction log.

Truncating transaction Log

BACKUP LOG t_log_name WITH TRUNCATE_ONLY

- This will free space on your transaction log (not the total log)

Shinking Transaction Log

DBCC SHRINKFILE (t_log_name,amount,retain )

- This will shrink the total amount of your transaction log

"-=Still Learning=-"

Lester Policarpio

Nikolay Chernavsky Mr or Mrs. 500 Points: 573 More actions · Answer 6

True too. 80 MB is nothing. I have logs anywhere between 2 - 10 GB in size.

stefaan SSChasing Mays Points: 616 More actions · Answer 7

Thanks, I'll have to ask then what is the difference between shrink and truncate then as I certainly was under the impression that it was the same thing.

Minaz Amin SSChampion Points: 11052 More actions · Answer 8

Truncating the Transaction Log & Shrinking the Transaction Log details from BOL will help you to understand the concept very clearly.

"More Green More Oxygen !! Plant a tree today"