Skip to content

Instantly share code, notes, and snippets.

🕵
Figuring it out

Eitan Blumin EitanBlumin

🕵
Figuring it out
Block or report user

Report or block EitanBlumin

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
View Data Driven Alternative Email and File Share.sql
-- Based on script by Jason Selburg
-- https://www.sqlservercentral.com/Forums/Topic279460-150-1.aspx
-- http://www.sqlservercentral.com/scripts/Miscellaneous/31733/
USE ReportServer
GO
IF OBJECT_ID(N'[dbo].[data_driven_subscription]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[data_driven_subscription]
GO
@EitanBlumin
EitanBlumin / Review SQL Instance Best Practices.sql
Last active Feb 13, 2019
Condensed SQL Server Checkup of most common and impactful best practices
View Review SQL Instance Best Practices.sql
DECLARE
@NumOfMinutesBackToCheck INT = 360,
@MinutesBackToCheck INT = 360,
@DaysBackToCheck INT = 10,
@MinAdHocSizeInMB INT = 200,
@MinAdHocPercent INT = 25,
@FreespaceMinimumMB INT = 1024,
@FreespaceMinimumPercent INT = 10,
@UnsentLogThresholdKB INT = 2048,
@UnrestoredLogThresholdKB INT = 2048,
@EitanBlumin
EitanBlumin / Online Index Operations without Enterprise.sql
Last active Oct 8, 2018
Generate Script to allow performing ONLINE index operations and heavy changes on huge tables, without needing Enterprise edition of SQL Server
View Online Index Operations without Enterprise.sql
/***********************************************************************************
Copyright: Eitan Blumin (c) 2018
https://gist.github.com/EitanBlumin/79222fc2be5163cec828d0a69270a0ab
***********************************************************************************/
GO
IF OBJECT_ID('tempdb..#PrintMax', 'P') IS NOT NULL DROP PROCEDURE #PrintMax;
GO
-----------------------------------------------------------------------------------------------
@EitanBlumin
EitanBlumin / Generate Synonyms Creation Script.sql
Created Aug 21, 2018
Generate Synonyms Creation Script for creating a "shell" database
View Generate Synonyms Creation Script.sql
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
EitanBlumin / ChangeJobStatusBasedOnHADR.sql
Last active Oct 17, 2019
This procedure detects whether the specified DB is primary or secondary in DB Mirroring or Availability Groups, and disables or enables a list of jobs accordingly. Run this from a job in both servers. More info here: https://eitanblumin.com/2018/11/06/automatically-enable-or-disable-jobs-based-on-hadr-role/
View ChangeJobStatusBasedOnHADR.sql
USE [SomeNonHadrDB]
GO
IF OBJECT_ID('ChangeJobStatusBasedOnHADR', 'P') IS NOT NULL DROP PROCEDURE ChangeJobStatusBasedOnHADR
GO
/*
--Sample usage:
EXEC ChangeJobStatusBasedOnHADR @DBName = 'DB_to_use_as_primary_indicator'
*/
CREATE PROCEDURE ChangeJobStatusBasedOnHADR
@DBName SYSNAME = NULL
@EitanBlumin
EitanBlumin / Calculate Max Memory for SQL.sql
Created Jul 16, 2018
Calculate Max Memory for SQL Server Instance
View Calculate Max Memory for SQL.sql
-- 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
EitanBlumin / QueryStore_Health Extended Event Session.sql
Created Jul 9, 2018
QueryStore_Health Extended Event Session
View QueryStore_Health Extended Event Session.sql
-- Get some metadata about query_store extended events
/*
select *
from sys.dm_xe_object_columns
where object_name like '%query_store%'
and name not in ('UUID','VERSION','CHANNEL','KEYWORD')
*/
/*
query_store_persist_on_shutdown_failed
@EitanBlumin
EitanBlumin / CompareInstanceProperties.sql
Last active Dec 23, 2018
Compare SQL Server Instance Properties
View CompareInstanceProperties.sql
----------------------------------------------------------------------------------
-- Created: by Eitan Blumin 26/06/18
-- Description:
-- Compares server level objects and definitions as outputted by the first script (GenerateInstancePropertiesForCompare.sql).
--
-- Instructions:
-- Run GenerateInstancePropertiesForCompare.sql on "First" server. Save output to a CSV file.
-- Run GenerateInstancePropertiesForCompare.sql on "Second" server. Save output to a CSV file.
-- Use this script ( CompareInstanceProperties.sql ) to load the files into a table, and output any differences
-- Don't forget to change file paths and server names accordingly.
@EitanBlumin
EitanBlumin / realign_identity_to_max_value.sql
Last active Feb 26, 2019
Re-align Identity Last Value to Actual Max Value
View realign_identity_to_max_value.sql
DECLARE @CurrTable SYSNAME, @CurrCol SYSNAME, @LastValue BIGINT
DECLARE @CMD NVARCHAR(MAX), @Result NVARCHAR(MAX)
DECLARE Cur CURSOR
LOCAL FAST_FORWARD
FOR
SELECT DISTINCT QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + '.' + QUOTENAME(OBJECT_NAME(t.object_id)), c.name, CONVERT(int, c.last_value)
FROM sys.identity_columns AS c
INNER JOIN sys.tables AS t
ON c.object_id = t.object_id
@EitanBlumin
EitanBlumin / check_untrusted_foreign_keys.sql
Last active Oct 14, 2018
Find and check untrusted Foreign Keys in all databases
View check_untrusted_foreign_keys.sql
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
CREATE TABLE #tmp (DBName SYSNAME, TableName SYSNAME, UntrustedObject NVARCHAR(1000));
EXEC sp_MSforeachdb '
INSERT INTO #tmp
SELECT ''?'', QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id, DB_ID(''?''))) + ''.'' + QUOTENAME(OBJECT_NAME(parent_object_id, DB_ID(''?''))), + QUOTENAME(name)
FROM [?].sys.foreign_keys
WHERE is_not_trusted = 1 AND is_not_for_replication = 0 AND is_disabled = 0'
SELECT DBName, TableName, UntrustedObject, CommandToRemediate = N'USE ' + QUOTENAME(DBName) + N'; ALTER TABLE ' + TableName + N' WITH CHECK CHECK CONSTRAINT ' + UntrustedObject
FROM #tmp
You can’t perform that action at this time.