Question about Optimizations

• kwiley

  SSC-Addicted

  Points: 437

  More actions

  November 18, 2004 at 7:58 am

  #161940

  Hello,

  We just upgraded a SQL Server from version 7.0 to 2000, sp3a.  Our database maintenance plan for our primary database on the server includes running the database optimizations.  The optimizations run three times per week in the middle of the night.

  Since the upgrade, when the optimizations run, the transaction log is growing to 15 GB (the database itself is only 22 GB).  The first time it ran, I did not think much about it, thinking that after the upgrade process, perhaps some reindexing was necessary.  So, I created a new transaction log for the database using sp_detach_db and sp_attach_db.  The log was about 150 MB when I left yesterday.

  The optimizations ran again this morning, and the transaction log again grew to 15 GB.  Does anyone know what might be causing this?  I do not want my transaction log to be that large, and I don't want the transaction log backup after the optimizations to be that large as well.

  The options set on the optimizations in the maintenance plan are:

  Reorganize data and index pages - Change free space per page to 10%. 

  That is the same as it was set when we were on SQL Server 7.0.

  Any insight is greatly appreciated.

  Thanks,

  Ken

• Taj

  Mr or Mrs. 500

  Points: 596

  More actions

  November 18, 2004 at 9:10 am

  #530688

  Are you running anything else with your maintenance plans. Like Backups or integrity checks. Remember Transaction log, records any changes to the Database.

  Try also backing up the transaction log in your maintenance plan right after you run the optimization jobs. That will bring down transaction log. Also bring down the free space to 5%. 10% is the default and is Ok for small DB's.

  Hope it helps..

  
  Tajammal Butt

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  November 19, 2004 at 8:26 am

  #530847

  If you rebuild all the indexes you'll get a large transaction log, one of the downsides. - you could try putting the database into simple recovery model during the index rebuild, then put it back into full and backup.

  I don't actually use the maint plans preferring to use t-sql calls in the job steps - how much difference it makes I don't know.

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/

• Johan Bijnens

  SSC Guru

  Points: 135368

  More actions

  November 19, 2004 at 1:33 pm

  #530904

  I switch all db's to "bulk logged" in the first step of the maintenance job (dbreindex/indexdefrag).

  then perform maintenance

  then switch back to "full" when needed

  backup after.

  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