Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2007
    Posts
    56

    Unanswered: Wise Job Scheduling

    Guys,

    I've been asked to properly schedule some jobs on our system for a database following a full recovery model. I realized that the order for some of the jobs might matter and came up with the solution, which is described below.

    Please let me know if this ordering makes sense and/or if you have any general comments/suggestions:

    1. Backup all dbs [in case defragmentation messes something up]
    2. Defragmentation [this will grow the transaction log]
    3. Backup transaction log [backs up trans log and truncates inactive portion of it]
    4. Shrink log file

    Also, I've been previously suggested not to shrink the log file, unless absolutely necessary, as it is resource intensive. In my experience, however, this operation does not seem to be resource intensive at all. It took me 1 second to shrink the transaction log file from 25GB to 2GB.

    Is it regrowing of the log file that will affect performance?

    Also, if I do shrink the transaction log file, is there a suggested value to shrink it to - possibly a function of the database file size?


    Thanks a lot

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    One thought to share; I have a medium (330 GB) vendor-owned database with a custom defrag job that runs weekly. It generates a HUGE amount of translog activity (~ 45 GB in an hour).

    I fixed the size of the translog (no autogrow). It's oversized for normal usage, but undersized for the defrag job. I then set an alert (for transaction log reaching 60% full) and the alert response was to kick off the transaction log backup job. I then set the delay between responses on the alert for 10 minutes (so it would not continuously try to kick off the log backup).

    It's been running in test and prod like this for 2 years with only one hiccup (when we migrated from old hardware to new hardware and I linked the alert to the wrong job in the process.

    Regards,

    hmscott
    Last edited by hmscott; 02-22-07 at 15:08.
    Have you hugged your backup today?

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Shrinking the log file requires almost no resources. Growing the logfile is what hurts. Think of it this way. You can give up your pay and your boss will not complain at all. Now go ask for a raise (i.e. more resources).

  4. #4
    Join Date
    Jan 2007
    Posts
    56
    hmscott: That is a good idea. I did not think about that one!

    Mcrowley: Yes, I now realized that growing the trans log is the problem. So, do you suggest to shrink it or not? How can one tell if it needs to be shrunk, assuming that hard disk is not a big issue, but you don't want to waste it either?

    Thanks a lot guys

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If diskspace is not a problem, I would say you should not shrink the log files. As an exercise for the interested reader, you may want to set up a perfmon to monitor the log usage over time, and see what your maximum required usage is. Only when you have this number in hand should you consider adjusting the size of the logs manually.

  6. #6
    Join Date
    Jan 2007
    Posts
    56
    MCrowley, I am a bit confused. How exactly can you find out the maximum required usage? Do you mean to sort of learn the trend?

    Thank you

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Yes .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •