HSN: December 2009

Thursday, December 10, 2009

Use Forfiles utility (forfiles.exe) to delete old database backup files:
```
forfiles /p "e:\sqlbackup" /m "*.bak" /c "cmd /c del /Q @path" /d -2
```
Use ALTER USER WITH LOGIN to repair orphaned logins.
Use sp_addsrvrolemember to give yourself the sysadmin role.
Use the PortQryUI utility to troubleshoot connectivity problems.
Use a different strategy when running DBCC CHECKDB against large databases.
Use the Import Package Option to deploy SQL Server Integration Services (SSIS) packages to the msdb database.
Use SQL Server 2008's local server groups and Central Management Servers to query multiple servers at the same time.

Daily maintenance jobs:

Daily monitoring jobs:

Monitor database connections.
Monitor physical storage usage.
Monitor memory usage: cache hits ratio, page reads, writes/sec etc.
Monitor CPU usage.
Monitor physical disk I/O, network segment.
Monitor error log on critical events: severity level > 16.
Monitor user activities: connections, dead lock detection, user security breach, SQL compilation/sec etc.

Daily procedures

Verify all instances are up: run daily reports or via "Oracle Enterprise Manager's 'probe' event"
Look for any new alert log entries: Check ORA-errors in $ORACLE_BASE//bdump/alert_.log
Verify success of database backup
Verify success of database archiving to tape
Verify enough resource for acceptable performance:
a. Verify free space in tablespaces (free.sql, space.sql)
b. Verify rollback segment: should be ONLINE
c. Identify bad growth projections:
I. analyze5pct.sql
II. nr_extents.sql
III. Query current table sizing
IV. Query current index sizing
V. Query growth trends
d. Identify space-bound objects (spacebound.sql)
e. Review contention for CPU, RAM, network and disk resources.
Copy archived logs to standby database and roll forward: use automated scripts, verify daily

Nightly procedures

Weekly procedures

Look for objects that break rules
a. Check NEXT_EXTENT: nextext.sql, check existing extents: existext.sql
b. Check missing PK: no_pk.sql, check disabled PK: disPK.sql, check PK indexes uniqueness: nonuPK.sql
c. All indexes should use INDEXES tablespace: mkrebuild_idx.sql
d. Check schema objects consistency between test and production: datatype.sql, obj_coord.sql, Quest Schema Manager.
Look for security policy violations
Look in SQL*Net logs for errors, issues
a. Check client side logs
b. Check server side logs
Archive all alert logs to history

Montly procedures

根据美国次贷危机，中国A股崩盘，网络泡沫破裂，911危机及亚洲金融风暴经验，股市通常会下跌到1/5位置，甚至会到达1/10，在此调整操盘策略：