系統城裝機大師 - 唯一官網:www.farandoo.com!

當前位置:首頁 > 數據庫 > MsSql > 詳細頁面

YourSQLDba低版本的一個Bug的淺析

時間:2020-05-15來源:電腦系統城作者:電腦系統城

幫人分析解決一個YourSQLDba備份報錯問題,個人覺得有點意思,順手記錄一下分析思路,大體解決思路如下:
 
首先,找到YourSQLDba作業YourSQLDba_FullBackups_And_Maintenance的報錯郵件或者作業的錯誤日志信息,檢查YourSQLDba出錯的詳細信息。
 
YOURSQLDBA.MAINT.ShowHistoryErrors 96
 
發現YourSQLDba在更新統計信息是遇到錯誤,如下所示
 
<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>
 
 
檢查發現這個對象是queue,根本不是表,所以更新統計信息會出錯。
 
clip_image001
 
那么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('<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>
 
這個腳本會將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 [<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>
分享到:

相關信息

系統教程欄目

欄目熱門教程

人氣教程排行

站長推薦

熱門系統下載

jlzzjlzz亚洲乱熟在线播放