Maintenance job failing: "Invalid object name 'Confluence.sys.Partitions'. Changed database contex

[Caesar S]

Hi there

My database is called "Confluence". The maintenance job has always failed since that job was setup 6 months ago. Attempting to run the job manually also fails with same error.

Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved.
Started: 1:00:00 AM Progress: 2020-05-12 01:00:05.53
Source: {AB5561C0-62BD-4185-A869-1AD993B98428}
Executing query "DECLARE @Guid UNIQUEIDENTIFIER
EXECUTE msdb..sp...".: 100% complete End Progress
Error: 2020-05-12 01:02:57.66 Code: 0xC002F210
Source: Execute T-SQL Statement Task Execute SQL Task
Description: Executing the query "use master; exec ap_RebuildIndexes 30, 80, 'Confl..." failed with the following error: "Invalid object name 'Confluence.sys.Partitions'. Changed database context to 'master'. ".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Warning: 2020-05-12 01:02:57.66 Code: 0x80019002 Source: Subplan_1 Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:00:00 AM Finished: 1:02:58 AM Elapsed: 177.607 seconds. The package execution failed. The step failed.

Are you able to assist please?

Thanks & regards

Caesar

[Nic Brough -Adaptavist-]

Why are you doing this?  You shouldn't be even looking at the database.

[Caesar S]

Begging your pardon, could you please explain?

[Nic Brough -Adaptavist-]

You are getting an error in something you have written to look at a Confluence database.

I am asking why you have written this and pointing out that you should not be looking at the database.

[Caesar S]

However, this is a maintenance plan built by using the in-built maint plan wizard in SQL Server. No part of this plan has been manually scripted.  

[Nic Brough -Adaptavist-]

Why are you running maintenance plans?

[Caesar S]

As we would like to rebuild the indices of the tables in this database. I thought that is a standard practice everywhere. This ensures that the application end-users do not experience slow responses etc.

What are the alternatives please?

[Nic Brough -Adaptavist-]

No.  You don't need to worry abou them for Atlassian applications.

[Caesar S]

And, why is that ?

[Nic Brough -Adaptavist-]

They don't use SQL for most of the user activity, it works off the local index, and the indices in the database aren't used much.  You won't see any day-to-day performance improvements by rebuilding indices. 

Plus, a well-behaved database would not need to rebuild indices.

Anyway, that said, there's no harm in rebuilding.  Problem with your question though is that it's nothing to do with Atlassian.  The problem you are having in the database rebuild is caused by the SQL wizard, not Confluence.

I'd take a look at SQL fora really, there's something wrong with what the wizard is doing, not Confluence, so we're not really the people who can help.

[Caesar S]

Thanks for clarifying.

The reason I posed the question here is to see if anyone had experienced this issue specifically with confluence.

I have been researching SQL fora as well and that has provided some other clues which will assist in narrowing down where the problem might lie.

Anyway, thanks again. 

[Caesar S]

The maintenance plan is to rebuild indexes and then update statistics for the single database called "Confluence".

The SQL Server instance is SQL 2012 (SP1).

Thanks v much for responding

Caesar

[Nic Brough -Adaptavist-]

What is this maintenance job and why are you running it?

[Caesar S]

see below