Saturday, 5 March 2016

Important SQL commands for SQL Server DBAs and when it should use in SQL Server.

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:

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

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

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

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

--To know  the DB status of particular Database:

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

No comments:

Post a Comment