SQL Server maintenance plan fails with a very vague error message

Question

SQL Server maintenance plan fails with a very vague error message

Yossu 21

I had been running SQL Server 2019 Developer Edition on my Win10 PC for a couple of years, and had a maintenance plan that ran every night to shrink and then backup all user databases to an external drive, and then delete backup files older than 1 week.

Last week I rebuilt my PC, and installed Win11. I set up a maintenance plan following what I had done previously. In case it helps, here is the designer that shows the main details...

I can supply more details if needed, but the designer shows pretty much everything of use.

I set this to run every night, but none of the databases are backed up.

The event viewer shows an Error entry for SQLISPackage150 with the message "Package "Backup" failed." Doesn't give me any more information.

If I try running the job manually, I get an error message...

Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.

===================================

Job 'Backup.Subplan_1' failed. (SqlManagerUI)

Program Location:

at Microsoft.SqlServer.Management.SqlManagerUI.MaintenancePlanMenu_Run.PerformActions()

If I look at the message for the actual job, all it says is "_Job 'Backup.Subplan_1' failed. (SqlManagerUI)_", which doesn't help at all.

As an experiment, I copied the SQL for one of the backups...

BACKUP DATABASE [Northwind]  
TO DISK = N'E:\SQL Server backups\Northwind_backup_2022_12_12_161327_7041409.bak'  
WITH NOFORMAT, NOINIT,  
NAME = N'Northwind_backup_2022_12_12_161327_7041409', SKIP, REWIND, NOUNLOAD,  STATS = 10

...into a new Query Analyser window, and it ran fine.

At this point I'm a bit lost. I have no idea why the job is failing, as it's not giving me any information.

Anyone able to help? Please let me know if there is any more information I can give.

Robbie Varn 351 Reputation points

2022-12-12T18:45:54.173+00:00

Is there any information in the Maintenance Plan history, which is located here: Instance>Management>Maintenance Plans>Rt click on the plan>View History?
Yossu 21 Reputation points

2022-12-15T20:34:56.13+00:00

@Robbie Varn Sorry, didn't get a notification of your comment. Please see my reply to SeeyaXi-msft which I think answers your question as well.

Thanks

6 answers

Your answer

Robbie Varn 351 Reputation points

2022-12-12T18:45:54.173+00:00

Is there any information in the Maintenance Plan history, which is located here: Instance>Management>Maintenance Plans>Rt click on the plan>View History?
Yossu 21 Reputation points

2022-12-15T20:34:56.13+00:00

@Robbie Varn Sorry, didn't get a notification of your comment. Please see my reply to SeeyaXi-msft which I think answers your question as well.

Thanks

Answer 1

Yossu 21

Posting this as a top-level post rather than a reply to someone else so it's more visible.

I just tried creating a new backup maintenance plan, using exactly the same steps and parameters as before, and it worked! I right-clicked and choose Execute, and all my user databases were backed up as expected.

I've scheduled it to run every night as before, so will have to wait until tomorrow to be sure it's worked, but the previous attempt never run even on demand, so I'm hopeful this will be the answer.

When in doubt, delete it and try again!

Thanks again to all those who gave advice.

Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-12-19T23:18:40.327+00:00

Good to know that you get your code working. You still don't what happened for your old setup. I would move on without worrying about it.

Answer 2

Yossu 21

Hmm, just looked in the server configuration manager, and can see that the SQL Server Agent service is running under SQLSERVERAGENT (the default). However, the backup folder does not have that user in its permissions list. I tried adding it, but the user doesn't exist.

I don't know if this is the issue, as I set the permissions for Everyone to "Full control" as a test, and the job still failed, but thought I'd mention it in case it is relevant.

Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-12-13T16:40:38.647+00:00

It is good to know why this happened to the maintenance plan. We have been there and tried before and you may run into potential issues with GUI solution and may not have a quick fix at all.
The permission issue you are running into now, I forget about how to fix it locally but in my working station, I use an service AD account with proper permission assigned to access network resources without any hassles any more.
Yossu 21 Reputation points

2022-12-14T17:16:50.177+00:00

Thanks for the reply, but I didn't understand what you meant.

I don't know if this is a permissions issue at all. I did point out that the backup folders has full control set for the Everyone user, so I wouldn't expect any permissions issues.

Also, I just tried changing the account for the Agent service to be my Windows account 9which has admin rights and full control over that folder) and it didn't make any difference, so I'm really not convinced that it's anything to do with permissions.

Which leaves me back where I started. A basic maintenance task fails with an absolutely hopeless error message, and seemingly no way to find out what's going wrong.

Surely there must be some way to fix this without having to resort to 3rd party scripts (however good they may be).

Any comments? Thanks again.
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-12-14T17:34:12.893+00:00

It does not worth the time to investigate the issue for the GUI. There have been some known issues so people learned other way around.
Good to have incentive to dig out your issues and I am sure you will learn a lot along the way. But it may take time to reproduce your issue with maintenance plan you are using. Until then, other knowledge people in this area will offer some help. I don't have time to set up a environment to reproduce your issue for now.
Hope someone else can help you further.
Dirk Hondong 876 Reputation points

2022-12-19T12:51:50.157+00:00

Little keep in mind for the backup database command and writing to a specific folder:
in the end, it is the actual account running the SQL server itself which needs write permissions on the backup folder. The agent account just runs the backup job telling SQL server: do a backup.

BR,
Dirk
Yossu 21 Reputation points

2022-12-19T15:45:26.543+00:00

@Dirk Hondong Thanks for the reply, didn't think of it that way.

However it doesn't help me, as the MSSQLSERVER account (which is what SQL Server runs under) has full control over that folder. Also, as I mentioned earlier, I gave Everyone full control as an experiment, so I can't see how it can be a permissions issue.

Also, I tried changing the destination folder to C:\Users\Public\Downloads, which is accessible by all users, and got the same unhelpful error message.

Thanks again. Any other ideas?
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-12-19T17:10:01.763+00:00

You need to make sure the user who is running the agent job has the permission.
You can launch SQL Configuration manager to configure SQL Server Agent under SQL Server Service Tab:
SQL Server Agent Properties: choose your account (Bessie\AY Silver) with proper password.
Restart SQL agent within the SQL Configuration manager and rerun your jobs to find out whether the jobs work.
Permissions on the folder security with MSSQLSERVER / everyone are not the right ones in this context. Everyone is not the real meaning of everyone here.
Yossu 21 Reputation points

2022-12-19T18:02:19.88+00:00

@Jingyang Li Thanks for the reply. I just checked, and SQL Server Agent is already running as me...

I've restarted the PC a few times since I changed the account, but tried restarting again just now and got the same error, so that's not the issue.

Thanks again, any other ideas?
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-12-19T20:35:33.023+00:00

Is your job is running under this user? Is this user has the proper permissions for the folder?
I have worked with this type of issue quite a few times. Just takes time to single out any situations until problem solved. Use a GUI solution is quick but in a long run, it may not save your time in cases like this. That is why you use your own code as a solution. It will take a little longer to build but you will be on top of it all the time.
Yossu 21 Reputation points

2022-12-19T20:44:30.453+00:00

Is your job is running under this user?

Not exactly sure what you mean. As you can see from my previous screenshot, the SQL Server Agent is already running as me. Was that what you meant? If not, please clarify.

Is this user has the proper permissions for the folder?

My user has full control over the folder. As I mentioned above, I tried having the job back up to C:\Users\Public\Downloads, which should be available to everyone, and it gave the same error.

This would all be so much easier if it would tell me what the error is. I find it hard to believe that such a sophisticated program as this is incapable of a helpful error message!

As for your last comment, I've been using SQL Server for about 20 years, and always set up my local backups this way. It's the first time it's ever given my any issues. I can use 3rd party scripts, but I would still like to get this working if I can.

Thanks again
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-12-19T21:16:34.593+00:00

I can generate some meaningful error message with my machine. Here is the detail error from the job history sub error message:
Message
Executed as user: mydomain\usr_myad Microsoft (R) SQL Server Execute Package Utility V
ersion 15.0.4249.2 for 64-bit Copyright (C) 2019 Microsoft. All rights reserved.
Started: 2:59:50 PM Progress: 2022-12-19 14:59:51.68 Source: {124B9613-9160-4E88-B134-371D3DAB0553}
Executing query "DECLARE @George Wang UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete
End Progress Progress: 2022-12-19 14:59:51.95 Source: Shrink Database
Executing query "USE [myprocessing] ".: 50% complete End Progress Progress: 2022-12-19 14:59:54.58
Source: Shrink Database Executing query "DBCC SHRINKDATABASE(N'myprocessing', 10, TRUNC...".: 100% complete End Progress Progress: 2022-12-19 14:59:54.59
Source: Shrink Database Executing query "USE [test] ".: 50% complete End Progress Progress: 2022-12-19 14:59:54.77
Source: Shrink Database Executing query "DBCC SHRINKDATABASE(N'test', 10, TRUNCATEON...".: 100% complete
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-12-19T21:17:04.737+00:00

--Continue (part 2):
Source: Shrink Database Executing query "DBCC SHRINKDATABASE(N'test', 10, TRUNCATEON...".: 100% complete
End Progress Progress: 2022-12-19 15:01:24.55 Source: Back Up Database (Full)
Executing query "BACKUP DATABASE [myprocessing] TO DISK = N'C:...".: 100% complete
End Progress Progress: 2022-12-19 15:01:25.36 Source: Back Up Database (Full)
Executing query "BACKUP DATABASE [test] TO DISK = N'C:\Prog...".: 100% complete
End Progress Error: 2022-12-19 15:01:25.49 Code: 0xC002F210 Source: Maintenance Cleanup Task Execute SQL Task
Description: Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2022..." failed with the
following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:59:50 PM
Finished: 3:01:25 PM Elapsed: 95.172 seconds. The package execution failed. The step failed.
Yossu 21 Reputation points

2022-12-19T21:22:11.387+00:00

Dunno, all I got was that very terse and completely useless message I showed at the beginning.

Anyway, as you can see from my post just earlier, I sorted the problem by deleting the plan and recreating it. Seems to work fine now!

Thanks again.

Answer 3

Yossu 21

Thanks to both of you for the suggestion, I'll take a look at that.

However, I'd still like to know what's going wrong with my maintenance task. It bothers me that something so basic can just fail, and not give any useful error message. Surely there must be some way of finding out what went wrong.

Answer 4

Seeya Xi-MSFT 16,676

Hi @Yossu ,

Welcome to Microsoft Q&A!
Using Ola Hallrngren' script's is a good method that many professionals use. If you don't want to use it, then check out the related logs for more information.

Best regards,
Seeya

Seeya Xi-MSFT 16,676 Reputation points

2022-12-14T06:34:56.747+00:00

Hi @Yossu ,

By this method you do not see the obvious error?

Best regards,
Seeya
Yossu 21 Reputation points

2022-12-15T20:32:46.503+00:00

Thanks for the suggestion, but that didn't give any more help.

I have the job scheduled to run at 00:30:00 every day. For some reason, there isn't anything in the log for that time, but there is an entry for 02:00:02 that shows that the job failed...

The text for the top-level failed message is...

Date 15/12/2022 02:00:02
Log Job History (Backup.Subplan_1)
Duration 00:00:02
Step ID
Server BESSIE
Job Name Backup.Subplan_1
Step Name
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by Schedule 9 (Backup). The last step to run was step 1 (Subplan_1).

I'll have to post the message from the sub-step separately, as it would exceed the character limit here.
Yossu 21 Reputation points

2022-12-15T20:33:14.877+00:00

The message for the sub-step is...

Date 15/12/2022 02:00:02
Log Job History (Backup.Subplan_1)
Duration 00:00:02
Step ID 1
Server BESSIE
Job Name Backup.Subplan_1
Step Name Subplan_1
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: Bessie\AY Silver. Microsoft (R) SQL Server Execute Package Utility Version 15.0.2000.5 for 64-bit Copyright (C) 2019 Microsoft. All rights reserved. Started: 02:00:02 Progress: 2022-12-15 02:00:02.83 Source: {19C11A8E-BF00-4FB1-B58A-FC1794019FC3} Executing query "DECLARE @George Wang UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 02:00:02 Finished: 02:00:03 Elapsed: 0.828 seconds. The package execution failed. The step failed.

Don't know if that gives you any clues, but I couldn't see anything there that indicated why the job had failed.

Thanks again.
Yossu 21 Reputation points

2022-12-15T20:38:11.727+00:00
Just noticed that the job is set to write errors to the event viewer.

There is an error from 00:20:03 whose "General" text is simply "Package "Backup" failed." The "Details" tab shows the following (XML view shown as you can see more)...

<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">

<System>
<Provider Name="SQLISPackage150" />
<EventID Qualifiers="16385">12291</EventID>
<Version>0</Version>
<Level>2</Level>
<Task>0</Task>
<Opcode>0</Opcode>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2022-12-15T02:00:03.0592600Z" />
<EventRecordID>23274</EventRecordID>
<Correlation />
<Execution ProcessID="33632" ThreadID="0" />
<Channel>Application</Channel>
<Computer>Bessie</Computer>
<Security UserID="S-1-5-21-1173397798-18497839-1137060361-1001" />
</System>

<EventData>
<Data>Backup</Data>
</EventData>
</Event>

<continued in next comment due to lack of space in this comment>...
Yossu 21 Reputation points

2022-12-15T20:39:32.027+00:00

There is a warning entry 1 second later whose "general" message is "SQL Server Scheduled Job 'Backup.Subplan_1' (0x8D6C7E82D54C144DB5B8179E285189E4) - Status: Failed - Invoked on: 2022-12-15 02:00:02 - Message: The job failed. The Job was invoked by Schedule 9 (Backup). The last step to run was step 1 (Subplan_1)." The "Details" tab shows the following (again, XML view)...

<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="SQLSERVERAGENT" />
<EventID Qualifiers="16384">208</EventID>
<Version>0</Version>
<Level>3</Level>
<Task>3</Task>
<Opcode>0</Opcode>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2022-12-15T02:00:04.8577471Z" />
<EventRecordID>23275</EventRecordID>
<Correlation />
<Execution ProcessID="13032" ThreadID="0" />
<Channel>Application</Channel>
<Computer>Bessie</Computer>
<Security />
</System>
<EventData>
<Data>Backup.Subplan_1</Data>
<Data>0x8D6C7E82D54C144DB5B8179E285189E4</Data>
<Data>Failed</Data>
<Data>2022-12-15 02:00:02</Data>
<Data>The job failed. The Job was invoked by Schedule 9 (Backup). The last step to run was step 1 (Subplan_1).</Data>
</EventData>
</Event>

Don't know if that helps. Thanks again
Seeya Xi-MSFT 16,676 Reputation points

2022-12-16T08:00:05.94+00:00

Hi @Yossu ,

It looks like there is a problem with the backup job step. Can you back up manually and see if it reports an error.

Best regards,
Seeya
Yossu 21 Reputation points

2022-12-18T21:38:10.577+00:00

Do you mean right-click the database, and choose tasks -> Backup?

If so, it works fine.

What next? Thanks
Seeya Xi-MSFT 16,676 Reputation points

2022-12-19T02:31:36.82+00:00

Hi @Yossu ,

If you don't have the latest CU installed, you can install the latest cumulative update. Finally, use SELECT @@version to check if it is installed.
https://www.microsoft.com/en-us/download/details.aspx?id=100809

Best regards,
Seeya

Answer 5

Jingyang Li 5,901 Volunteer Moderator

Instead of debugging your error message from the built-in maintenance plan, I would like to introduce your another solution from Ola Hallrngren for database maintenance work.
This is commonly used by many professional around the world.

ola.hallengren.com

Share via

SQL Server maintenance plan fails with a very vague error message

6 answers

Your answer