Data World

Archive for the ‘DBA’ Category

SQL Server -DBA Must Know commands

Posted by Pramod Singla on March 28, 2017


Following are couple of DBA must know commands.

–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_READERRORLOG 0, 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_ERRORMSGS SELECT Databasepropertyex (‘kcc_dw’, ‘STATUS’)         

DBCC sqlperf(‘logspace’) 

Advertisements

Posted in DBA | Tagged: , | Leave a Comment »