Analysis of a Bug in your sqldba's lower version

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
 ;With
   TableSizeStats as
 (
 select 
   object_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 Pg
From
  sys.dm_db_index_physical_stats (db_id('<DbName>'), NULL, NULL, NULL, 'LIMITED') Ps
Group by 
  Ps.object_id  
)
Insert into #tableNames (scn, tb, seq, sampling)
Select 
  scn
, tb
, row_number() over (order by scn, tb) as seq
, Case 
    When pg > 200001 Then '10'
    When Pg between 50001 and 200000 Then '20'
    When Pg between 5001 and 50000 Then '30'
    else '100'
  End  
From 
  TableSizeStats
where (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 statistics
truncate table #TableNames
set @sql =
'
 Use [<DbName>]
 set nocount on
 ;With
   TableSizeStats as
 (
 select 
   object_schema_name(Ps.object_id) as scn --collate <srvCol>
 , object_name(Ps.object_id) as tb --collate <srvCol>
 , Sum(Ps.Page_count) as Pg
From
  sys.dm_db_index_physical_stats (db_id("<DbName>"), NULL, NULL, NULL, "LIMITED") Ps
Where (   OBJECTPROPERTYEX ( Ps.object_id , "IsTable" ) = 1
       Or OBJECTPROPERTYEX ( Ps.object_id , "IsView" ) = 1)
Group by 
  Ps.object_id  
)
Insert into #tableNames (scn, tb, seq, sampling)
Select 
  scn
, tb
, row_number() over (order by scn, tb) as seq
, Case 
    When 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"
  End  
From 
  TableSizeStats
where scn is not null and tb is not null and (abs(checksum(tb)) % <SpreadUpdStatRun>) = <seqStatNow>

Tags: SQL Server SQL Database

Posted on Fri, 08 May 2020 06:17:47 -0400 by brianlange