Thursday, December 22, 2016

SQL Server - Checks

Hi,

try to start a list of most important things (not in order) to check as a DBA in operation:


  • Services Running
    (see: sys.dm_server_services)
  • FileSystem Space
    (see: sp_spaceused and sp_MSforeachtable / sys.master_files + sys.databases / msdb.sys.dm_io_virtual_file_stats)
  • Error Log (Agent and DB)
    (see: xp_readerrorlog)
  • Locks and Waits
    (see: sys.dm_exec_requests (wait_type), sys.dm_exec_connections, sys.dm_exec_sql_text, trace-flags 1204, 1222)
  • Backups
    (see: msdb.dbo.backupmediafamily, msdb..backupset)
  • expensive queries
    (see: dm_exec_query_stats, dm_exec_sql_text, dm_exec_query_plan)
  • index fragmentation
    (see: sys.dm_db_index_physical_stats)
  • statistics
    (see: dbcc show_statistics)
  • consistency
    (see: dbcc checkdb / progress: SYS.DM_EXEC_REQUESTS, SYS.DM_EXEC_SQL_TEXT)
  • overview of sessions (users)
    (see: sys.dm_exec_sessions, kill)
  • Shrink
    (see: dbcc shrinkfile / don't do that if not edge case)
  • User Handling
    (see: create user/login, sp_change_users_login)
  • Update Process
  • Logging Data-Changes
    (see: Change Data Capture (msdn), Trigger)
  • Firewall Security
  • Automation
    (see: SQL Server Agent, Powershell, SSIS, ETL)
Supporting Tools: Management Studio, Profiler, SSDT / BIDS, Configuration Manager, PowerShell, Nagios, IIS Crypto, sqlcmd, bcp, tsqlt 

kr,
Daniel

No comments: