SQL Server Performance

Wise Job Scheduling

Discussion in 'General DBA Questions' started by sql_er, Feb 22, 2007.

  1. sql_er New Member


    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. MohammedU New Member

    You loose point in time recovery if you truncate the log...
    Why do you want truncate the log? instead you can run the tlog backup and then srhink the file...

    If you are not worried about point in time recovery then why you want to keep your db in full recovery mode? change it simple recovery mode...

  3. satya Moderator

    Don't perform step 4 as it will have negative effect while other process is trying to increase the log size, if the disk free space is not a problem then leave the Tlog size to as is.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page