Help with "Conflicthing Schedules"

kepr00

Hello

I have a server that is currently hosting 60 dbs. I am trying to schedule a Transaction Log backup every hour. Every time I set the job up, it raises an error in the display that the schedule is conflicting. I have tried splitting the job into two different jobs, but this does not seem to help. Is it not possible to do hourly transaction log backups over a certain number of databases?

Ben Pinchen

I've looked into this and to further our conversation. The conflicting schedules in the GUI are just warnings and the backups should still run. The warning is there to let you know that two jobs will be running at the same time which could possibly cause issues. For example if the jobs are both trying to update the msdb database then there's the possibility of locks. To clarify, you aren't able to run multiple jobs on the same database at the same time but you'll be able to run different database jobs concurrently. So, yes, as you're running t-log backups on 60 different databases you should be able to run the job split into two jobs at the same time.

I think that the issue is not the warnings that you get, it is the fact that the job isn't running at all. When you split the job into two can you just schedule one job, on it's own, to run successfully? If so what happens when you offset the second job, say by 15 mins? Does this then cause the jobs not to run?

kepr00

When you split the job into two can you just schedule one job, on it's own, to run successfully? If so what happens when you offset the second job, say by 15 mins? Does this then cause the jobs not to run?

When I split the job into two I can run one of the jobs.
Scheduling the 2nd job causes the "Conflicting Schedule" message to appear.
This happens no matter the offset. I tried offsetting for both 15 and 30 minutes.

It seems that once the "Conflicting Schedule" situation is detected that the job ceases to run.

Is SQL Backup a singleton class? If so, it does not matter how many schedules I create, it could never run more than one at a time. This would indicate to me that there is a fixed limit on the time SQL Backup Jobs can consume on any given Server.

Are there issues with running one or more Transaction Backups on different databases in parallel? It seems that this should effectively extend the amount of SQL Backup time that I have available per server.

kepr00

I think I finally got this working. Looks like I just needed to be a little more proactive in monitoring the space that the log files are consuming.

It seems that simply backing up the transaction log and removing the inactive entries with SQL Backup does not shrink the log file very much. Some times the file shrinks a little, but the overall trend is that the file grows even though the actual data in the Log File is < 10% of the total file size. What I had to do was set up a daily job to monitor the size of the logs using DBCC sqlperf (logspace)

I have a server that hosts over 60 db instances. What happened in my particular instance was that once the transaction logs reach a certain size, SQL Backup stoped backing up the Transaction Logs because the time it thought it should take overlaped the next start. Once this happened, the transaction logs filled up and continued to grow.

The only way that I found to correct it was to stop all SQL Backup schedules and use dbcc shrinkfile and BACKUP LOG <DBName,SysName,dbName> WITH truncate_only on all my databases.

Once the database transaction logs were a manageable size again, I restarted SQL Backup Transaction Log backups. Now I monitor the log size daily. Once they reach a certain size I just have to use DBCC ShrinkFile to reclaim some of the empty space.