Scripts

***================================================================***
One Liners, Stored Procedures

***================================================================***

To get the list of Objects
SP_HELP
To check the health of an instance/database
SP_HELPDB OR SP_HELPDB ‘<DB_NAME>’
To Get the logins list
SP_HELPLOGINS OR SP_HELPLOGINS ‘<LOGIN_NAME>’
To get the list of connections, active or inactive
SP_WHO or SP_WHO2
SP_WHO2 ACTIVE --For Active Transactions.
SP_WHO2 <Session ID> --To get details about specific Transaction/Session
Execute this line to get the transfer logins as a script from one instance to another
SP_HELP_REVLOGIN
Execute to below to know the members in a group login
EXEC XP_LOGININFO 'GROUP\LOGIN’, ‘MEMBERS'
/** Give Input as Group login name name **/
Execute below to remove the databases from replication.
EXEC SP_REMOVEDBREPLICATION '<DB_NAME>'
To read the SQL server and SQL agent Error Logs use the below.
SP_READERRORLOG <LOG_NUMBER>, <LOG_TYPE>, <SEARCH_TERM1>, <SEARCH_TERM2>, <START_DATE>, <END_DATE>, <SORT_ORDER>;
--OR
XP_READERRORLOG <LOG_NUMBER>, <LOG_TYPE>, <SEARCH_TERM1>, <SEARCH_TERM2>, <START_DATE>, <END_DATE>, <SORT_ORDER>;
/*
<LOG_NUMBER>: 0-current log file, 1-next logfile archives with can go till 6 with default configuration.
<LOG_TYPE>: 1: Error log, 2: Agent error log
N'', --These are filters
N'', --These are filters
N'2022-07-19', --Start Date to Filter logs
N'2022-07-20', --End Date to Filter logs
N'ASC' --Sort Order DESC OR ASC(default)
Examples:
SP_READERRORLOG 0,1 --For current error logs;
SP_READERRORLOG 1,1 --For archived logs
*/
To Extract the orphan users (report option) and used to fix them
USE [DB_NAME]
GO
SP_CHANGE_USERS_LOGIN 'REPORT’ --For Extracting Orphan Users.
SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', ‘<LOGIN NAME>’, '<USER NAME>’ --For Fixing the Orphan Users.
SP_CHANGE_USERS_LOGIN 'AUTOFIX','<USER NAME>'
To Get the process list
SELECT * FROM SYS. SYSPROCESSES
WHERE SPID>50 AND (LOWER(STATUS) = 'SLEEPING' OR LOWER(STATUS) = 'RUNNABLE' OR LOWER(STATUS) = 'BACKGROUND';
To Read the SQL Audit log files
SELECT DISTINCT (ACTION_ID)
FROM SYS.FN_GET_AUDIT_FILE('<Audit File Name with Path>', DEFAULT, DEFAULT)

***================================================================***

DBCC Commands Used

***================================================================***

Command to check the integrity of a database
DBCC CHECKDB (<DATABASE NAME>) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
Command to repair database if corrupted with data loss
DBCC CHECKDB (TESTDB, REPAIR_ALLOW_DATA_LOSS | REPAIR_REBUILD | REPAIR_FAST) WITH NO_INFOMSGS;
/* Perform the Above Operation only after setting the database in single user mode, once repair is done change into multi user mode, As Below */
USE MASTER;
GO
ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB (DB_NAME, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
ALTER DATABASE DB_NAME SET MULTI_USER;
To Get the Query running in that session
DBCC INPUTBUFFER(<SESSION_ID>)
To Check Open transaction
DBCC OPENTRAN
To Check the log file size and percent of used in one click
DBCC SQLPERF(LOGSPACE)

Still more to come here -----

***================================================================***

Other Scripts

***================================================================***

Instances, database and tables related

If Database is in recovery pending then take it offline and then online, if issues still persist check the error log and take further action.

To get the up time/startup time of any SQL server
SELECT
@@SERVERNAME, SQLSERVER_START_TIME
FROM
SYS.DM_OS_SYS_INFO
To remove or Drop database
USE MASTER
GO
ALTER DATABASE <DB_NAME> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE <DB_NAME>;
To get the list of all user databases
SELECT NAME
FROM SYS.DATABASES
WHERE DATABASE_ID > 4 AND GROUP_DATABASE_ID IS NULL
To set the databases Offline from online we use this
ALTER DATABASE <DB_NAME> SET OFFLINE WITH ROLLBACK IMMEDIATE
To get the row count of all tables present in a database
USE [DB_NAME]
GO
SELECT SCHEMA_NAME (SCHEMA_ID) AS [SCHEMA_NAME], [TABLES].NAME AS [TABLENAME], SUM([PARTITIONS].[ROWS]) AS [TOTALROWCOUNT]
FROM SYS.TABLES AS [TABLES]
JOIN SYS.PARTITIONS AS [PARTITIONS] ON [TABLES].[OBJECT_ID] = [PARTITIONS].[OBJECT_ID] AND [PARTITIONS].INDEX_ID IN ( 0, 1 )
--WHERE [TABLES].NAME = N'<Name of Table>' --Keep The Table name here based on need
GROUP BY SCHEMA_NAME(SCHEMA_ID), [TABLES].NAME;
To check Database Fragmentation Use below Query
USE [DB_NAME]
GO
SELECT
OBJECT_NAME(OBJECT_ID), INDEX_ID, INDEX_TYPE_DESC, INDEX_LEVEL,
  AVG_FRAGMENTATION_IN_PERCENT, AVG_PAGE_SPACE_USED_IN_PERCENT, PAGE_COUNT
FROM
  SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(N'DB_NAME'), NULL, NULL, NULL, 'SAMPLED')
ORDER BY
  AVG_FRAGMENTATION_IN_PERCENT DESC
--Or
SELECT
S.NAME AS 'SCHEMA', T.NAME AS 'TABLE', I.NAME AS 'INDEX',
DDIPS.AVG_FRAGMENTATION_IN_PERCENT, DDIPS.PAGE_COUNT
FROM
SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN SYS.TABLES T ON T.OBJECT_ID = DDIPS.OBJECT_ID
INNER JOIN SYS.SCHEMAS S ON T.SCHEMA_ID = S.SCHEMA_ID
INNER JOIN SYS.INDEXES I ON I.OBJECT_ID = DDIPS.OBJECT_ID
AND DDIPS.INDEX_ID = I.INDEX_ID
WHERE
DDIPS.DATABASE_ID = DB_ID()
AND I.NAME IS NOT NULL
AND DDIPS.AVG_FRAGMENTATION_IN_PERCENT > 0
ORDER BY
DDIPS.AVG_FRAGMENTATION_IN_PERCENT DESC
To Get the Index Usage
SELECT
OBJECT_NAME(S.[OBJECT_ID]) AS [TABLENAME],
I.NAME AS [INDEXNAME], I.INDEX_ID, USER_SEEKS, USER_SCANS,
USER_LOOKUPS, USER_UPDATES
FROM
SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
WHERE
OBJECTPROPERTY(S.[OBJECT_ID],
'ISUSERTABLE') = 1 AND OBJECT_NAME(S.[OBJECT_ID]) IN ('SPT_IDENTITY_ENTITLEMENT','SPT_SYSLOG_EVENT','SPT_CERTIFICATION_ITEM','SPT_ENTITLEMENT_SNAPSHOT') AND (USER_SCANS+USER_SEEKS+USER_LOOKUPS)=0
ORDER BY
USER_SEEKS DESC;
To get the List of Objects using tempdb use below
WITH TempResultsCTE AS (
SELECT
s.login_name, s.session_id, tsu.exec_context_id, CASE WHEN tsu.user_objects_alloc_page_count > tsu.user_objects_dealloc_page_count
THEN (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) / 128 ELSE 0 END AS user_objects_MB,
CASE WHEN tsu.internal_objects_alloc_page_count > tsu.internal_objects_dealloc_page_count THEN (tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) / 128 ELSE 0
END AS internal_objects_MB, er.sql_handle, er.plan_handle, er.statement_start_offset, er.statement_end_offset
FROM
sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions s ON er.session_id = s.session_id
INNER JOIN sys.dm_db_task_space_usage tsu ON er.session_id = tsu.session_id
WHERE
s.is_user_process = 1
)
SELECT
login_name, session_id, exec_context_id,
user_objects_MB + internal_objects_MB AS total_objects_MB,
user_objects_MB, internal_objects_MB, CONVERT(XML, qp.query_plan) AS query_plan, SUBSTRING(st.text, (tr.statement_start_offset / 2) + 1, ((CASE tr.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE tr.statement_end_offset END - tr.statement_start_offset) / 2) + 1) AS statement_text, st.text AS full_statement_text
FROM
TempResultsCTE tr
CROSS APPLY sys.dm_exec_sql_text(tr.sql_handle) st
CROSS APPLY sys.dm_exec_text_query_plan(tr.plan_handle, tr.statement_start_offset, tr.statement_end_offset) qp
WHERE
tr.user_objects_MB + tr.internal_objects_MB > 0
ORDER BY
tr.user_objects_MB + tr.internal_objects_MB DESC;
Version Store Space Usage of database
SELECT DB_NAME(DATABASE_ID),*
FROM SYS.DM_TRAN_VERSION_STORE_SPACE_USAGE
Run a Thing on Multiple databases use below
DECLARE @DBNAME NVARCHAR(128)
DECLARE @SQL NVARCHAR(MAX)
DECLARE DB_CURSOR CURSOR FOR
-- LISTING ALL THE DATABASES TO CURSOR(CHANGE HERE LOGIC BASED ON NEED)
SELECT NAME FROM SYS.DATABASES WHERE STATE_DESC = 'ONLINE' AND NAME NOT IN ('MASTER','MSDB','MODEL','TEMPDB')
OPEN DB_CURSOR FETCH NEXT FROM DB_CURSOR INTO @DBNAME
SELECT @@SERVERNAME AS SQL_INSTANCE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
-- CHANGE BELOW QUERY WHICH IS TO BE EXECUTED ON ALL DATABASES(CHANAGE BACKUP TYPE, BACKUP PATH, DATE HIGHLIGHTED)
SET @SQL = 'BACKUP DATABASE ['+@DBNAME+'] TO DISK = N''I:\'+@DBNAME+'_FULLBACKUP_DDMMYYYY.bak'' WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N'''+@DBNAME+'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 1;'
EXEC SP_EXECUTESQL @SQL --EXECUTED THE ABOVE DECLARED SQL QUERY
SELECT 'BACKUP COMPLTED SUCESSFULLY FOR '+@DBNAME+' DATABASE'
FETCH NEXT FROM DB_CURSOR INTO @DBNAME
END
CLOSE DB_CURSOR
DEALLOCATE DB_CURSOR

***================================================================***

Files related

To get the Logical, Physical Files name and locations along with database and type of file
SELECT
D.NAME DATABASENAME, F.NAME LOGICALNAME,
F. PHYSICAL_NAME AS PHYSICALNAME, F. TYPE_DESC TYPEOFFILE
FROM
SYS. MASTER_FILES F INNER JOIN SYS. DATABASES D
ON D. DATABASE_ID = F. DATABASE_ID;
To get the sum of all list of files use the below
SELECT
(SUM (SIZE* 8.00) / 1024.00 / 1024.00/1024.00) AS USEDSPACE
FROM
MASTER.SYS. MASTER_FILES
(Or)
SELECT
'SERVER' = @@SERVERNAME,
DB_NAME(DATABASE_ID) AS DATABASE_NAME,
'TOTAL SIZE IN MEGABYTES'= CONVERT (DECIMAL (10,2), (SUM (SIZE * 8.00) / 1024.00)),
'TOTAL SIZE IN GIGABYTES' = CONVERT (DECIMAL (10,2), (SUM (SIZE * 8.00) / 1024.00 / 1024.00)),
'TOTAL SIZE IN TERABYTES' = CONVERT (DECIMAL (10,2),(SUM (SIZE * 8.00) / 1024.00 / 1024.00 /1024.00))
FROM
SYS. MASTER_FILES
GROUP BY DATABASE_ID
To do file movement for any databases use the below
USE MASTER
GO
ALTER DATABASE <DB NAME> MODIFY FILE (NAME = <Logical Filename>, FILENAME = '<Physical File Path With File Name>')
GO
ALTER DATABASE <DB NAME> MODIFY FILE (NAME = <Logical Filename>, FILENAME = '<Physical File Path With File Name>')
GO
ALTER DATABASE <DB NAME> MODIFY FILE (NAME = <Logical Filename>, FILENAME = '<Physical File Path With File Name>')
GO
To get Real Time Space Usage statistics
USE [DB_NAME]
GO
SELECT
GETDATE() AS RUNTIME,
DB_NAME(DATABASE_ID) AS DATABASE_NAME,
(USER_OBJECT_RESERVED_PAGE_COUNT * 8) AS USR_OBJ_KB,
(INTERNAL_OBJECT_RESERVED_PAGE_COUNT * 8) AS INTERNAL_OBJ_KB,
(VERSION_STORE_RESERVED_PAGE_COUNT * 8) AS VERSION_STORE_KB,
(UNALLOCATED_EXTENT_PAGE_COUNT * 8) AS FREESPACE_KB,
(MIXED_EXTENT_PAGE_COUNT * 8) AS MIXEDEXTENT_KB
FROM SYS.DM_DB_FILE_SPACE_USAGE;
To Get Log File Utilization for all the database
DECLARE @TEXT NVARCHAR(200)
CREATE TABLE #TABLE
(
DATABASE_NAME NVARCHAR(100), LOG_SIZE_MB NVARCHAR(50), LOG_SIZE_PERCENT NVARCHAR (50), STATUS NVARCHAR(50)
)
-- Command To Get Log Space Usage
SET @TEXT= 'DBCC SQLPERF(LOGSPACE)';
-- Insert Results into Temp Table
INSERT INTO #TABLE
EXEC SP_EXECUTESQL @TEXT;
--Query Results Excluding System Databases
SELECT * FROM #TABLE WHERE DATABASE_NAME NOT IN ('MASTER','MODEL','MSDB','TEMPDB') ORDER BY LOG_SIZE_MB DESC
--Clean Up
DROP TABLE #TABLE

***================================================================***

Logins and User Related

To Get the list of Logins and associated roles to it at server level
SELECT
R.NAME AS ROLE, M.NAME AS PRINCIPAL
FROM
MASTER.SYS. SERVER_ROLE_MEMBERS RM
INNER JOIN MASTER.SYS. SERVER_PRINCIPALS R ON R. PRINCIPAL_ID = RM. ROLE_PRINCIPAL_ID
AND R. TYPE = 'R'
INNER JOIN MASTER.SYS. SERVER_PRINCIPALS M ON M. PRINCIPAL_ID = RM. MEMBER_PRINCIPAL_ID
To Extract Users with the associated logins and its permissions in a database:
USE [DB_NAME]
GO
SELECT
[LOGIN TYPE] =
CASE SP. TYPE
WHEN 'U' THEN 'DomainAccount'
WHEN 'S' THEN 'LocalAccount'
WHEN 'G' THEN 'GroupAccount'
END,
CONVERT (CHAR (45), SP.NAME) AS SRVLOGIN,
CONVERT (CHAR (45), SP2.NAME) AS SRVROLE,
CONVERT (CHAR (25), DBP.NAME) AS DBUSER,
CONVERT (CHAR (25), DBP2.NAME) AS DBROLE
FROM
SYS. SERVER_PRINCIPALS AS SP JOIN
SYS. DATABASE_PRINCIPALS AS DBP ON SP.SID=DBP.SID JOIN
SYS. DATABASE_ROLE_MEMBERS AS DBRM ON DBP. PRINCIPAL_ID=DBRM.MEMBER_PRINCIPAL_ID
JOIN
SYS. DATABASE_PRINCIPALS AS DBP2 ON DBRM. ROLE_PRINCIPAL_ID=DBP2.PRINCIPAL_ID LEFT JOIN
SYS. SERVER_ROLE_MEMBERS AS SRM ON SP. PRINCIPAL_ID=SRM. MEMBER_PRINCIPAL_ID LEFT JOIN
SYS. SERVER_PRINCIPALS AS SP2 ON SRM. ROLE_PRINCIPAL_ID=SP2.PRINCIPAL_ID
To get the list of users based on their role assigned
USE [DB_NAME]
GO
SELECT
MEMBERS.NAME AS 'MEMBERS_NAME',
ROLES.NAME AS 'ROLES_NAME',
ROLES.TYPE_DESC AS 'ROLES_DESC', MEMBERS.TYPE_DESC AS 'MEMBERS_DESC'
FROM
SYS.DATABASE_ROLE_MEMBERS ROLEMEM
INNER JOIN SYS.DATABASE_PRINCIPALS ROLES ON ROLEMEM.ROLE_PRINCIPAL_ID = ROLES.PRINCIPAL_ID
INNER JOIN SYS.DATABASE_PRINCIPALS MEMBERS ON ROLEMEM.MEMBER_PRINCIPAL_ID = MEMBERS.PRINCIPAL_ID
WHERE ROLES.NAME = 'DB_OWNER' --Change Role Name Here Based On The Need
ORDER BY MEMBERS.NAME
Find Users and details who are currently running the query with Hash
SELECT
S.HOST_NAME, S.LOGIN_NAME, INPUT_BUFFER.EVENT_INFO
FROM
SYS.DM_EXEC_SESSIONS AS S
JOIN SYS.DM_EXEC_REQUESTS AS R ON R.SESSION_ID = S.SESSION_ID
CROSS APPLY SYS.DM_EXEC_INPUT_BUFFER(S.SESSION_ID, R.REQUEST_ID) AS INPUT_BUFFER
WHERE
S.IS_USER_PROCESS = 1
AND R.QUERY_HASH = 0X90F7BA12EAE9502E --Change Query Hash Here Based On Need.

***================================================================***

Session/Process, Locks, Blockings, Hung and Others Related

To get the list of process running and what are they is given using below
SELECT SPID, DB_NAME (DBID) AS DB_NAME, HOSTNAME, LOGINAME, LAST_BATCH, PROGRAM_NAME FROM SYS. SYSPROCESSES WHERE PROGRAM_NAME <> ' '; --Change the program name here based on need
--OR
SELECT * FROM SYS. SYSPROCESSES WHERE SPID>50 AND (LOWER(STATUS) = 'SLEEPING' OR LOWER(STATUS) = 'RUNNABLE' OR LOWER(STATUS) = 'BACKGROUND');
For user session/process details
SELECT SPID, HOSTNAME, LOGINAME, DB_NAME(DBID) AS DB_NAME, STATUS
FROM SYS. SYSPROCESSES
WHERE SPID>50 AND DB_NAME(DBID) NOT IN ('MASTER', 'MODEL', 'MSDB')
/* AS spid<50 are internal system process*/
To Kill the deadlock session
USE MASTER
GO
DECLARE @KILL VARCHAR(MAX) = '';
SELECT @KILL = @KILL + 'KILL ' + CONVERT (VARCHAR (10), SPID) + '; '
FROM MASTER.SYS.SYSPROCESSES
WHERE SPID > 50 AND DBID = DB_ID('<YOUR_DB_NAME>')
--SELECT (@KILL); --To get the queries
EXEC(@KILL); --To Kill the blockings
GO
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [<YOUR_DB_NAME>] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [<YOUR_DB_NAME>] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
To Check any hung session if any
SELECT @@SERVERNAME AS SERVERNAME,CMD, SPID, KPID, LOGIN_TIME, STATUS, HOSTNAME, NT_USERNAME, LOGINAME, HOSTPROCESS, CPU, MEMUSAGE, PHYSICAL_IO
FROM SYS. SYSPROCESSES
WHERE CMD = 'KILLED/ROLLBACK';
--OR
SELECT * FROM SYS. SYSPROCESSES
WHERE CMD = 'KILLED/ROLLBACK' OR BLOCKED <> 0
To Check the execution percentage of a session we use below.
SELECT PERCENT_COMPLETE
FROM SYS.DM_EXEC_REQUESTS
WHERE SESSION_ID = <SESSION ID OF RESTORING QUERY>
To check the transaction locks on the database
SELECT * FROM SYS.DM_TRAN_LOCKS
WHERE DB_NAME(RESOURCE_DATABASE_ID) = '<DB_NAME>' --Keep The database name here.
/**
Locks looks something like below which means
PAG: 11:3:8 -- which means as below
11 is database id
3 is file id
and 8 is page id
**/
Query Used to retrieve the details about waiting types, CPU Usage Also with Queries
SELECT
DM_WS.WAIT_DURATION_MS, DM_WS.WAIT_TYPE, DM_ES.STATUS,
DM_T.TEXT, DM_QP.QUERY_PLAN, DM_WS.SESSION_ID, DM_ES.CPU_TIME,
DM_ES.MEMORY_USAGE, DM_ES.LOGICAL_READS,
DM_ES.TOTAL_ELAPSED_TIME, DM_ES.PROGRAM_NAME,
DB_NAME(DM_R.DATABASE_ID) DATABASENAME
--Optional Columns Include Based On Need
--DM_WS.BLOCKING_SESSION_ID,
--DM_R.WAIT_RESOURCE,
--DM_ES.LOGIN_NAME,
--DM_R.COMMAND,
--DM_R.LAST_WAIT_TYPE
FROM
SYS.DM_OS_WAITING_TASKS DM_WS
INNER JOIN SYS.DM_EXEC_REQUESTS DM_R ON DM_WS.SESSION_ID = DM_R.SESSION_ID
INNER JOIN SYS.DM_EXEC_SESSIONS DM_ES ON DM_ES.SESSION_ID = DM_R.SESSION_ID
CROSS APPLY SYS.DM_EXEC_SQL_TEXT (DM_R.SQL_HANDLE) DM_T
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (DM_R.PLAN_HANDLE) DM_QP
--WHERE DM_ES.IS_USER_PROCESS = 1
Query Gives Us Top 10 Queries consuming most of CPU
SELECT
TOP 10 QUERY_STATS.QUERY_HASH AS [QUERY HASH], SUM(QUERY_STATS.TOTAL_WORKER_TIME) / SUM(QUERY_STATS.EXECUTION_COUNT) AS [CPU TIME], MIN(QUERY_STATS.STATEMENT_TEXT) AS [STATEMENT TEXT]
FROM (
SELECT QS.*, SUBSTRING(ST.TEXT, (QS.STATEMENT_START_OFFSET/2) + 1,
((CASE STATEMENT_END_OFFSET WHEN -1 THEN DATALENGTH(ST.TEXT) ELSE QS.STATEMENT_END_OFFSET END - QS.STATEMENT_START_OFFSET)/2) + 1) AS STATEMENT_TEXT
FROM
SYS.DM_EXEC_QUERY_STATS AS QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS ST) AS QUERY_STATS
GROUP BY QUERY_STATS.QUERY_HASH
ORDER BY 2 DESC;
Query Used to find sessions using high Memory
SELECT
SQLTEXT.TEXT, REQ.BLOCKING_SESSION_ID, SP.HOSTNAME,
SP.CMD, SP.LASTWAITTYPE, SP.LAST_BATCH, REQ.SESSION_ID,
REQ.STATUS, REQ.COMMAND, REQ.CPU_TIME, REQ.TOTAL_ELAPSED_TIME
FROM
SYS.SYSPROCESSES SP,SYS.DM_EXEC_REQUESTS REQ
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) AS SQLTEXT
WHERE SP.SPID = REQ.SESSION_ID
Query Used to find a Query/Stored Procedure That is running most number of Times
SELECT
TOP 50 USECOUNTS, CACHEOBJTYPE, OBJTYPE, TEXT
FROM
SYS.DM_EXEC_CACHED_PLANS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE)
WHERE USECOUNTS > 1
ORDER BY USECOUNTS DESC;
Query Used to find the long running transactions
SELECT
GETDATE() AS Current_DateTime,@@SERVERNAME AS SQL_InstanceName,
SQLTEXT.TEXT AS SQLQuery, S.SESSION_ID,S.LOGIN_NAME, R.STATUS,
R.COMMAND, R.CPU_TIME, R.TOTAL_ELAPSED_TIME,R.WAIT_TYPE, R.WAIT_TIME,
R.WAIT_RESOURCE, R.BLOCKING_SESSION_ID, T.TRANSACTION_ID,
T.TRANSACTION_BEGIN_TIME, T.TRANSACTION_STATE, T.TRANSACTION_STATUS,
QP.QUERY_PLAN AS EXECUTION_PLAN, P.HOSTNAME
FROM
SYS.DM_TRAN_ACTIVE_TRANSACTIONS T
JOIN SYS.DM_TRAN_SESSION_TRANSACTIONS ST ON T.TRANSACTION_ID = ST.TRANSACTION_ID
JOIN SYS.DM_EXEC_SESSIONS S ON ST.SESSION_ID = S.SESSION_ID
JOIN SYS.DM_EXEC_REQUESTS R ON S.SESSION_ID = R.SESSION_ID
JOIN SYS.SYSPROCESSES P ON S.SESSION_ID = P.SPID
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) AS SQLTEXT
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(R.PLAN_HANDLE) AS QP
WHERE
T.TRANSACTION_BEGIN_TIME < DATEADD(MINUTE, -5, GETDATE())
ORDER BY
T.TRANSACTION_BEGIN_TIME DESC

***================================================================***

CPU & Memory Related

Operating System memory Overview
SELECT
GETDATE() AS SNAPSHOTTIME, @@SERVERNAME AS SQL_INSTANCENAME,
TOTAL_PHYSICAL_MEMORY_KB / 1024 AS TOTAL_PHYSICAL_MEMORY_MB,
AVAILABLE_PHYSICAL_MEMORY_KB / 1024 AS AVAILABLE_PHYSICAL_MEMORY_MB,
TOTAL_PAGE_FILE_KB / 1024 AS TOTAL_PAGE_FILE_MB,
AVAILABLE_PAGE_FILE_KB / 1024 AS AVAILABLE_PAGE_FILE_MB,
SYSTEM_MEMORY_STATE_DESC AS MEMORY_STATE
FROM
SYS.DM_OS_SYS_MEMORY
SQL Server Process Memory Usage

SELECT
GETDATE() AS SNAPSHOTTIME, @@SERVERNAME AS SQL_INSTANCENAME,
PHYSICAL_MEMORY_IN_USE_KB / 1024 AS PHYSICALMEMORY_MB,
LOCKED_PAGE_ALLOCATIONS_KB / 1024 AS LOCKEDPAGES_MB,
LARGE_PAGE_ALLOCATIONS_KB / 1024 AS LARGEPAGES_MB,
MEMORY_UTILIZATION_PERCENTAGE AS MEMORYUTILIZATION_PERCENT,
AVAILABLE_COMMIT_LIMIT_KB / 1024 AS AVAILABLECOMMIT_MB,
PROCESS_PHYSICAL_MEMORY_LOW AS ISPHYSICALMEMORYLOW,
PROCESS_VIRTUAL_MEMORY_LOW AS ISVIRTUALMEMORYLOW,
PAGE_FAULT_COUNT AS PAGEFAULTS

FROM
SYS.DM_OS_PROCESS_MEMORY

SQL Server System-Level Memory and CPU Info

SELECT
GETDATE() AS SNAPSHOTTIME, @@SERVERNAME AS SQL_INSTANCENAME,
CPU_COUNT AS LOGICALCPU_COUNT,HYPERTHREAD_RATIO AS
HYPERTHREADING_RATIO, SCHEDULER_COUNT AS SCHEDULER_COUNT,
PHYSICAL_MEMORY_KB / 1024 AS TOTALPHYSICALMEMORY_MB,
COMMITTED_KB / 1024 AS SQL_COMMITTEDMEMORY_MB,
COMMITTED_TARGET_KB / 1024 AS SQL_TARGETMEMORY_MB,
SQLSERVER_START_TIME AS SQLSERVERSTARTTIME, MS_TICKS AS
SERVERUPTIME_MILLISECONDS, OS_QUANTUM AS
SCHEDULERQUANTUM_MICROSECONDS

FROM
SYS.DM_OS_SYS_INFO

Memory Usage by SQL Server Memory Clerks

SELECT
GETDATE() AS SNAPSHOTTIME, @@SERVERNAME AS SQL_INSTANCENAME,
TYPE AS CLERKTYPE,SUM(PAGES_KB) / 1024 AS TOTALPAGES_MB,
SUM(VIRTUAL_MEMORY_COMMITTED_KB) / 1024 AS TOTALVIRTUALCOMMITTED_MB,
SUM(SHARED_MEMORY_COMMITTED_KB) / 1024 AS TOTALSHAREDCOMMITTED_MB

FROM
SYS.DM_OS_MEMORY_CLERKS
GROUP BY TYPE
ORDER BY TOTALVIRTUALCOMMITTED_MB DESC

Queries Waiting for Memory Grants(Resource Semaphore)

SELECT
GETDATE() AS SNAPSHOTTIME, @@SERVERNAME AS SQL_INSTANCENAME,
SESSION_ID, REQUESTED_MEMORY_KB / 1024 AS REQUESTED_MB,
IDEAL_MEMORY_KB / 1024 AS IDEAL_MB,QUERY_COST, WAIT_ORDER,
IS_NEXT_CANDIDATE, REQUEST_TIME

FROM
SYS.DM_EXEC_QUERY_MEMORY_GRANTS
WHERE GRANT_TIME IS NULL
ORDER BY REQUESTED_MEMORY_KB DESC;

Detailed Memory Grants with Query Texts - Real Time Memory Usage

SELECT
GETDATE() AS SNAPSHOTTIME, @@SERVERNAME AS SQL_INSTANCENAME,
MG.SESSION_ID, MG.REQUEST_TIME, MG.GRANT_TIME,
MG.REQUESTED_MEMORY_KB / 1024 AS REQUESTED_MB,
MG.GRANTED_MEMORY_KB / 1024 AS GRANTED_MB,
MG.IDEAL_MEMORY_KB / 1024 AS IDEAL_MB, MG.QUERY_COST,
MG.DOP, MG.IS_NEXT_CANDIDATE, MG.WAIT_ORDER, ST.TEXT AS SQLTEXT,
QP.QUERY_PLAN

FROM
SYS.DM_EXEC_QUERY_MEMORY_GRANTS AS MG
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(MG.PLAN_HANDLE) AS ST
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(MG.PLAN_HANDLE) AS QP
ORDER BY MG.REQUESTED_MEMORY_KB DESC;

Buffer Pool Usage

SELECT
GETDATE() AS SNAPSHOTTIME, @@SERVERNAME AS SQL_INSTANCENAME,
COUNT(*) AS PAGECOUNT, DB_NAME(DATABASE_ID), AS DATABASENAME, FILE_ID, PAGE_TYPE, SUM(ROW_COUNT) AS TOTALROWS_ACCROSS_PAGES, SUM(FREE_SPACE_IN_BYTES / 1024) AS FREE_SPACE_IN_KBYTES

FROM
SYS.DM_OS_BUFFER_DESCRIPTORS
WHERE DB_NAME(DATABASE_ID) IS NOT NULL
GROUP BY DATABASE_ID, FILE_ID, PAGE_TYPE
ORDER BY TOTALVIRTUALCOMMITTED_MB DESC

Memory Pressure Via Wait Stats

SELECT
GETDATE() AS SNAPSHOTTIME, @@SERVERNAME AS SQL_INSTANCENAME,
WAIT_TYPE, WAIT_TIME_MS / 1000.0 AS WAITTIME_SEC, WAITING_TASKS_COUNT

FROM
SYS.DM_OS_WAIT_STATS
WHERE WAIT_TYPE LIKE 'RESOURCE_SEMAPHORE%'
ORDER BY WAIT_TIME_MS DESC;

Memory Usage by Cache Stores

SELECT
GETDATE() AS SNAPSHOTTIME, @@SERVERNAME AS SQL_INSTANCENAME,
NAME AS CACHESTORE, TYPE, SUM(PAGES_KB) / 1024 AS MEMORYUSED_MB

FROM
SYS.DM_OS_MEMORY_CLERKS
WHERE TYPE LIKE 'CACHESTORE%'
GROUP BY NAME, TYPE

ORDER BY MEMORYUSED_MB DESC;

Memory Related Breakdown - Not Sure.

SELECT
GETDATE() AS SNAPSHOTTIME, @@SERVERNAME AS SQL_INSTANCENAME,
CAST(CFG.VALUE_IN_USE AS INT) AS MAXMEMORY_MB,
SI.COMMITTED_KB / 1024 AS COMMITTEDMEMORY_MB,
PM.PHYSICAL_MEMORY_IN_USE_KB / 1024 AS USEDMEMORY_MB,
(CAST(CFG.VALUE_IN_USE AS INT) - PM.PHYSICAL_MEMORY_IN_USE_KB / 1024) AS FREEMEMORY_MB,
(SELECT COUNT(*) * 8 / 1024 FROM SYS.DM_OS_BUFFER_DESCRIPTORS WHERE DATABASE_ID <> 32767) AS BUFFERPOOL_MB,
(SELECT SUM(PAGES_KB) / 1024 FROM SYS.DM_OS_MEMORY_CLERKS) AS CLERKMEMORY_MB

FROM
SYS.DM_OS_SYS_INFO AS SI
CROSS JOIN SYS.DM_OS_PROCESS_MEMORY AS PM
JOIN SYS.CONFIGURATIONS AS CFG ON CFG.NAME = 'MAX SERVER MEMORY (MB)';

Memory Related Queries

FROM
SYS.
GROUP BY TYPE
ORDER BY TOTALVIRTUALCOMMITTED_MB DESC

***================================================================***

Backup and Restore Related

To take Backup of a databases use below
BACKUP DATABASE <DB_NAME>
TO DISK = 'FILE NAME WITH PATH AND EXTENSION'
To bring the database Online from restoring state
RESTORE DATABASE <DB_NAME> WITH RECOVERY
Use Below to check if any backup or restore happening and its percentage of complete
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')
--OR
SELECT PERCENT_COMPLETE
FROM SYS.DM_EXEC_REQUESTS
WHERE SESSION_ID = <SESSION_ID_WHERE_BACKUP/RESTORE IS RUNNING>
To Check When the last backup is happened.
SELECT
DATABASE_NAME,
CONVERT (SMALLDATETIME, MAX (BACKUP_FINISH_DATE)) AS LAST_BACKUP,
DATEDIFF (D, MAX (BACKUP_FINISH_DATE), GETDATE ()) AS DAYS_SINCE_LAST FROM
MSDB.DBO. BACKUPSET
WHERE TYPE = 'L'
--D--FULL
--I--DIFF
--L--TLOG
GROUP BY DATABASE_NAME ORDER BY DAYS_SINCE_LAST
--OR Use below to get the list for only online databases
SELECT
DB.NAME,
CONVERT (SMALLDATETIME, MAX (BKP.BACKUP_FINISH_DATE)) AS LAST_BACKUP,
DATEDIFF (D, MAX (BKP.BACKUP_FINISH_DATE), GETDATE ()) AS DAYS_SINCE_LAST
FROM
SYS.DATABASES DB INNER JOIN MSDB.DBO.BACKUPSET BKP ON DB.NAME=BKP.DATABASE_NAME
WHERE
DB.STATE_DESC = 'ONLINE' AND BKP.TYPE ='D'
--D--FULL
--I--DIFF
--L--TLOG
GROUP BY
DB.NAME ORDER BY DAYS_SINCE_LAST DESC
To check where the backup file went with what name.
SELECT TOP 5 A. SERVER_NAME, A. DATABASE_NAME, BACKUP_FINISH_DATE, A. BACKUP_SIZE, CASE A.[TYPE]-- LET'S DECODE THE THREE MAIN TYPES OF BACKUPS HERE
WHEN 'D' THEN 'FULL'
WHEN 'I' THEN 'DIFFERENTIAL'
WHEN 'L' THEN 'TRANSACTION LOG'
ELSE A.[TYPE]
END AS BACKUPTYPE
-- BUILD A PATH TO THE BACKUP
,'\\' +
-- LET’S EXTRACT THE SERVER NAME OUT OF THE RECORDED SERVER AND INSTANCE NAME
CASE
WHEN PATINDEX ('%\%', A. SERVER_NAME) = 0 THEN A. SERVER_NAME
ELSE SUBSTRING (A. SERVER_NAME,1, PATINDEX ('%\%', A. SERVER_NAME)-1)
END
-- THEN GET THE DRIVE AND PATH AND FILE INFORMATION
+ '\' + REPLACE (B. PHYSICAL_DEVICE_NAME,':','$') AS
'\\SERVER\DRIVE\BACKUP_PATH\BACKUP_FILE'
FROM MSDB.DBO. BACKUPSET A JOIN MSDB.DBO. BACKUPMEDIAFAMILY B
ON A. MEDIA_SET_ID = B. MEDIA_SET_ID
WHERE A. DATABASE_NAME LIKE 'REPORTSERVER' --Change Database Name Here as needed.
AND A. TYPE='D' --Change the backup Type here based on need.
ORDER BY A. BACKUP_FINISH_DATE DESC
To Restore/Refresh the database use below.
SP_HELPDB [DB_NAME]
GO
RESTORE FILELISTONLY FROM DISK = 'FILE NAME WITH PATH AND EXTENSION'
GO
ALTER DATABASE [DB_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE MASTER
GO
RESTORE DATABASE [DB_NAME] FROM DISK = 'FILE NAME WITH PATH AND EXTENSION'
WITH REPLACE,
MOVE '<SOURCE FILE LOGICAL NAME FROM BACKUP FILE>' TO '<PATH AND FILE NAME OF DEST>',
MOVE '<SOURCE FILE LOGICAL NAME FROM BACKUP FILE>' TO '<PATH AND FILE NAME OF DEST>',
MOVE '<SOURCE FILE LOGICAL NAME FROM BACKUP FILE>' TO '<PATH AND FILE NAME OF DEST>'
To take Backup of a databases to URL use below
> Take the path or URL from the jobs and make it feasible.
BACKUP DATABASE <DATABASE_NAME>
TO URL = 'HTTPS://ABCD.BLOB.CORE.WINDOWS.NET/AZUSQL/AZUSQLPROD_DIFF_20220905_180000.BAK'
WITH DIFFERENTIAL,
CREDENTIAL= '<GET THE CREDS FROM SERVER IN CREDENTIAL TAB>'
To Restore a database from URL use below
SP_HELPDB [<DESTINATION DB NAME>]
GO
RESTORE FILELISTONLY FROM URL = '<PATH AND FILE NAME OF SOURCE DATABASE>'
WITH CREDENTIAL= 'XXXXXX'
To Restore a database from URL use below
ALTER DATABASE [<DESTINATION DB NAME>] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE MASTER
GO
RESTORE DATABASE <DATABASE NAME>
FROM URL = 'HTTPS://ABCD.BLOB.CORE.WINDOWS.NET/AZUSQL/AZU20220905_180000.BAK'
WITH CREDENTIAL= 'XXXXXX',
MOVE '<SOURCE FILE LOGICAL NAME FROM BACKUP FILE>' TO '<PATH AND FILE NAME OF DESTINATION>',
MOVE '<SOURCE FILE LOGICAL NAME FROM BACKUP FILE>' TO '<PATH AND FILE NAME OF DESTINATION>',
MOVE '<SOURCE FILE LOGICAL NAME FROM BACKUP FILE>' TO '<PATH AND FILE NAME OF DESTINATION>'
REPLACE, STATS=1

***================================================================***

Others

To get the default audits or trace data in SQL Server
SELECT * FROM FN_TRACE_GETTABLE(
(SELECT PATH FROM SYS.TRACES WHERE IS_DEFAULT = 1), DEFAULT)
WHERE LOGINNAME IN ('VADDHIS\CHARA') --Change Login name here based on need
--AND EVENTCLASS = 164 --Optional Filters
--AND NTUSERNAME <> 'SQLTELEMETRY' --Optional Filters
ORDER BY STARTTIME DESC
To list SQL Server Jobs running now
SELECT
SJ.NAME, SJA.SESSION_ID,SJA.START_EXECUTION_DATE,
CASE
WHEN SJA.START_EXECUTION_DATE IS NULL THEN 'NOT RUNNING'
WHEN SJA.START_EXECUTION_DATE IS NOT NULL AND SJA.STOP_EXECUTION_DATE IS NULL THEN
'RUNNING'
WHEN SJA.START_EXECUTION_DATE IS NOT NULL AND SJA.STOP_EXECUTION_DATE IS NOT NULL THEN 'NOT RUNNING'
END AS 'RUNSTATUS'
FROM
MSDB.DBO.SYSJOBS SJ
JOIN MSDB.DBO.SYSJOBACTIVITY SJA
ON SJ.JOB_ID = SJA.JOB_ID
WHERE
SESSION_ID = (SELECT MAX(SESSION_ID) FROM MSDB.DBO.SYSJOBACTIVITY)
AND SJA.START_EXECUTION_DATE IS NOT NULL
AND SJA.STOP_EXECUTION_DATE IS NULL
ORDER BY
SJA.START_EXECUTION_DATE
Disks Space where SQL Related Files are Stored
USE MASTER
GO
SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1;
RECONFIGURE
GO
SP_CONFIGURE 'XP_CMDSHELL',1;
RECONFIGURE
GO
BEGIN
SET ARITHIGNORE ON;
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(1000);
CREATE TABLE #DRVLETTER (DRIVE VARCHAR(500));
CREATE TABLE #DRVINFO(DRIVE VARCHAR(500) NULL, [MB FREE] DECIMAL(20,2), [MB TOTALSIZE] DECIMAL(20,2), [VOLUME NAME] VARCHAR(64));
INSERT INTO #DRVLETTER
EXEC XP_CMDSHELL 'WMIC VOLUME WHERE DRIVETYPE="3" GET CAPTION, FREESPACE, CAPACITY, LABEL';
DELETE FROM #DRVLETTER WHERE DRIVE IS NULL OR LEN(DRIVE) < 4 OR DRIVE LIKE '%CAPACITY%' OR DRIVE LIKE '%NOT RECOGNIZED%' OR DRIVE LIKE 'OPERABLE PROGRAM OR BATCH FILE.' OR DRIVE LIKE '%\\%\VOLUME%';
DECLARE @STRLINE VARCHAR(8000);
DECLARE @DRIVE VARCHAR(500);
DECLARE @TOTALSIZE REAL;
DECLARE @FREESIZE REAL;
DECLARE @VOLUMENAME VARCHAR(64);
WHILE EXISTS (SELECT 1 FROM #DRVLETTER)
BEGIN
SET ROWCOUNT 1;
SELECT @STRLINE = DRIVE FROM #DRVLETTER;
-- GET TOTALSIZE
SET @TOTALSIZE = CAST(LEFT(@STRLINE, CHARINDEX(' ', @STRLINE)) AS REAL) / 1024 / 1024;
-- REMOVE TOTAL SIZE
SET @STRLINE = REPLACE(@STRLINE, LEFT(@STRLINE, CHARINDEX(' ', @STRLINE)), '');
-- GET DRIVE
SET @DRIVE = LEFT(LTRIM(@STRLINE), CHARINDEX(' ', LTRIM(@STRLINE)));
SET @STRLINE = RTRIM(LTRIM(REPLACE(LTRIM(@STRLINE), LEFT(LTRIM(@STRLINE), CHARINDEX(' ', LTRIM(@STRLINE))),'')));
SET @FREESIZE = LEFT(LTRIM(@STRLINE), CHARINDEX(' ', LTRIM(@STRLINE)));
SET @STRLINE = RTRIM(LTRIM(REPLACE(LTRIM(@STRLINE),LEFT(LTRIM(@STRLINE), CHARINDEX(' ', LTRIM(@STRLINE))), '')));
SET @VOLUMENAME = @STRLINE;
INSERT INTO #DRVINFO SELECT @DRIVE, @FREESIZE / 1024 / 1024, @TOTALSIZE, @VOLUMENAME;
DELETE FROM #DRVLETTER;
END
SET ROWCOUNT 0;
-- POPULATE TEMP TABLE WITH LOGICAL DISKS
-- WORKAROUND FOR WINDOWS 2003 BUG: WMIC DOESN'T RETURN LONG VOLUME NAMES
SET @SQL = 'WMIC /FAILFAST:ON LOGICALDISK WHERE (DRIVETYPE ="3" AND VOLUMENAME!="RECOVERY" AND VOLUMENAME!="SYSTEM RESERVED") GET DEVICEID,VOLUMENAME /FORMAT:CSV';
IF OBJECT_ID('TEMPDB..#OUTPUT1') IS NOT NULL DROP TABLE #OUTPUT1;
CREATE TABLE #OUTPUT1 (COL1 VARCHAR(2048));
INSERT INTO #OUTPUT1
EXEC MASTER..XP_CMDSHELL @SQL;
DELETE #OUTPUT1 WHERE LTRIM(COL1) IS NULL OR LEN(COL1) = 1 OR COL1 LIKE 'NODE,DEVICEID,VOLUMENAME%' OR COL1 LIKE '%NOT RECOGNIZED%' OR COL1 LIKE '%PROGRAM OR BATCH%';
IF OBJECT_ID('TEMPDB..#LOGICALDISK') IS NOT NULL DROP TABLE #LOGICALDISK;
CREATE TABLE #LOGICALDISK(DEVICEID VARCHAR(128), VOLUMENAME VARCHAR(256));
DECLARE @NODENAME VARCHAR(128);
SET @NODENAME = (SELECT TOP 1 LEFT(COL1, CHARINDEX(',', COL1)) FROM #OUTPUT1);
-- CLEAN UP SERVER NAME
UPDATE #OUTPUT1 SET COL1 = REPLACE(COL1, @NODENAME, '');
INSERT INTO #LOGICALDISK SELECT LEFT(COL1, CHARINDEX(',', COL1) - 2), SUBSTRING(COL1, CHARINDEX(',', COL1) + 1, LEN(COL1))
FROM #OUTPUT1;
UPDATE DR SET DR.[VOLUME NAME] = LD.VOLUMENAME FROM #DRVINFO DR RIGHT OUTER JOIN #LOGICALDISK LD ON LEFT(DR.DRIVE, 1) = LD.DEVICEID WHERE LEN([VOLUME NAME]) = 1;
BEGIN
SELECT DISTINCT
CASE
WHEN LEN(DRIVE) = 3 THEN LEFT(DRIVE, 1)
ELSE DRIVE
END AS DRIVE,
[MB FREE], [MB TOTALSIZE],[VOLUME NAME], CEILING(([MB FREE] / [MB TOTALSIZE]) * 100) AS [PERCENT AVAILABLE]
FROM #DRVINFO, SYSALTFILES SAF WHERE LEFT(#DRVINFO.DRIVE, 1) = LEFT(SAF.FILENAME, 1) ORDER BY [PERCENT AVAILABLE] ASC;
END
END
DROP TABLE #LOGICALDISK;
DROP TABLE #DRVLETTER;
DROP TABLE #DRVINFO;
GO
SP_CONFIGURE 'XP_CMDSHELL',0;
RECONFIGURE
GO
SP_CONFIGURE 'SHOW ADVANCED OPTIONS',0;
RECONFIGURE
GO

***================================================================***

Always on Availability related

To remove databases from AOAG (run on primary instance)
ALTER AVAILABILITY GROUP <AOAG GROUP NAME>
REMOVE DATABASE <DB NAME>;
To Sync Logins from Primary instance to Secondary instance
--To get the Login with Same SID run the below on primary & then run the output of this on secondary.
SELECT N'CREATE LOGIN ['+SP.[NAME]+'] WITH PASSWORD=0X'+
CONVERT(NVARCHAR(MAX), L.PASSWORD_HASH, 2)+N' HASHED, '+
N'SID=0X'+CONVERT(NVARCHAR(MAX), SP.[SID], 2)+N';'
FROM MASTER.SYS.SERVER_PRINCIPALS AS SP
INNER JOIN MASTER.SYS.SQL_LOGINS AS L ON SP.[SID]=L.[SID]
WHERE SP.NAME = 'Login Name' --Change the user name here based on the need
Use Below Query to monitor sync of AOAG
SELECT
RS.IS_PRIMARY_REPLICA ISPRIMARY, RS.LAST_RECEIVED_LSN,
RS.LAST_HARDENED_LSN, RS.LAST_REDONE_LSN,
RS.END_OF_LOG_LSN, RS.LAST_COMMIT_LSN
FROM
SYS.AVAILABILITY_REPLICAS R INNER JOIN
SYS.DM_HADR_DATABASE_REPLICA_STATES RS ON R.REPLICA_ID=RS.REPLICA_ID
ORDER BY ISPRIMARY DESC

***================================================================***

Mirroring related

To Operate pair in high performance mode – Async
--Connect to principal server and execute
ALTER DATABASE [DB_NAME] SET PARTNER SAFETY OFF
To Operate pair in high safety mode – Sync
--Connect to principal server and execute
ALTER DATABASE [DB_NAME] SET PARTNER SAFETY FULL
ALTER DATABASE [DB_NAME] SET PARTNER SUSPEND
ALTER DATABASE [DB_NAME] SET PARTNER RESUME
ALTER DATABASE [DB_NAME] SET PARTNER FAILOVER
ALTER DATABASE [DB_NAME] SET PARTNER OFF
--ON Mirror
ALTER DATABASE [DB_NAME] SET PARTNER SUSPEND
ALTER DATABASE [DB_NAME] SET PARTNER RESUME
--ON Mirror
ALTER DATABASE [DB_NAME] SET PARTNER SUSPEND
ALTER DATABASE [DB_NAME] SET PARTNER RESUME
--ONLY WHEN PRINCIPLE IS DOWN
ALTER DATABASE [DB_NAME] SET PARTNER FAILOVER
Run below on principal databases based on requirement
--To disconnect the mirroring use the below
ALTER DATABASE <DATABASE NAME> SET PARTNER SAFETY OFF
--To change the database mode from Async to sync
ALTER DATABASE <DATABASE NAME> SET PARTNER SAFETY FULL
--To change the database mode from sync to Async
ALTER DATABASE <DATABASE NAME> SET PARTNER OFF
--To pause the mirroring between database use below
ALTER DATABASE <DATABASE NAME> SET PARTNER SUSPEND
--To Resume the mirroring between databases, use below
ALTER DATABASE <DATABASE NAME> SET PARTNER RESUME
--To failover the databases from principal to mirror use below
ALTER DATABASE <DATABASE NAME> SET PARTNER FAILOVER
--To Start or stop the endpoints of mirror in SQL Server
--To get the endpoint name
SELECT * FROM SYS.ENDPOINTS
ALTER ENDPOINT <Endpoint Name> STATE = STOPPED -- To stop the endpoint.
GO
ALTER ENDPOINT <Endpoint Name> STATE = STARTED -- To start the endpoint
GO
Run below on Mirror databases based on requirement
--To pause the mirroring between database use below
ALTER DATABASE <DATABASE NAME> SET PARTNER SUSPEND
--To Resume the mirroring between databases, use below
ALTER DATABASE <DATABASE NAME> SET PARTNER RESUME
--To failover the databases from principal to mirror use below –Manual failover
ALTER DATABASE <DATABASE NAME> SET PARTNER FAILOVER --Only when principal is down
--To failover the databases from principal to mirror use below –Forcible failover
ALTER DATABASE <DATABASE NAME> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
--To switch the mirroring off use below
ALTER DATABASE <DATABASE NAME> SET WITNESS OFF
--When the database is in restoring to bring it online use below
RESTORE DATABASE <DATABASE NAME> WITH RECOVERY
Run the below query to know the endpoint details
SELECT * FROM SYS.DATABASE_MIRRORING_ENDPOINTS
Run the below query to get the mirroring details
SELECT
DB_NAME(DATABASE_ID) AS DB_NAME,
MIRRORING_STATE_DESC, MIRRORING_ROLE_DESC,
MIRRORING_PARTNER_NAME, MIRRORING_PARTNER_INSTANCE, *
FROM SYS.DATABASE_MIRRORING

SELECT

DB_NAME(DBM.DATABASE_ID),

DB.NAME, DB.STATE_DESC, DBM.MIRRORING_STATE_DESC, SAF.NAME

FROM

SYS.DATABASE_MIRRORING DBM,

SYS.DATABASES DB,SYS.SYSALTFILES SAF

WHERE

DBM.DATABASE_ID = DB.DATABASE_ID AND

DB.DATABASE_ID=SAF.DBID AND

DBM.MIRRORING_STATE IS NULL AND

DB.STATE_DESC = 'ONLINE' AND

SAF.FILEID=2

***================================================================***

Others(Not SQL)

If Configuration management is not opening follow the below process to open it

cd %programfiles(x86)%\Microsoft\Microsoft SQL Server\100\Shared folder -- Go to this location
mofcomp "sqlmgmproviderxpsp2up.mof" -- check the file presence and run the command

To check the cluster details in AOAG configuration use this "cluadmin.msc" in run
To add users at AD level use "lusrmgr.msc" in run
To get hostname and Domain use the below
Get-wmiobject Win32_ComputerSystem -computer <System IP> | fl Name,Domain
To Resync the Time on any servers with domain use below commands one by one
net stop w32time
w32tm /unregister
w32tm /register
net start w32time
w32tm /config /syncfromflags:DOMHIER /update
w32tm /resync /nowait
To Extract the list of users in Administrators group use below
net localgroup Administrators > AdminsOn-%computername%.txt

Microsoft SQL Server Database Administration.

Search This Blog

Scripts

Comments

Post a Comment

Popular posts from this blog

6. SQL Server Agent.

Index

Microsoft SQL Server Architecture

1. Starting with SQL Server

2. Installation, Patching, Migration, Upgradation of SQL Server.