EitanBlumin

-- Function to Retrieve a global variable value
-- don't forget to convert to the correct data type
CREATE FUNCTION dbo.global_variable(@VariableName sysname)
RETURNS sql_variant
AS
BEGIN
RETURN (SELECT [value]
	FROM sys.extended_properties
	WHERE major_id = 0 AND minor_id = 0
	AND [name] = @VariableName)

EitanBlumin

/*
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Date: August, 2015
Description:
Use this script to easily roll forward multiple transaction log backups from a given folder.

More info:
https://eitanblumin.com/2018/10/28/t-sql-script-to-roll-forward-transaction-log-backups
*/
DECLARE

EitanBlumin

/*
Check for low PAGE compression success rates
============================================
Author: Eitan Blumin
Date: 2022-01-13
Based on blog post by Paul Randal:
https://www.sqlskills.com/blogs/paul/the-curious-case-of-tracking-page-compression-success-rates/
*/
DECLARE
	/* threshold parameters: */

EitanBlumin

CREATE EVENT SESSION [TrackFailedLogins] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.nt_username,sqlserver.database_id,sqlserver.session_id)
    WHERE (([severity]=(20) OR [severity]=(14) OR [severity]=(16)) 
    AND ([error_number]=(18056) 
    OR [error_number]=(17892) 
    OR [error_number]=(18061)
    OR [error_number]=(18452)
    OR [error_number]=(11248)
    OR [error_number]=(17806)

EitanBlumin

/*
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Date: November, 2018
Description:
Update @@SERVERNAME to Actual Machine Name.
Run as-is. The script is idempotent and requires no parameters.
SQL Service restart may be required in order to apply changes.

More info:
https://eitanblumin.com/2018/11/06/how-to-update-servername-to-actual-machine-name/

EitanBlumin

/*
AlwaysOn Availability Group Error Events
========================================
Author: Eitan Blumin
Date: 2020-05-31
This alert check the contents of the AlwaysOn_Health extended events session for data suspension, role changes, and other errors.
 
For more info:
https://docs.microsoft.com/sql/database-engine/availability-groups/windows/always-on-extended-events
*/

EitanBlumin

-- Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
-- Date: 2020-05-31
-- Last Update: 2021-04-22
-- Description: Collect T-SQL Events using an Extended Events Buffer

SET NOCOUNT ON;

DECLARE
  @SourceLinkedServer SYSNAME
, @MinimumDurationMilliSeconds BIGINT

EitanBlumin

-- Max Memory Calculation
-- Based on Tiger Toolbox script BP_Check (Copyright Pedro Lopes)
 
DECLARE @sqlmajorver int, @systemmem int, @systemfreemem int, @maxservermem int, @numa_nodes_afinned int, @numa int
DECLARE @mwthreads_count int, @mwthreads int, @arch smallint, @sqlcmd nvarchar(4000)
DECLARE @MinMBMemoryForOS INT, @RecommendedMaxMemMB INT
SET @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);
SET @arch = CASE WHEN @@VERSION LIKE '%<X64>%' THEN 64 WHEN @@VERSION LIKE '%<IA64>%' THEN 128 ELSE 32 END;
  
SELECT @maxservermem = CONVERT(int, [value]) FROM sys.configurations (NOLOCK) WHERE [Name] = 'max server memory (MB)';

EitanBlumin

DECLARE
	@LinkedServer NVARCHAR(300),
	@DBInLinkedServer NVARCHAR(300)

SET @LinkedServer = '111.222.111.222'
SET @DBInLinkedServer = 'SomeOtherDatabase'

-- Generate create script for any non-system schemas:

SELECT CreateStatement = N'CREATE SCHEMA ' + QUOTENAME(name) + N';', DropStatement = N'DROP SCHEMA ' + QUOTENAME(name) + N';'

EitanBlumin

/************** Find Orphaned Records **************
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
More info: https://eitanblumin.com/2018/11/06/find-and-fix-untrusted-foreign-keys-in-all-databases/
****************************************************/
DECLARE
	@ForeignKeyName SYSNAME = 'FK_MyTable_MyOtherTable'
	, @PrintOnly		BIT			= 0

DECLARE
	@FKId INT,