none
Delay Log Backups while Full Backup takes place

    Question

  • I have the following Maintenance setup accross about 50 servers.

    Subplan 1 : Full sunday backups
    Subplan2 : hourly log backups everyday
                     hourly log shrinks everyday

    On sunday these 2 jobs will obviously overlap and then i get an error which states that the log shrinks cant run in serie with the backup (which is understandable).
    I am not worried about these errors becaues they will just pick up again in an hour, but still we get an email notification that it failed, and my seniors dont want that.

    They only way i can think of how to resolve this accross all 50 servers is by running a script on all of them including 2 additional steps in the backup jobs to disable and enable subplan 2 job.

    Does anyone else have a better idea for this? Can i use SSIS expressions somehow ?


    Jack of all trades, mastered by my wife.

    Wednesday, May 15, 2013 7:54 AM

Answers

  • You could try the following expression in the precedence constraint between the log backup task and the log shrink task:

    DATEPART("dw", GETDATE()) != 1 || DATEPART("hour", GETDATE()) < 9 ||

    DATEPART("hour", GETDATE()) > 11

    I missed one piece that your point made, so I corrected it. 


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com


    • Marked as answer by Dolfie99 Wednesday, May 15, 2013 12:32 PM
    • Edited by Russ Loski Wednesday, May 15, 2013 12:36 PM
    Wednesday, May 15, 2013 12:21 PM

All replies

  • Why do you do a log shrink?  That is extremely bad practice on a regular basis.

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, May 15, 2013 9:33 AM
  • May i ask why you say that? We have been running it like that from some time and didnt have any problems, even in a DR situation.

    Jack of all trades, mastered by my wife.

    Wednesday, May 15, 2013 9:39 AM
  • Your transaction logs are going to grow then shrink then grow then shrink.  Every time it grows it uses io to zero out the file space.  Also, there is a chance of disk fragmentation.


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, May 15, 2013 9:47 AM
  • Without a log shrink and with the transaction log backup, the file will grow to a certain point then should stop growing until you get a very large transaction.


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, May 15, 2013 9:55 AM
  • Hi Russ

    Our DBs are not that active that i should worry about the I/O and they are all replicated, which means that the shrinks happens only really over weekends when the log is not being locked up by stuff to replicate. But problem is still that it overlaps with the full backup. How do i get around that?

    Thanks in advance for your time.


    Jack of all trades, mastered by my wife.

    Wednesday, May 15, 2013 9:55 AM
  • You could try the following expression in the precedence constraint between the log backup task and the log shrink task:

    DATEPART("dw", GETDATE()) != 1 || DATEPART("hour", GETDATE()) < 9 ||

    DATEPART("hour", GETDATE()) > 11

    I missed one piece that your point made, so I corrected it. 


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com


    • Marked as answer by Dolfie99 Wednesday, May 15, 2013 12:32 PM
    • Edited by Russ Loski Wednesday, May 15, 2013 12:36 PM
    Wednesday, May 15, 2013 12:21 PM
  • Figured out how to use the expressions to delay the log shrink.

    If you use this in your precedence constraint, it will not flow to the next job on sundays between hours 15 and 17. I still dont know if this is the best way.

    datepart("weekday",@[System::ContainerStartTime])   ==  1 &&
    (datepart("HH",@[System::ContainerStartTime]) < 15 ||
    datepart("HH",@[System::ContainerStartTime]) > 17 )


    Jack of all trades, mastered by my wife.

    • Proposed as answer by Russ Loski Wednesday, May 15, 2013 12:33 PM
    Wednesday, May 15, 2013 12:28 PM
  • Thanks Russ, was posting prob same time as you :)


    Jack of all trades, mastered by my wife.

    Wednesday, May 15, 2013 12:32 PM
  • Best is a relative term. First, if you have a broad enough range of time, then you are unlikely to overlap with the backup.  I'm guessing that at most once a year you will get an error.  Second, let's say that you have too broad a range of time excluding the log shrink, your transaction log shrink will happen sometime on Sunday, just not during the range you block off.  Even if you think you need a transaction log shrink and think it is a good thing, missing one or two extra log shrinkings on Sunday is not going to do any damage.

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, May 15, 2013 12:41 PM
  • For those reading this post, even months or years later...

    Never schedule to shrink your transaction logs, unless there is a real & valid reason.

    If you can afford your transaction log to reach a certain size, then better empty it (by a transaction log backup - this should be done regularly in case your DB is in Full Recovery model). It will most likely reach that big size again, so why shrink it anyway! It will cause unecessary I/O and fragmentation.

    Monday, September 09, 2013 8:48 AM