Skip to content

Instantly share code, notes, and snippets.

Avatar
🕵️‍♂️
Figuring it out

Eitan Blumin EitanBlumin

🕵️‍♂️
Figuring it out
View GitHub Profile
@EitanBlumin
EitanBlumin / Configure load balancer IP and name for a domain-independent AG.ps1
Created Jan 20, 2021
Script to configure load balancer listener IP and name for a domain-independent Availability Group in a Windows Workgroup
View Configure load balancer IP and name for a domain-independent AG.ps1
<#
.SYNOPSIS
Script to configure load balancer listener IP and name for a domain-independent Availability Group in a Windows Workgroup
Author: Eitan Blumin
Date: 2021-01-20
.DESCRIPTION
This script is adapted from the scripts provided in the following resources:
View Relocate AG Database Files.ps1
############## Setup ##############
$AGName = ''
$AGPrimary = ''
$AGSecondary = ''
# This allows you to process just a subset of databases using the name (wildcards are possible)
$DBNamePattern = 'AdventureW*'
$TargetFolder = 'K:\Data\'
@EitanBlumin
EitanBlumin / SentryOne_CPU_Utilization_Stats_for_all_Sql_Server_Targets.sql
Created Jan 11, 2021
Script to run in the SentryOne database to get CPU utilization min/max/avg/6σ for all targets
View SentryOne_CPU_Utilization_Stats_for_all_Sql_Server_Targets.sql
USE SentryOne
GO
DECLARE
@SiteName NVARCHAR(1000) = NULL
,@SqlServerName NVARCHAR(1000) = NULL
,@End_date DATETIME = NULL
,@Start_date DATETIME = NULL
,@CounterID SMALLINT = 1858 -- CPU %
,@DefaultDaysBack INT = 90
@EitanBlumin
EitanBlumin / Foreign_Key_Hierarchy_Tree.sql
Created Jan 7, 2021
T-SQL script to retrieve the hierarchy tree for a given table, based on foreign key references.
View Foreign_Key_Hierarchy_Tree.sql
/*
Retrieve Foreign Key Hierarchy Tree
===================================
Author: Eitan Blumin | https://eitanblumin.com | https://madeiradata.com
Date: 2021-01-07
Description:
Retrieve the hierarchy tree for a given table,
based on foreign key references.
Use this script to map out your entity relational structure,
see which foreign keys are dependent on a given table,
@EitanBlumin
EitanBlumin / Delete_Batches_From_Table_By_DateTime_Column.sql
Created Dec 26, 2020
Generic script to delete time based data in batches
View Delete_Batches_From_Table_By_DateTime_Column.sql
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
EitanBlumin / R_myReadXcelProc.sql
Last active Dec 25, 2020
R stored procedure to load Excel files, by Matteo Lorini
View R_myReadXcelProc.sql
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
EitanBlumin / SQLDump_File_Examine.sql
Created Dec 8, 2020
T-SQL script to read the contents of a SQLDump file's txt file
View SQLDump_File_Examine.sql
/*
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
EitanBlumin / Powershell_Template_with_Transcript.ps1
Last active Dec 7, 2020
Template Powershell script with built-in transcript log management
View Powershell_Template_with_Transcript.ps1
# 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
EitanBlumin / Redundant_Indexes.sql
Created Dec 6, 2020
T-SQL script to find redundant indexes within the current database, also outputs index usage stats and drop commands
View Redundant_Indexes.sql
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
EitanBlumin / Invalid_owner_for_system_schema_role_or_database.sql
Created Nov 25, 2020
TSQL script to check for invalid owners of system Roles, Schemas, or Databases
View Invalid_owner_for_system_schema_role_or_database.sql
/*
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.