SQL Server : Important Commands for SQL Server DBA

--TO CHECK FOR BLOCKED PROCESSES:

SP_WHO2   

--TO FIND ALL THE LOG FILES AND THE PERCENTAGE SPACE USED OF THOSE LOG FILES IN AN INSTANCE:

DBCC SQLPERF (LOGSPACE)  

--TO LIST ALL THE FILES AND THEIR DETAILS IN A PARTICULAR DATABASE:

EXEC SP_HELPFILE

--TO LIST ALL THE DATABASES AND THEIR DETAILS IN A INSTANCE:

EXEC SP_HELPDB

--TO SHRINK A DATABASE FILE WITHOUT SPECIFYING TARGET SIZE(PREFERRED FOR LOG FILES):

DBCC SHRINKFILE(FILEID)

--TO SHRINK A DATABASE FILE WITH SPECIFYING TARGET SIZE(PREFERRED FOR DATAFILES):

DBCC SHRINKFILE(FILEID,TARGET_SIZE)

--TO FIND THE DETAILS ABOUT LOCKS CURRENTLY HELD BY THE PROCESSES:

EXEC SP_LOCK

--TO FIND ALL THE DRIVE SPACES THROUGH MS SQL:

EXEC MASTER..XP_FIXEDDRIVES

--TO PUT THE DATABASE IN SINGLE USER MODE AT COMMAND PROMPT:

SQLSERVR.EXE –M

--TO PUT THE DATABASE IN MINIMAL MODE AT COMMAND PROMPT:

SQLSERVR.EXE –F         -->  -C  FOR CONSOLE APPLICATIONS

--TO FIND OUT THE DATABASE ID?

 SELECT DB_ID ('DB_NAME')

--TO FIND OUT LOGINS:

SELECT * FROM SYS.SYSLOGINS

--TO FIND OUT CURRENT USER:

SELECT CURRENT_USER

--SHORT CUT FOR MS SQL SERVER MANAGEMENT STUDIO:

SQLWB.EXE

--TO CHECK THE SERVICE PACK AT PRODUCT LEVEL:

SELECT SERVERPROPERTY ('MACHINENAME')

SELECT  SERVERPROPERTY('PRODUCTVERSION'), SERVERPROPERTY ('PRODUCTLEVEL'),SERVERPROPERTY ('EDITION')

--TO CHECK THE OPEN TRANSACTIONS :

DBCC OPENTRAN('DBNAME')

--TO FIND MS SQL SERVER PRODUCT LEVEL DETAILS:

EXEC MASTER..XP_MSVER

--TO FIND THE DETAILS OF CURRENT PROCESSES RUNNING LIKE PERCENT COMPLETED:

SELECT * FROM SYS.DM_EXEC_REQUESTS

--TO FIND THE LONGEST RUNNING QUERIES

SELECT * FROM SYS.DM_EXEC_QUERY_STATS

--COMMAND TO FIND THE FREE SPACE AND USED SPACE :

SELECT * FROM DBO.SYSFILES

--TO CHECK LOAD IN SERVER AND DATABASE AND FILES:

SELECT * FROM SYS.DM_IO_VIRTUAL_FILE_STATS(DATABASE ID,FILE ID)

--TO FIND THE LONGEST RUNNING QUERIES

SELECT * FROM SYS.DM_EXEC_SQL_TEXT

--TO FIND ORPHAN LOGIN:

SP_VALIDATELOGINS

--TO FIND ORPHANED USERS

EXEC SP_CHANGE_USERS_LOGIN 'REPORT'

--TO MAP USERS TO LOGINS

EXEC SP_CHANGE_USERS_LOGIN @ACTION='UPDATE_ONE', @USERNAMEPATTERN='',@LOGINNAME=''

--IF YOU ALREADY HAVE A LOGIN ID AND PASSWORD FOR THIS USER, FIX IT BY DOING:

EXEC SP_CHANGE_USERS_LOGIN 'AUTO_FIX', 'USER'

--TO DELETE LOGIN:

EXEC SP_REVOKELOGIN 'USERX'

--TO LIST ALL THE OBJECTS OWNED BY USER ID:

SELECT NAME FROM SYSOBJECTS WHERE UID=USER_ID('USER')

--TO ENABLE ‘SHOW ADVANCED OPTIONS’ FOR SERVER CONFIGURATION OPTIONS:

EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 1

--TO ENABLE PARTICLAR SERVER CONFIGURATION OPTION:

EXEC SP_CONFIGURE 'OPTION NAME', 1

--TO FIND PERFORMNCE RELATED STASTICTICS:

SELECT * FROM SYS.DM_OS_PERFORMANCE_COUNTERS

--TO FIND THE INFORMATION ABOUT INDEXES ON TABLES AND VIEWS.

SELECT * FROM SYS.DM_DB_INDEX_USAGE_STATS

--TO FIND OUT ALL OF THE USERS WHO ARE CONNECTED TO THE DATABASE SERVER:

SELECT * FROM SYS.DM_EXEC_SESSIONS

--TO DISPLAY THE ERROR LOG USING THE QUERY:

XP_READERRORLOG

--TO DISPLAY ERROR LOG ARCHIVE NUMBERS AND THEIR DATES:

EXEC SP_ENUMERRORLOGS

--TO VIEW NO. OF TRACES RUNNING.

SELECT COUNT(*) FROM :: FN_TRACE_GETINFO(DEFAULT) WHERE PROPERTY = 5 AND VALUE = 1

--TO FIND DETAILS ABOUT THE TRACES WHICH ARE RUNNING.

SELECT * FROM :: FN_TRACE_GETINFO(DEFAULT)

--TO TERMINATE A TRACE

EXEC SP_TRACE_SETSTATUS 1, @STATUS = 0 / @STATUS=2

--TO KNOW THE SPACE USED BYE DATA,INDEX IN PARTICULAR DATABASE:

SP_SPACEUSED

--TO KNOW  THE DB STATUS OF PARTICULAR DATABASE:

SELECT DATABASEPROPERTYEX('ADVENTUREWORKS', 'STATUS')

--TO VIEW JOBS WHICH FAILED AT LAST RUN:

 SYSJOBACTIVITY, SYSJOBSCHEDULES, MSDB.DBO.SYSJOBSERVERS, MSDB.DBO.SYSJOBS

--TO CHECK PAGES OF TABLE :

DBCC IND('DBNAME','TABLENAME',-1)

--TO CHECK PAGES CONTENTS

DBCC TRACE ON(3604)  DBCC PAGE('DBNAME',FID,PID,1)              -- F:FILE  P:PAGE

--TO SET MAXIMUM CONNECTIONS.

EXEC SP_CONFIGURE

--TO FIND NO.OF CONNECTIONS.

SELECT COUNT(DBID) AS TOTALCONNECTIONS FROM SYS.SYSPROCESSES WHERE DBID > 0

--IF LOG FILE IS FULL,TO FIND OUT REASON.

SELECT NAME,LOG_REUSE_WAIT_DESC  FROM SYS.DATABASES