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