YourSQLDba低版本的一个Bug的浅析

帮人分析解决一个YoursqlDba备份报错问题，个人觉得有点意思，顺手记录一下分析思路，大体解决思路如下：

首先，找到YourSQLDba作业YourSQLDba_FullBackups_And_Maintenance的报错邮件或者作业的错误日志信息，检查YourSQLDba出错的详细信息。

YOURSQLDBA.MAINT.ShowHistoryErrors 96

发现YourSQLDba在更新统计信息是遇到错误，如下所示

  yMaint.UpdateStats

  update statistics selected

  update statistics [model].[dbo].[ServiceBrokerQueue] WITH sample 100 PERCENT

  Error 2706, Severity 16, level 6 : Table "ServiceBrokerQueue" does not exist.

检查发现这个对象是queue，根本不是表，所以更新统计信息会出错。

那么YourSQLDba怎么会更新queue对象的统计信息呢？我首先检查了一下YourSQLDba的版本信息。当前数据库服务器上的YourSQLDba是相当老的一个版本了。然后我就去检查YourSQLDba的代码

Exec YourSQLDba.Install.PrintVersionInfo

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

YourSQLDba version: 5.0.2 2012-06-12

YourSQLDba更新统计信息是通过[yMaint].[UpdateStats]来更新统计，于是检查代码，发现获取要更新统计信息的对象是通过下面脚本获取的。首先将要更新统计信息的对象放到临时表#TableNames中去，然后生成更新统计信息的脚本。

-- 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("")) as scn --collate 

 , object_name(Ps.object_id, db_id("")) as tb --collate 

 , Sum(Ps.Page_count) as Pg

From

  sys.dm_db_index_physical_stats (db_id(""), 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)) % ) = 

"  

这个脚本会将queue类型的对象也放入临时表，所以明显是个Bug，不过YourSQLDba后续的版本已经Fix掉这个Bug了。如下所示，后续的版本就加上条件过滤了，只获取表和视图的数据。所以遇到这个问题，只需要升级YourSQLDba的版本就好了

-- 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 []

 set nocount on

 ;With

   TableSizeStats as

 (

 select 

   object_schema_name(Ps.object_id) as scn --collate 

 , object_name(Ps.object_id) as tb --collate 

 , Sum(Ps.Page_count) as Pg

From

  sys.dm_db_index_physical_stats (db_id(""), 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)) % ) = 

"