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
