HUGE Transaction Log size after Update Statistics is run
-
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
-
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
-
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
-
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
-
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
-
"...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
-
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
-
Great to know I'm not the only one that has those concerns
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
-
Hey Adam, your defrag script sounds great. Have you considered uploadeding it for others to look at?
Francis
-
Fhanlon, look up DBCC SHOWCONTIG under books online it gives you a good start on the procedure.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply