# [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個