Author: Enrique

/* Informational Commands ​​ */

DBCC​​ SQLPERF(LOGSPACE)​​ --View transaction log size info

GO

DBCC​​ SHOW_STATISTICS('Sales.SalesOrderDetail',​​ 'IX_SalesOrderDetail_ProductID')​​ --View query stats for table or indexed view object

GO

DBCC​​ USEROPTIONS​​ --View current connections user set options

GO

/* Maintenance Commands ​​ */

DBCC​​ DBREINDEX('Person.Person')​​ --Rebuilds indexes on a table

GO

DBCC​​ CLEANTABLE(AdventureWorks2012,​​ 'Production.ProductDescription')​​ --Reclaims unused spaced from tables

GO

/* Validation Commands ​​ */

DBCC​​ CHECKTABLE('Person.Person')​​ --Integrity check of table pages/structure

GO

DBCC​​ CHECKFILEGROUP​​ --Integrity check of filegroup structure/allocation

GO

DBCC​​ CHECKDB(AdventureWorks2012)​​ --Integrity check of database objects

GO

/* Misc. Commands */

DBCC​​ HELP('CHECKDB')​​ --Syntax information for DBCC statements.. use '?' for list

GO

DBCC​​ TRACEON(610)​​ --Turn on trace flag 610 (minimally logged inserts into indexed tables)

GO

DBCC​​ TRACEOFF(610)​​ --Turn off trace flag 610

GO

DBCC​​ TRACESTATUS(610)​​ --Check trace flag status

GO

--Turn on advanced options

sp_configure​​ 'show advanced options',​​ 1

GO

RECONFIGURE

GO

--Configure min server memory

sp_configure​​ 'min server memory',​​ 0

GO

RECONFIGURE

GO

--Configure max server memory

sp_configure​​ 'max server memory',​​ 2048

GO

RECONFIGURE

GO

--Configure processor affinity

ALTER​​ SERVER​​ CONFIGURATION​​ SET​​ PROCESS​​ AFFINITY​​ CPU​​ =​​ 1,​​ 2​​ --AUTO

GO

--Configure fill factor

sp_configure​​ 'fill factor',​​ 75

GO

RECONFIGURE

GO

EXEC​​ sp_configure​​ 'Show Advanced Options',​​ 1;

GO

RECONFIGURE;

GO

EXEC​​ sp_configure;

DECLARE​​ @PortNumber​​ VARCHAR(10),​​ @KeyValue​​ VARCHAR(100)

IF​​ CHARINDEX('\',@@SERVERNAME,0)​​ <>0

BEGIN

SET​​ @KeyValue​​ =​​ 'SOFTWARE\MICROSOFT\Microsoft SQL Server\'+@@SERVICENAME+'\MSSQLServer\Supersocketnetlib\TCP'

END

ELSE

BEGIN

SET​​ @keyValue​​ =​​ 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer \Supersocketnetlib\TCP'

END

--How to read Registry in T-SQL Terms - ROOTKEY, KEY, VALUENAME,VALUE​​ 

EXEC​​ master..xp_regread​​ 'HKEY_LOCAL_MACHINE',@KeyValue,'Tcpport',@value=@PortNumber​​ OUTPUT

SELECT​​ 'SQLSERVER_NAME: '+@@SERVERNAME​​ +​​ ' : SQL SERVER Port Number:'+CONVERT(VARCHAR(10),

@PortNumber)

The script below will retrieve the number of rows, reserved size, data size, index size and unused size. The​​ reserved size​​ is the overall size of the table (it is the total of data size, index size and unused size).

--DROP TABLE #tmpTableSizes

CREATE​​ TABLE​​ #tmpTableSizes

(

 ​​ ​​ ​​​​ tableName​​ varchar(100),

 ​​ ​​ ​​​​ numberofRows​​ varchar(100),

 ​​ ​​ ​​​​ reservedSize​​ varchar(50),

 ​​ ​​ ​​​​ dataSize​​ varchar(50),

 ​​ ​​ ​​​​ indexSize​​ varchar(50),

 ​​ ​​ ​​​​ unusedSize​​ varchar(50)

)

insert​​ #tmpTableSizes

EXEC​​ sp_MSforeachtable​​ @command1="EXEC sp_spaceused '?'"

select ​​​​ *​​ from​​ #tmpTableSizes

order​​ by​​ cast(LEFT(dataSize,​​ LEN(dataSize)​​ -​​ 4)​​ as​​ int) ​​​​ desc

--Make Database Read Only

USE​​ [master]

GO

ALTER​​ DATABASE​​ [TESTDB]​​ SET​​ READ_ONLY​​ WITH​​ NO_WAIT

GO

--Make Database Read/Write

USE​​ [master]

GO

ALTER​​ DATABASE​​ [TESTDB]​​ SET​​ READ_WRITE​​ WITH​​ NO_WAIT

GO

--If you face error that if database is already in use, you can resolve the same by making database in single user mode

--1. Set the database to single mode:

ALTER​​ DATABASE​​ [TESTDB]​​ SET​​ SINGLE_USER​​ WITH​​ ROLLBACK​​ IMMEDIATE;

--2. Set the database to Multi-user mode

ALTER​​ DATABASE​​ [TESTDB]

SET​​ MULTI_USER​​ WITH​​ ROLLBACK​​ IMMEDIATE

To view the current isolation level of SQL Server, execute the script below.

SELECT​​ 

 CASE​​ transaction_isolation_level​​ 

  WHEN​​ 0​​ THEN​​ 'Unspecified'​​ 

  WHEN​​ 1​​ THEN​​ 'ReadUncommitted'​​ 

  WHEN​​ 2​​ THEN​​ 'ReadCommitted'​​ 

  WHEN​​ 3​​ THEN​​ 'Repeatable'​​ 

  WHEN​​ 4​​ THEN​​ 'Serializable'​​ 

  WHEN​​ 5​​ THEN​​ 'Snapshot'​​ 

 END​​ AS​​ TRANSACTION_ISOLATION_LEVEL​​ 

FROM​​ 

 sys.dm_exec_sessions​​ 

WHERE​​ 

 session_id​​ =​​ @@SPID

Below are some very useful​​ Change Data Capture (CDC) T-SQL Commands.

--Check if cdc is enabled

 USE​​ master​​ 

 GO​​ 

 SELECT​​ [name],​​ database_id,​​ is_cdc_enabled ​​​​ 

 FROM​​ sys.databases ​​ ​​ ​​ ​​ ​​ ​​​​ 

 GO ​​ ​​ ​​ ​​​​ 

--Enabled CDC at database level

 USE​​ AdventureWorks​​ 

 GO​​ 

 EXEC​​ sys.sp_cdc_enable_db​​ 

 GO ​​ 

--Check which tables are CDC enabled

 USE​​ AdventureWorks​​ 

 GO​​ 

 SELECT​​ [name],​​ is_tracked_by_cdc ​​​​ 

 FROM​​ sys.tables​​ 

 GO ​​​​ 

--Cnabled CDC for a table

 USE​​ AdventureWorks​​ 

 GO​​ 

 EXEC​​ sys.sp_cdc_enable_table​​ 

 @source_schema​​ =​​ N'HumanResources',​​ 

 @source_name ​​ ​​​​ =​​ N'Shift',​​ 

 @role_name ​​ ​​ ​​ ​​​​ =​​ NULL​​ 

 GO

--Disable CDC on a table​​ 

 USE​​ AdventureWorks;

 GO

 EXECUTE​​ sys.sp_cdc_disable_table

  @source_schema​​ =​​ N'HumanResources',

  @source_name​​ =​​ N'Shift',

  @capture_instance​​ =​​ N'HumanResources_Shift';

 GO

--CaptureSelectedColumns

 EXEC​​ sys.sp_cdc_enable_table​​ 

 @source_schema​​ =​​ N'HumanResources',​​ 

 @source_name ​​ ​​​​ =​​ N'Shift',​​ 

 @role_name ​​ ​​ ​​ ​​​​ =​​ NULL,​​ 

 @captured_column_list​​ =​​ '[ShiftID],[Name]'​​ 

 GO

Restore With VERIFY ONLY

RESTORE​​ VERIFYONLY​​ FROM ​​​​ DISK​​ =​​ N'H:\Backup_location_directory_Path\backupfile.bak'​​ with​​ NOUNLOAD;​​ 

This is a cool script to monitor backup and restore activity.

SELECT​​ session_id​​ as​​ SPID,​​ command,​​ a.text​​ AS​​ Query,​​ start_time,​​ percent_complete,​​ dateadd(second,estimated_completion_time/1000,​​ getdate())​​ as​​ estimated_completion_time​​ 

FROM​​ sys.dm_exec_requests​​ r​​ CROSS​​ APPLY​​ sys.dm_exec_sql_text(r.sql_handle)​​ a​​ 

WHERE​​ r.command​​ in​​ ('BACKUP DATABASE','RESTORE DATABASE')