Skip to main content

Scripts

.

***================================================================***
One Liners, Stored Procedures
***================================================================***
  1. To get the list of Objects
    SP_HELP
  2. To check the health of an instance/database
    SP_HELPDB OR SP_HELPDB ‘<DB_NAME>’
  3. To Get the logins list
    SP_HELPLOGINS OR SP_HELPLOGINS ‘<LOGIN_NAME>’
  4. 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   
  5. Execute this line to get the transfer logins as a script from one instance to another
    SP_HELP_REVLOGIN
  6. Execute to below to know the members in a group login
    EXEC XP_LOGININFO 'GROUP\LOGIN’, ‘MEMBERS'
    /** Give Input as Group login name name **/
  7. Execute below to remove the databases from replication.
    EXEC SP_REMOVEDBREPLICATION '<DB_NAME>'
  8. 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
    */
  9. 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>'
  10. To Get the process list
    SELECT * FROM SYS. SYSPROCESSES
    WHERE SPID>50 AND (LOWER(STATUS) = 'SLEEPING' OR LOWER(STATUS) = 'RUNNABLE' OR LOWER(STATUS) = 'BACKGROUND';
  11. 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
***================================================================***
  1. Command to check the integrity of a database
    DBCC CHECKDB (<DATABASE NAME>) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
  2. 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;
  3. To Get the Query running in that session
    DBCC INPUTBUFFER(<SESSION_ID>)
  4. To Check Open transaction
    DBCC OPENTRAN
  5. 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.
  1. To get the up time/startup time of any SQL server
    SELECT
         @@SERVERNAME, SQLSERVER_START_TIME 
    FROM 
         SYS.DM_OS_SYS_INFO
  2. To remove or Drop database
    USE MASTER
    GO
    ALTER DATABASE <DB_NAME> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    GO
    DROP DATABASE <DB_NAME>;
  3. To get the list of all user databases
    SELECT NAME
    FROM SYS.DATABASES
    WHERE DATABASE_ID > 4 AND GROUP_DATABASE_ID IS NULL
  4. To set the databases Offline from online we use this
    ALTER DATABASE <DB_NAME> SET OFFLINE WITH ROLLBACK IMMEDIATE
  5. 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;
  6. 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
  7. 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;
  8. 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;
  9. Version Store Space Usage of database
    SELECT DB_NAME(DATABASE_ID),*
    FROM SYS.DM_TRAN_VERSION_STORE_SPACE_USAGE

***================================================================***
Files related
  1. 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; 
  2. 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
  3. 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
  4. 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;
***================================================================***
Logins and User Related
  1. 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
  2. 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
  3. 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 
  4. 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
  1. 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');
  2. 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*/
  3. 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
  4. 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
  5. 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>
  6. 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
    **/
  7. 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  
  8. 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; 
  9. 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
  10. 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; 
  11. Query Used to find the long running transactions 
    SELECT 
         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
    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
    WHERE
         T.TRANSACTION_BEGIN_TIME < DATEADD(MINUTE, -5, GETDATE())
    ORDER BY 
         T.TRANSACTION_BEGIN_TIME DESC
***================================================================***
Backup and Restore Related
  1. To take Backup of a databases use below
    BACKUP DATABASE <DB_NAME>
    TO DISK = 'FILE NAME WITH PATH AND EXTENSION'
  2. To bring the database Online from restoring state
    RESTORE DATABASE <DB_NAME> WITH RECOVERY
  3. 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>
  4. 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
  5. 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
  6. 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>'
  7. 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>'
  8. 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'
  9. 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
  1. 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 
  2. 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 
***================================================================***
Always on Availability related
  1. To remove databases from AOAG (run on primary instance)
    ALTER AVAILABILITY GROUP <AOAG GROUP NAME>
    REMOVE DATABASE <DB NAME>;
  2. 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 
  3. 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
  1. To Operate pair in high performance mode – Async
    --Connect to principal server and execute
         ALTER DATABASE [DB_NAME] SET PARTNER SAFETY OFF
  2. 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
  3. 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

  4. 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 

  5. Run the below query to know the endpoint details
    SELECT * FROM SYS.DATABASE_MIRRORING_ENDPOINTS

  6. 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

 


Comments

Post a Comment

Popular posts from this blog

6. SQL Server Agent.

All SQL Server Agent details are maintained by MSDB databases, which store all the below information All individual jobs and maintenances plan related jobs. Schedules of all jobs. Operator, Database mails. All Alerts related information. All the Error log information is stored here. What is SQL server Agent? SQL Server is an individual service and it’s a default service which comes with the SQL server engine service, All the agent is stored in msdb. Why SQL server Agent? This SQL server agent is used for providing automation for all SQL server administrative tasks. What is operator? All the notifications of a job are received by this person based on the job status. What is Database mail? Database mail is the from address from where and which SMTP the notification to Operator need to send is given while configuring database mail, Only user with DatabaseMailUser role can configure this, By default Database mail is not enabled in older versions, we use SSAC(SQL Surface Area Conf...
Post a Comment
Read more

Index

Microsoft SQL Server Database  Administration  Concepts. Module1:  Starting with SQL Server   Responsibilities of Database Administrator  Types of DBAs  History of SQL Server  Editions of SQL Server  Tools of SQL Server  Differences between Standard and Enterprise editions  Instances types in SQL Server  Default Instance  Named Instance  SQL Server Services  Instance aware services  Instance unaware services Module2:  Installation, Patching, Migration, Upgradation of SQL Server.   Environments Installation of SQL Server SQL Server Instances SQL Server Services Ports, Protocols and Service Accounts. Patching Migration & Upgradation. Module3:  Creation and Managing SQL Server Databases Databases. Types of databases and Its Properties Database Architecture Page Extent Files File Groups Modifying Database Modifying Files and File Parameters File Movement Database Corruption Log File ...
Post a Comment
Read more

Microsoft SQL Server Architecture

MS-SQL Server is a Client-Server Architecture. MS-SQL Server process starts with the client application sending a request, The SQL Server accepts, process and replies to the request. SQL server Architecture contains the following major components they are Protocol Layer. Relational Engine. Storage Engine. Databases (Files). Protocol Layer This supports three type of client-server architecture            1.   Shared Memory :  SHARED MEMORY PROTOCOL  Here MS-SQL server provides and supports shared memory protocol here CLIENT and MS-SQL server are on the same machine. To get connected to the default instance on the machine we can use SSMS (SQL Server Management Studio) and to connect use any of the type in SSMS connection method ".", "localhost", "127.0.0.1", "Machine_name\Instance_name"           2.  TCP/IP :  The client and MS-SQL server is on different physical location and different machines, whic...
Post a Comment
Read more

1. Starting with SQL Server

Topics In This Module. Responsibilities of Database Administrator  Types of DBAs  History of SQL Server  Editions of SQL Server  Tools of SQL Server  Differences between Standard and Enterprise editions  Instances types in SQL Server  SQL Server Services 
Post a Comment
Read more

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

Topics In This Module. Environments Installation of SQL Server SQL Server Instances SQL Server Services Ports, Protocols and Service Accounts. Patching Migration & Upgradation. *** ============================================================================*** Environments There are two types of environments in general they are: Pre-Prod Environments. => Which Involves Dev environment and UAT environment. Prod Environments. => We do have OLAP (Online Analytical Processing) and OLTP (Online Transactional Processing) Servers in production. SQL Server ->  Instance  ->  database  ->  File groups  ->  Files  ->  as two types below The Files in SQL Server are of two types they are Data files  ->  Extends  ->  Pages  ->  Header part, Data part(8060 Bytes), Row off Set Log files  ->  Virtual logs  ->  LSN  ->           ...
Post a Comment
Read more