---
# System prepended metadata

title: —資料庫檢查—

---

# [DBA] ---資料庫檢查---


## 檢查帳戶權限 

:::success
參考語法
select 
	'CREATE LOGIN ['+name+'] WITH PASSWORD = ' + convert(varchar(256),password_hash,1  ) + ' HASHED , SID = ' + convert(varchar(256),sid,1  )
from sys.sql_logins　
where type = 'S' AND is_disabled = 0 
:::

透過上述語法，於舊DB複製出使用者帳號，並至新DB創建，
<BR>創建完畢後，參考就DB設定使用者權限

## 檢查資料筆數是否同步

:::success
 SELECT
     OBJECT_NAME(p.object_id) as TableName
     ,p.partition_number as PartitionNumber
     ,prv_left.value as LowerBoundary
     ,prv_right.value as  UpperBoundary
     ,ps.name as PartitionScheme
     ,pf.name as PartitionFunction
     ,fg.name as FileGroupName
     ,CAST(p.used_page_count * 8.0 / 1024 AS NUMERIC(18,2)) AS UsedPages_MB
     ,p.row_count as Rows
 FROM  sys.dm_db_partition_stats p
 INNER JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
 INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
 INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
 INNER JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
 INNER JOIN sys.filegroups fg ON fg.data_space_id = dds.data_space_id
 LEFT JOIN sys.partition_range_values prv_right ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number
 LEFT JOIN sys.partition_range_values prv_left  ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1
 WHERE
 p.object_id = OBJECT_ID('T_GameResult') and p.index_id < 2
:::

由於T_GameResult資料量過大，查詢count(*) 仍需要大量時間
<br>且T_GameResult建有Partition，因此透過上述語法，直接比較Partition內的筆數
<BR>其餘資料表則透過比較count(*) 來完成


## 新增SP
新增 sp_Platform_SyncActivityResult

## 建置新機器的備份排程&維護計畫
 參考舊DB手動建置，
<BR>原計畫應該是透過( Integration Services )去備份(匯出封裝)，
<BR>但實際去連Integration Services時失敗了，故改為手動建

## 調整伺服器記憶體選項
最大伺服器記憶體 未設定，扔保持預設值，已調整成就DB相同

## 調整tempDB
1.當初安裝mssql時，未調整tempDB的儲存位置，以至於路徑指向的是預設路徑，
<BR>經修正後，tempDB的儲存路徑已導向正確的磁區
2.預設的tempDB資料庫檔案只有4個，而舊DB為16個，故將新DB也調整為16個

