Monday, November 17, 2014

SQL Server : Important Commands for SQL Server DBA

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

Like and Share to SQL Integrity Blog


Thank You !!!!