I know there are plenty of vital commands and Scripts are available for SQL DBAs,
I am mentioning few Important commands which every DBA must know. Also DBAs should know when it should use in production environment-
--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 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 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 consistancy check of database :
Dbcc CheckDB('dbname')
--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 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='<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 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 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
