Thursday, March 5, 2015

DB Health Check.

DB health check includes

Disk space check: exec xp_fixeddrives : it gives informations of free space left on all drives

Agent jobs check: sp_help_job, sysjobhistory : it gives info about origination server name, job name, enabled status, description of job, owner, date created, date modified, last run data and time, next run date and time etc.

Database size and free space left: sp_spaceused : dbneme, db size and unallocated or free space on disk.

Log space details: dbcc sqlperf(logspace) : dbname, log size in MB and space used in percentage.

Login failed and succeeded detais: EXEC sp_readerrorlog 0,1,'Login Succeeded or failed'

Database status: sys.databases : state_description

Deadlock: dbcc (1222,1) : write all deadlock details in error log file.

Blocking: activity monitor or sp_who2 or sys.sysprocesses gives informations about blocking.

Database uptime: script to find out it : since when database up.

Integrity check of db: DBCC Checkdb : checks integrity of db

Statistics report : DBCC show_statistics : gives the info about when last updated.

Fragmentaiton detail: DBCC showcontig : fragmentation detials.

No comments: