Maintenance plan will not do a transaction log backup

The issue is that I cannot get a Maintenance Plan to do a transaction log backup on one of the SQL instances, but it works fine on the other two.  If I execute the SQL Agent job, or the Maintenance Plan itself, it never completes, there are no errors displayed and no backup file is created.  However, I can copy and paste the SQL created by the View T-SQL button into SSMS and it executes fine, creating the log backups

We are running SQL Server 2012 Enterprise 11.0.5058 x64 running on Windows Server 2012 R2, 3 named SQL instances in a 2-node cluster.

There are no errors logged in either the SQL error logs or Windows Event logs.  The databases are all set to Full recovery model.  I have deleted and recreated the Maintenance plan several time to no effect.   We only recently moved to the new hardware and the logs were being backed up fine on the old cluster.

Thanks in advance for any suggestions you may have.

August 22nd, 2015 3:09pm

The issue is that I cannot get a Maintenance Plan to do a transaction log backup on one of the SQL instances, but it works fine on the other two.  If I execute the SQL Agent job, or the Maintenance Plan itself, it never completes, there are no errors displayed and no backup file is created.  However, I can copy and paste the SQL created by the View T-SQL button into SSMS and it executes fine, creating the log backups

We are running SQL Server 2012 Enterprise 11.0.5058 x64 running on Windows Server 2012 R2, 3 named SQL instances in a 2-node cluster.

There are no errors logged in either the SQL error logs or Windows Event logs.  The databases are all set to Full recovery model.  I have deleted and recreated the Maintenance plan several time to no effect.   We only recently moved to the new hardware and the logs were being backed up fine on the old cluster.

Thanks in advance for any suggestions you may have.

I would not bother with the maintenance plan. I recommend Ola's maintenance plan to my customers.

It's completely free and works wonders! https://ola.hallengren.com/  He also did a SQLPASS presentation about his scripts that I'm sure you can find online somewhere.

I'd go this

Free Windows Admin Tool Kit Click here and download it now
August 22nd, 2015 3:24pm

I'm with Daniel on this. 100%.

If you, however, insist on keeping with MP, then I would run a trace on a server that works as well as on this server. Include SQLBatchStarting and try to determine on which SQL command it hangs...

August 22nd, 2015 3:34pm

Thanks for the quick reply.  I actually already use some of his other scripts, but I had forgotten all about his backup solution.  It's just my luck that I just completed migrating all our servers to new hardware and the instance that is causing the issue was the very last one in production, so I guess I'll go back and switch to Ola's maintenance sprocs in all of the environments.
Free Windows Admin Tool Kit Click here and download it now
August 22nd, 2015 3:41pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics