Help people to analyze and solve a problem of your sqldba backup error reporting. I think it's a little interesting. I'll record the analysis ideas. The general solutions are as follows:
First, find the error message of YourSQLDba job yousqldba ﹣ fullbackups ﹣ and ﹣ maintenance or the error log information of the job, and check the error details of YourSQLDba.
YOURSQLDBA.MAINT.ShowHistoryErrors 96
Found that YourSQLDba encountered an error in updating statistics, as follows
<Exec><ctx>yMaint.UpdateStats</ctx><inf>update statistics selected</inf><cmd>update statistics [model].[dbo].[ServiceBrokerQueue] WITH sample 100 PERCENT</cmd><err>Error 2706, Severity 16, level 6 : Table 'ServiceBrokerQueue' does not exist.</err>
</Exec>
The check shows that the object is queue, not a table at all, so there will be an error in updating statistics.
So how does YourSQLDba update the statistics of the queue object? I first checked the version information of your sqldba. You sqldba on the current database server is quite an old version. Then I'll check your sqldba code
Exec YourSQLDba.Install.PrintVersionInfo
========================================YourSQLDba version: 5.0.2 2012-06-12
Your sqldba updates statistics through [ymaint]. [updatestates]. Then check the code and find that the object to get the statistics is obtained through the following script. First, put the object to update statistics into the temporary table (TableNames), and then generate the script to update statistics.
-- makes query boilerplate with replacable parameter identified by-- labels between '<' et '>'-- this query select table for which to perform update statistics
truncate table #TableNames
set @sql ='
set nocount on;WithTableSizeStats as(selectobject_schema_name(Ps.object_id, db_id('<DbName>')) as scn --collate <srvCol>
, object_name(Ps.object_id, db_id('<DbName>')) as tb --collate <srvCol>
, Sum(Ps.Page_count) as PgFromsys.dm_db_index_physical_stats (db_id('<DbName>'), NULL, NULL, NULL, 'LIMITED') PsGroup byPs.object_id)Insert into #tableNames (scn, tb, seq, sampling)Selectscn, tb, row_number() over (order by scn, tb) as seq, CaseWhen pg > 200001 Then '10'
When Pg between 50001 and 200000 Then '20'
When Pg between 5001 and 50000 Then '30'
else '100'
EndFromTableSizeStatswhere (abs(checksum(tb)) % <SpreadUpdStatRun>) = <seqStatNow>'
This script will put the objects of queue type into the temporary table, so it's obviously a Bug, but the Bug has been fixed in subsequent versions of YourSQLDba. As shown below, subsequent versions add conditional filtering and only get the data of tables and views. So if you encounter this problem, just upgrade your sqldba version
-- makes query boilerplate with replacable parameter identified by-- labels between "<" et ">"
-- this query select table for which to perform update statisticstruncate table #TableNamesset @sql ='
Use [<DbName>]set nocount on;WithTableSizeStats as(selectobject_schema_name(Ps.object_id) as scn --collate <srvCol>, object_name(Ps.object_id) as tb --collate <srvCol>, Sum(Ps.Page_count) as PgFromsys.dm_db_index_physical_stats (db_id("<DbName>"), NULL, NULL, NULL, "LIMITED") PsWhere ( OBJECTPROPERTYEX ( Ps.object_id , "IsTable" ) = 1Or OBJECTPROPERTYEX ( Ps.object_id , "IsView" ) = 1)Group byPs.object_id)Insert into #tableNames (scn, tb, seq, sampling)Selectscn, tb, row_number() over (order by scn, tb) as seq, CaseWhen Pg > 5000001 Then "0"When Pg between 1000001 and 5000000 Then "1"When Pg between 500001 and 1000000 Then "5"When pg between 200001 and 500000 Then "10"When Pg between 50001 and 200000 Then "20"When Pg between 5001 and 50000 Then "30"else "100"EndFromTableSizeStatswhere scn is not null and tb is not null and (abs(checksum(tb)) % <SpreadUpdStatRun>) = <seqStatNow>'