系統維護工作日誌–SQL

增設自動化系統維護設定

2010 3月31日

1. Data Mail

2. Add 操作員

3. Add Critical Error Alert

4. Add User Connection too High 100 Alert (For watch if SQL is in High Load)

5. Add Login Fault Alert (For watch if SQL is being hacked)

6. Use Penguin Backup Task complete to test Alert setting / functions→ It is Ok, alert condition is changed to "If task is failure"

7. Add AllDatabaseFullBK(All Database would be full backup every monday 13:30 to .\BackupFiles\FullBKCluster\) task

8. 執行備份時發現penguin有一致性錯誤，進行修復(平台期間需停用，費時1分內)，以下為執行碼

EXEC sp_dboption 'Penguin', 'single user', 'TRUE'

use Penguin

dbcc checkdb ('Penguin',repair_allow_data_loss)-------修复数据库

dbcc checkdb ('Penguin',REPAIR_REBUILD)----------------修复数据库索引

dbcc checkdb

EXEC sp_dboption 'Penguin', 'single user','FALSE'

9. Add PrimaryDbDiffBK(Primary Database Diffence Backup every day at 14:00 to .\BackupFiles\DiffBKCluster) task

10. Add PrimaryDbLogBK(Primary Database Log Backup every 30 min to .\BackupFiles\LogBKCluster) task

11. Add delete old backup file tasks : DeleteOldBkPlan(over 6 weeks full and diff) DeleteLodBkPlan(over 2 days log)

12. reset clearlog task (remove log files over 12 weeks)

13. remove old penguin backup plans

14. 日誌檔(LDF)無法隨自動交易備份作業完成後自動縮減(標準程序：備份交易檔(with nolog = "截斷交易紀錄")，再壓縮資料庫)，尚待研究