sql maintenance task fails

hi, I've created a backup in maintenance plans but on all of our servers, the cleanup part fails. it gives me an error:
"execution failed. See the maintenance plan and sql server agent job history logs for details. I think i may be missing a setting which is causing this. any help appreciated.
Thanks
LVL 1
winterhillschoolAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David SankovskySenior SysAdminCommented:
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)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
winterhillschoolAuthor Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Where did you get this information from?
0
David SankovskySenior SysAdminCommented:
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

Open in new window

0
winterhillschoolAuthor Commented:
this is from the log viewer for the job (view history)
0
winterhillschoolAuthor Commented:
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)
0
David SankovskySenior SysAdminCommented:
Neither Can I.
What exactly does the clean up do?
Can you attache a screen shot of the cleanup task settings?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, no errors. Did you confirm you are checking the logs for the correct job?
0
winterhillschoolAuthor Commented:
the clean up removes stale backups that are passed the expiry date. sql.jpg
0
winterhillschoolAuthor Commented:
the log file by using i the query that David posted, and copied all the events from today.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
winterhillschoolAuthor Commented:
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.
0
winterhillschoolAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
winterhillschoolAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
That's why I don't like to use the GUI to run commands. SSMS can only see local drives but Backup command allows you to write to a network share. Example:
BACKUP DATABASE dbname
TO DISK='\\Servername\ShareName\FileName.bak'


Check if you can change the destination by editing the job.

Btw, I don't even use SSMS's Maintenance Plan to perform maintenance tasks (to be honest almost no DBA use it). You can easily create your own Maintenance Plan by scripting the necessary commands or then use the worldwide known Ola Hallengren's scripts.
0
David SankovskySenior SysAdminCommented:
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.
0
winterhillschoolAuthor Commented:
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?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you want you can follow MSDN article on how to Create a Job.
0
winterhillschoolAuthor Commented:
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?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
INIT is an option of the BACKUP command. Here's an example how to use it.
BACKUP DATABASE dbname
TO DISK='\\Servername\ShareName\FileName.bak'
WITH INIT

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
winterhillschoolAuthor Commented:
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.
0
winterhillschoolAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can access but how about the SQL Server service account? Is the one that need access.
0
winterhillschoolAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Local SQL logins doesn't have access to operating system files. Can't you change the job owner to SQL Server Agent account?
0
winterhillschoolAuthor Commented:
hi It is already
Capture.PNG
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You have an active directory user named sa?
0
winterhillschoolAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just for testing purpose, can you change the job owner to sa (just sa, no domain)?
0
winterhillschoolAuthor Commented:
hi, still the same result
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
winterhillschoolAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
winterhillschoolAuthor Commented:
the account is local on the server. Is it possible to give a local account a permission on another server?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
No, local account no. Or you make the share available to everyone or then you'll need a domain user to access it.
0
winterhillschoolAuthor Commented:
hi, i had to change the local account in the end. it now all works. thanks guys
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.