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

SQL Server : Complete DBCC commands for Performance Tuning in SQL Server

SQL Server : Complete DBCC commands for Performance Tuning in SQL Server.

Using below queries we can tune the DB.

/*Returns size and fragmentation information for the data and indexes of the specified table or view*/

SELECT * FROM sys.dm_db_index_physical_stats

(DB_ID(N'HRMS_DB'), OBJECT_ID(N'Sparsed'), NULL, NULL , 'DETAILED');

/*This command is used to scan the current DB

and display the pages and extens and fragemention*/

DBCC SHOWCONTIG

/*This command is used to scan a particular table

and display pages,extens,avg size,row size,

and scanned pages of a table*/

DBCC SHOWCONTIG(tbl_Emp)

SELECT * FROM sys.dm_db_index_physical_stats('HRMS_DB','tbl_Emp' , NULL, NULL , 'LIMITED');

/* To display total pagesize,datapage size,

used pages on a database*/

select * from sys.allocation_units

/*To display the rows spread accross

the pages in a table*/

DBCC CHECKTABLE ('tbl_Emp')

/*To check the overall Database and tables

with rows spread across the pages*/

DBCC CHECKDB (HRMS_DB)

/*DBCC CLEANTABLE reclaims space after a

variable-length column is dropped.

A variable-length column can be one of the following data types:

varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml.

The command does not reclaim space after a fixed-length column is dropped */

/*For the below example let's create a table

and insert some data and then drop the

column and then use the command and see the result*/

/*Step 1: Create table */

drop table dbo.CleanTableTest

CREATE TABLE dbo.CleanTableTest

    (FileName nvarchar(4000),

    DocumentSummary nvarchar(max),

    Document varbinary(max)

    );

/*Step 2: Insert the data into the table

using wizard or from the table which contains

large records */

/*Now Verify the current page counts

 and average space used */

 SELECT * FROM sys.dm_db_index_physical_stats

(DB_ID(N'HRMS_DB'), OBJECT_ID(N'dbo.CleanTableTest'), NULL, NULL , 'DETAILED');

/*Now find out from the output few columns like

avg_pagesize,record count,pagecount,max record byte size,

avg_record byte size*/

/*Step 3: drop the columns of the table*/

ALTER TABLE dbo.CleanTableTest

DROP COLUMN FileName, Document;

/*Now Check the size of the table*/

SELECT * FROM sys.dm_db_index_physical_stats

(DB_ID(N'HRMS_DB'), OBJECT_ID(N'dbo.CleanTableTest'), NULL, NULL , 'DETAILED');

/*Now Clean the table*/

/*Even though you drop the column the space

is still reserved so now take a screen shot

and test the min,max,avg,row sized */

/*(After DBCC CLEANTABLE now check the

space used the min,max,row sizes will be different*/

DBCC CLEANTABLE (HRMS_DB,'dbo.CleanTableTest');

/*Now check the space of the table*/

SELECT * FROM sys.dm_db_index_physical_stats

(DB_ID(N'HRMS_DB'), OBJECT_ID(N'dbo.CleanTableTest'), NULL, NULL , 'DETAILED');

DBCC CheckDB(HRMS_DB)

/*This Command is used Removes residual

information left on database pages

because of data modification routines

in SQL Server*/

/*Before this command check the DBSpace using

sp_helpdb HRMS_DB */

EXEC sp_clean_db_free_space

@dbname = N'HRMS_DB' ;

/*To get the complete infor of all the

data type*/

EXEC sp_datatype_info

/*This Command display's all the objects

that references the table tbl_Emp

like views,sp,indexes releated to this table*/

EXEC sp_depends @objname = N'tbl_Emp';

/*To Display the Memory Status*/

DBCC MemoryStatus

Note: Please do not run the below query on production server if it's not required. To run above queries we can tune the our database easily.