Data World

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: