sql maintenance task fails

Could you attache the logs of the task / or the configuration of the failed subplan?
Feel free to blackout any information you feel may be personal (Especially DB names, IP Addresses and such)

See the maintenance plan and sql server agent job history logs for details

Did you check the job history for details? It should be there in the logs what caused the error.

hi, is this what you need?:
Date,Source,Severity,Plan Name,SubPlan Name,Task Name,Duration
11/12/2015 08:56:36,,Error,Backup,Subplan_1,,00:00:06
11/12/2015 08:56:36,,Error,,,Maintenance Cleanup Task (SQLSERVER),00:00:00
11/12/2015 08:56:30,,Success,,,Back Up Database (Full) (SQLSERVER),00:00:06
11/12/2015 08:53:32,,Error,Backup,Subplan_1,,00:00:11
11/12/2015 08:53:32,,Error,,,Maintenance Cleanup Task (SQLSERVER),00:00:00
11/12/2015 08:53:26,,Success,,,Back Up Database (Full) (SQLSERVER),00:00:06

Where did you get this information from?

I'm afraid this is a query that shows you when did the plan run, and what was the end result.
not the actual error log.
Use this query to find the location of the error log:

USE master
GO
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc' 
GO

Select allOpen in new window

this is from the log viewer for the job (view history)

2015-11-12 00:00:48.67 spid18s     This instance of SQL Server has been using a process ID of 2052 since 01/09/2015 15:49:23 (local) 01/09/2015 14:49:23 (UTC). This is an informational message only; no user action is required.
2015-11-12 08:52:59.13 spid65      Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
2015-11-12 08:52:59.14 spid65      Using 'xpsqlbot.dll' version '2009.100.1600' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2015-11-12 08:52:59.36 spid65      Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
2015-11-12 08:52:59.39 spid65      Using 'xplog70.dll' version '2009.100.1600' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2015-11-12 08:53:32.06 Backup      Database backed up. Database: DB1, creation date(time): 2012/04/18(14:42:39), pages dumped: 16705, first LSN: 1526:11489:1, last LSN: 1526:12123:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\Backup\DB1_backup_2015_11_12_085326_6133342.bak'}). This is an informational message only. No user action is required.
2015-11-12 08:56:36.37 Backup      Database backed up. Database: DB1, creation date(time): 2012/04/18(14:42:39), pages dumped: 16713, first LSN: 1526:11489:1, last LSN: 1526:12149:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\Backup\DB1_backup_2015_11_12_085630_3986059.bak'}). This is an informational message only. No user action is required.


it doesn't show any error (that i can see)

Neither Can I.
What exactly does the clean up do?
Can you attache a screen shot of the cleanup task settings?

Yes, no errors. Did you confirm you are checking the logs for the correct job?

the clean up removes stale backups that are passed the expiry date.

the log file by using i the query that David posted, and copied all the events from today.

the clean up removes stale backups that are passed the expiry date

Which expire date? Are you backing up with date expiration?
The one you post just clean backup files older than 1 day. And why you want to do that? Are you copying the backup files to another destination? Otherwise what's the idea do keep only 1 day of backups? If today is Monday how would you restore to last Friday's situation?

hi, yes its an off site backup. we back up to a single location on a local drive, The software copies it off then the next day it creates a new one and cleans the old one up. The first task creates a backup with an expiration date of one day, the clean up task runs after it and deletes the last one.

We use veeam to back up the server on 4 day retention so that's there for an issue but this is pure disaster recovery.

The first task creates a backup with an expiration date of one day
The software copies it off (....)

Why copy it if the expiration is set to one day? Will be useless anyway. Do you really want to set an expiration date?

Btw, you don't need the cleanup task at all. Put the software to move the file instead of copying it or perform the backup to overwrite the backup file so it will recreate the file.

will the expire date stop it from being recovered?
At the moment we are using cobian to copy the file across because it can delete it.
My original issue was that i need sql to back up to a NAS which is accessible on the network but as sql has a very basic backup screen, it wouldn't let me. the whole thing could be much more simpler if there's a way round it.

Vitor Montalvão is correct.

There's no point in setting an expiration date assuming you are going to remove the backup itself. I suggest you edit the backup plan to remove that expiration date, and then force run the plan just to see if it fails. I'd even go further and temporarily move the existing bak files to a different folder to make sure that the ones created with the wrong settings won't affect the cleanup.

i have successfully performed a backup by query. can i set that to over write the backup automatically and how do i schedule the query to run?

In SQL Server Agent, create a new job. Add a step to run the script then schedule the job to run at the desired schedule.

If you want you can follow MSDN article on how to Create a Job.

I'm just setting it up now. how do i use the INIT command to overwrite backups? does it precede the backup database command or does it go after?

brill. thanks all. i have set the task on the schedule, we shall see if it creates the backup tonight when the network is quiet.

hi, it's giving me access denied errors in the history log. but i can't see where to give it extra permissions. i can access the destination folder from the server at the time of backup and with the sa account the db is using but it still gives denied

You can access but how about the SQL Server service account? Is the one that need access.

hi, i have been away but come back and it has worked on all but one server. The server in question is running the step as a local sql user (which has no permission to the backup location. is it possible to add a user to the run as box? I have tried the field under advanced but it didn't work.
Thanks

Local SQL logins doesn't have access to operating system files. Can't you change the job owner to SQL Server Agent account?

hi It is already
Capture.PNG

You have an active directory user named sa?

yes,
this is the error:
RemoteBackup,Error,1,<SQLSRV\DB>,RemoteBackup,Script,,Executed as user: NT SERVICE\SQLAgent$SQL2012. Cannot open backup device <BKP LOC>. Operating system error 5(Access is denied.). [SQLSTATE 42000] (Error 3201)  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.,00:00:01,16,3013,,,,0

Just for testing purpose, can you change the job owner to sa (just sa, no domain)?

hi, still the same result

Really? Is the backup folder structure different from SQL Server files and database files? If so, you'll need to verify the permissions on the backup folder and sub-folders.

It's just going to "backup server\backup share" the other sql servers have the same job and step (script copied and pasted). the only difference is the user it's run as. I can't change it because the server relies upon the local account it uses. i just need to change what user it uses to copy to the backup location. (If possible).
The backup folder has the Domain\sa account as full R-W-X.

Problem is that when accessing network share the SQL Server engine uses SQL Server service account and not the user that is owning the job. You'll need to give permission to that SQL Server instance service account.

the account is local on the server. Is it possible to give a local account a permission on another server?

No, local account no. Or you make the share available to everyone or then you'll need a domain user to access it.

hi, i had to change the local account in the end. it now all works. thanks guys