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
@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 Nov 14, 2018
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.
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
@EitanBlumin
EitanBlumin / zendesk_change_ticket_status.ps1
Created May 7, 2018
Change Zendesk Ticket Status Using Powershell
View zendesk_change_ticket_status.ps1
param
(
[int] $ticketid,
[validateset ("new","open","pending","solved","closed","delete","same")] [string] $newstatus,
[string] $admincomment = ""
)
# Global Zendesk Settings:
$global:zendesk_address = "https://yourcompany.zendesk.com"
@EitanBlumin
EitanBlumin / DeadlockTrace_Info.sql
Last active Jun 25, 2018
Get Deadlock Trace Info from Default Trace
View DeadlockTrace_Info.sql
declare @filename nvarchar(200)
select @filename = convert(nvarchar(200), value)
from ::fn_trace_getinfo(null)
where property = 2
and convert(nvarchar(200), value) LIKE '%deadlocks%'
PRINT @filename
select StartTime, convert(xml, TextData) AS Deadlock_Graph, ServerName
You can’t perform that action at this time.