–Get the db details
EXEC SP_HELPDB ‘Db_name’ –know the definition of the objects EXEC SP_HELPTEXT‘object_name’ –Identify the size of database and table EXEC SP_SPACEUSED –Know the details of the disk drives EXEC MASTER..XP_FIXEDDRIVES —Quick view of SQL server sessions EXEC SP_WHO2 –It’s advanced version of sp_who2 but not shipped with SQL server.Get it from the following link. EXEC SP_WHOISACTIVE –Used in Query tuning for getting the logical reads SET STATISTICS IO ON –Used in Query tuning for getting the CPU time SET STATISTICS TIME ON –Clean the data buffers.Used in query tuning. DBCC DROPCLEANBUFFERS –Free the procedure cache.Used in query tuning. DBCC FREEPROCCACHE –Check the Error logs in efficient way EXEC MASTER.DBO.XP_READERRORLOG0,1,‘search String 1’,‘search String 2’,‘2017-01-10’,‘2017-02-20’,N’desc’ –Check the query a session is running. DBCC INPUTBUFFER(<session_id>) –Get current log file path SELECT Serverproperty(‘ErrorLogFileName’) –Database consistency check DBCC CHECKDB WITH ESTIMATEONLY;
DBCC CHECKDB WITH NO_INFOMSGS;
DBCC CHECKDB(DBNAME) WITH NO_INFOMSGS, ALL_ERRORMSGSSELECT Databasepropertyex (‘kcc_dw’,‘STATUS’)