Basic SQL DBA Commands

 --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

 --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 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='<database_user>', @LoginName='<login_name>'

 --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 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 check pages of table :

Dbcc ind('dbname','tablename',-1)

 --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

 --Reports information about a specified database or all databases

sp_helpdb

 --Reports information about the indexes on a table or view

sp_helpindex

 --Returns statistics information about columns and indexes on the specified table

sp_helpstats

 --Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure

sp_helptext

 --Reports information about a database object

sp_help

 --Returns the physical names and attributes of files associated with the current database.

sp_helpfile

 --Returns a list of objects that can be queried in the current environment. This means any object that can appear in a FROM clause

sp_tables

 --Returns column information for the specified tables or views that can be queried in the current environment

sp_columns

 --Reports information about locks

sp_lock

 --Displays the number of rows, disk space reserved, and disk space used by a table

sp_spaceused

 --Displays or changes global configuration settings for the current server

sp_configure

 --Provides information about current users and processes

sp_who or sp_who2

 --Displays the current distribution statistics for the specified target on the specified table

DBCC SHOW_STATISTICS

 --Displays fragmentation information for the data and indexes of the specified table

DBCC SHOWCONTIG

 --Provides statistics about how the transaction-log space was used in all databases

DBCC SQLPERF

 --Displays the last statement sent from a client

DBCC INPUTBUFFER

--Commands For Database And Server Maintenance

--Rebuilds one or more indexes for a table in the specified database

DBCC DBREINDEX

 --Defragments indexes of the specified table or view

DBCC INDEXDEFRAG

 --Runs UPDATE STATISTICS against all user-defined and internal tables in the current database

sp_updatestats

 --Creates single-column statistics for all eligible columns for all user tables and internal tables in the current database. The new statistic has the same name as the column where it is created.

sp_createstats

 --Shrinks the size of the data files in the specified database

DBCC SHRINKDATABASE

 --Shrinks the size of the specified data file or log file for the related database

DBCC SHRINKFILE

 --Closes the current error log file and cycles the error log extension numbers just like a server restart. The new error log contains version and copyright information and a line indicating that the new log has been created

sp_cycle_errorlog or DBCC ERRORLOG

SOURCE : Google and bloggers