HUGE Transaction Log size after Update Statistics is run

Question

Post reply

HUGE Transaction Log size after Update Statistics is run

Andy Garratt

SSC Enthusiast

Points: 148
More actions
June 3, 2004 at 7:55 am

#84593

Hi

I am the DBA for a database holding CRM data, the database is currently about 4Gb and when we was using SQL 7 we used to rebuild the indexs and update the statistics every night to help performance.

After upgrading to SQL 2000 we tried to do the same thing, the first thing we noticed is that there isn't an option to rebuild the indexes in the maintenace plans, the second thing we noticed is that after the maintenance plan updates the statistics (using a 75% sample) the transaction log grows to roughly the same size as the database, in this case 4Gb.

Is it still worth while updating stats in SQL2000 or should it just cope with the auto update option on? If it is worth updating them is there a way to avoid the HUGE transaction log that it makes, the server has a maintenace plan backing up the db and transaction log to a seperate server so we are seeing a 9Gb backup in the morning

Any help or advice anyone can give would be appreciated

Andy

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

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

Andy Garratt SSC Enthusiast Points: 148 More actions · Answer 1

Andy Garratt

SSC Enthusiast

Points: 148

June 3, 2004 at 8:11 am

#508840

Correction, we do also reorganise the data and index pages while maintaining the original amount of free space, just in case its that thats causing the transaction log to grow.

Andy

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 2

switch to bluk-logging before performing your maintenance !

full-logged it logs everything !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 3

that's what I assumed.

Normaly, just the statistics will not cause such log-load.

dbreindex rewrites it all. this log-overhead can be reduced by switching to bulk-logged recovery mode.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Adam Tice SSC Journeyman Points: 87 More actions · Answer 4

There is a great article by Bill Wunder on DBCC INDEXDEFRAG and DBCC DBREINDEX. He touches on transaction log growth. See http://www.sswug.org/see/18075

Personally, I built a series of sprocs that automatically defrag indexes once they reach a certain Logical Frag % and are greater than a spedified number of pages. Between each defrag, the job checks the size of the log file and truncates & shrinks it if it grows above a specified threshold. This keeps my log from growing too large.

Adam

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 5

"...size of the log file and truncates & shrinks it ..."

I hope your DRP can cope with that

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Adam Tice SSC Journeyman Points: 87 More actions · Answer 6

Good point - we actually perform this process during our weekend maintenance window when user volume is quite low. Acceptable risk? maybe...

Our backup scheme includes 15 minute tran log backups. I guess we could disable that job while the defrag process was running, and manually run those backups when the log grows past x size. That would ensure any interruption would be recoverable.

Adam