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