EitanBlumin

DECLARE
	@DatabaseName		SYSNAME		= 'MyDB',
	@TableName		SYSNAME		= 'MyTable',
	@DateTimeColumnName	SYSNAME		= 'MyColumn',
	@ThresholdDateTime	DATETIME	= DATEADD(DAY, -14, GETDATE()),
	@BatchSize		INT		= 10000,
	@SleepBetweenBatches	VARCHAR(17)	= '00:00:00.6'

SET NOCOUNT ON;

EitanBlumin

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
/*
Based on sample by Matteo Lorini:
https://www.mssqltips.com/sqlservertip/6622/stored-procedure-in-sql-server-with-r-code/

The readxl package needs to be installed first:

EitanBlumin

/*
Read contents of a SQLDump file's txt file
=============================================
Author: Eitan Blumin | https://www.madeiradata.com
Date: 2020-12-08
*/
SET NOCOUNT, ARITHABORT, XACT_ABORT ON;
DECLARE @FilePath NVARCHAR(4000), @CMD NVARCHAR(MAX)

-- Use below to read the contents of latest memory dump file (mdmp) generated by the current instance:

EitanBlumin

# when creating a scheduled task to run such scripts, use the following structure example:
# powershell.exe -NoProfile -ExecutionPolicy Bypass -File "C:\Madeira\Powershell_Template_with_Transcript.ps1"
Param
(
[string]$logFileFolderPath = "C:\Madeira\log",
[string]$logFilePrefix = "my_ps_script_",
[string]$logFileDateFormat = "yyyyMMdd_HHmmss",
[int]$logFileRetentionDays = 30
)
Process {

EitanBlumin

SET NOCOUNT ON;
DECLARE @MinimumRowsInTable INT = 200000;
IF OBJECT_ID('tempdb..#FindOnThisDB') IS NOT NULL DROP TABLE #FindOnThisDB;
;WITH Indexes AS
(
select
sets.schema_id,
sets.table_o_id,
sets.key_column_list,
sets.include_column_list,

EitanBlumin

/*
Invalid owner for a system Role, Schema, or Database
====================================================
Author: Eitan Blumin | Madeira Data Solutions | https://www.madeiradata.com
Date: 2020-11-25
Description:

System roles and schemas must have specific owning users or roles.
 
For example, all system database roles such as db_owner, db_datawriter, db_datareader, etc. must be owned by dbo.

EitanBlumin

/******************************************************************
		Parse SQL Vulnerability Assessment Tool Results
		***********************************************
Author: Eitan Blumin | https://www.eitanblumin.com

Description:
	Use this script to parse a Vulnerability Assessment Tool
	results file into a relational structure and save in an HTML page.
	This will output the T-SQL queries used by VAT behind the scenes
	and their respective meta-data, as displayed in the VAT.

EitanBlumin

/*
Check that the configured value for MAXDOP is in the recommended range, as described in this KB article: 
https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi

If @WhatIf = 0 then MAXDOP will automatically be changed to the recommended setting.
*/
-- change this to 1 to only display findings without actually changing the config:
DECLARE @WhatIf BIT = 0;

--------------------------------------

EitanBlumin

/*
=================================================
Reproduce an access violation error in SQL Server
=================================================
The following script reproduces an Access Violation error
caused by a parallelism plan involving specific system table functions.
The error occurses ONLY with parallel execution plans.

Discovered by Eitan Blumin and Nathan Lifshes on 2020-09-06
The error has been reproduced in multiple SQL Server versions that were tested:

EitanBlumin

SELECT SiteName, ReplicaName
	, NumberOfAvailabilityGroups = COUNT(DISTINCT AGName)
	, NumberOfPrimaries = COUNT(DISTINCT PrimaryReplica)
FROM
(
SELECT DISTINCT
	ES.ObjectName AS ReplicaName, S.Name AS SiteName
	, AG.Name AS AGName
	, AG.PrimaryReplica
FROM [SentryOne].[AlwaysOn].[AvailabilityGroup] AS AG