LetsGoRafting

select

hadrc.cluster_name,

SERVERPROPERTY('MachineName') AS [ServerName],

SERVERPROPERTY('InstanceName') AS [Instance],

SERVERPROPERTY('Edition') AS [Edition],

LetsGoRafting

-- Last updated October 1, 2021
WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]

LetsGoRafting

DECLARE @DatabaseName nvarchar(255)
DECLARE @UserName nvarchar(255) 
DECLARE @Command nvarchar(1000)
DECLARE @SqlStatement nvarchar(4000)

IF OBJECT_ID( 'tempdb..#temp') IS NOT NULL
DROP TABLE tempdb..#temp 
 
CREATE TABLE tempdb..#temp (name VARCHAR(100))

LetsGoRafting

#----enter path---#
$targetpath = "C:\drive\"
#----enter the days---#
$days = 5
#----extension of the file to delete---#
$Extension = "*.trn"
$Now = Get-Date
$LastWrite = $Now.AddDays(-$days)
#----- get files based on lastwrite filter in the specified folder ---#
$Files = Get-Childitem $targetpath -Include $Extension -Recurse | Where 

LetsGoRafting

select sp.name as login,
       sp.type_desc as login_type,
       sl.password_hash,
       sp.create_date,
       sp.modify_date,
       case when sp.is_disabled = 1 then 'Disabled'
            else 'Enabled' end as status
from sys.server_principals sp
left join sys.sql_logins sl
          on sp.principal_id = sl.principal_id

LetsGoRafting

# Create a job that runs this powershell script, and voila!

#Days older than
$HowOld = -3

#Path to the root folder
$Path = "myPath"

#Deletion files task
get-childitem $Path -recurse | where {$_.lastwritetime -lt (get-date).adddays($HowOld) -and -not $_.psiscontainer} |% {remove-item $_.fullname -force -verbose}

LetsGoRafting

-- Blocking tool
	DECLARE @sp_who2 TABLE(SPID INT, Status VARCHAR(MAX), LOGIN VARCHAR(MAX), HostName VARCHAR(MAX), BlkBy VARCHAR(MAX), DBName VARCHAR(MAX), Command VARCHAR(MAX), CPUTime INT, DiskIO INT, LastBatch VARCHAR(MAX), ProgramName VARCHAR(MAX), SPID_1 INT, REQUESTID INT)
	INSERT INTO @sp_who2 EXEC sp_who2
 
	SELECT sp.SPID, sp.Status, sp.[LOGIN], sp.HostName, sp.BlkBy, sp.DBName, sp.Command, sp.ProgramName, der.start_time, sp.LastBatch, der.text, '' AS [---], 'KILL '+CONVERT(VARCHAR(32),sp.SPID) AS [T-SQL]
	FROM @sp_who2 sp
	LEFT JOIN (
		SELECT der.session_id, der.start_time, der.status, der.command, dest.text 
		FROM master.sys.dm_exec_requests der 
		CROSS APPLY master.sys.dm_exec_sql_text(der.sql_handle) dest 

LetsGoRafting

----------------------------------------------------------------
-------- Ultimate Compression Savings Estimation Check ---------
----------------------------------------------------------------
-- Author: Eitan Blumin | https://www.eitanblumin.com
-- Create Date: 2019-12-08
-- Last Update: 2021-03-21
-- Source: http://bit.ly/SQLCompressionEstimation
-- Full Link: https://gist.github.com/EitanBlumin/85cf620f7267b234d677f9c3027fb7ce
-- GitHub Repo: https://github.com/MadeiraData/MadeiraToolbox/blob/master/Utility%20Scripts/ultimate_compression_savings_estimation_whole_database.sql
-- Blog: https://eitanblumin.com/2020/02/18/ultimate-compression-savings-estimation-script-entire-database/

LetsGoRafting

-- basic query

SELECT      OPR.object_name
            , MSG.message_time
            , MSG.message
FROM        catalog.operation_messages  AS MSG
INNER JOIN  catalog.operations          AS OPR
    ON      OPR.operation_id            = MSG.operation_id
WHERE       MSG.message_type            = 120 -- errors 130 is warnings!

LetsGoRafting

SELECT name, value, value_in_use, [description] 
FROM sys.configurations
WHERE name like '%server memory%'
ORDER BY name OPTION (RECOMPILE);

SELECT 
physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB, 
    large_page_allocations_kb/1024 AS sql_large_page_allocations_MB, 
    locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,