Skip to content

Instantly share code, notes, and snippets.

🕵️‍♂️
Figuring it out

Eitan Blumin EitanBlumin

🕵️‍♂️
Figuring it out
View GitHub Profile
@EitanBlumin
EitanBlumin / Check_SQLServerUpdates.ps1
Created May 7, 2018
Check For SQL Server Updates Using SQLServerUpdatesModule
View Check_SQLServerUpdates.ps1
param (
[version] $BuildNumber = "9.00.5324"
)
#Run this script with -ExecutionPolicy Bypass
Install-Module -Name SQLServerUpdatesModule
Import-Module SQLServerUpdatesModule
$ErrorActionPreference = "SilentlyContinue"
@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
@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"
View check_untrusted_foreign_keys.sql
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
CREATE TABLE #tmp (DBName SYSNAME, SchemaName SYSNAME, TableName SYSNAME, FullTableName AS QUOTENAME(SchemaName) + N'.' + QUOTENAME(TableName), UntrustedObject SYSNAME);
INSERT INTO #tmp(DBName, SchemaName, TableName, UntrustedObject)
EXEC sp_MSforeachdb 'IF EXISTS (SELECT * FROM sys.databases WHERE state_desc = ''ONLINE'' AND name = ''?'' AND DATABASEPROPERTYEX(''?'', ''Updateability'') = ''READ_WRITE'')
BEGIN
USE [?];
SELECT ''?'', OBJECT_SCHEMA_NAME(parent_object_id), OBJECT_NAME(parent_object_id), [name]
FROM [?].sys.foreign_keys
WHERE is_not_trusted = 1 AND is_not_for_replication = 0 AND is_disabled = 0;
View realign_identity_to_max_value.sql
/*
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Date: November, 2018
Description:
Re-align Identity Last Value to Actual Max Value
More info: https://eitanblumin.com/2018/11/06/re-align-identity-last-value-to-actual-max-value/
*/
DECLARE @CurrTable SYSNAME, @CurrCol SYSNAME, @LastValue BIGINT
DECLARE @CMD NVARCHAR(MAX), @Result NVARCHAR(MAX)
@EitanBlumin
EitanBlumin / CompareInstanceProperties.sql
Last active May 31, 2020
Compare SQL Server Instance Properties
View CompareInstanceProperties.sql
----------------------------------------------------------------------------------
-- Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
-- Date: 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
@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 / 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 / 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 / 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';'
You can’t perform that action at this time.