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 / Find_SQL_TCP_Port.sql
Last active May 7, 2018
Find SQL Server Instance TCP Port In Use
View Find_SQL_TCP_Port.sql
USE master
GO
-- Using SQL Error Logs:
xp_readerrorlog 0, 1, N'Server is listening on', N'any', NULL, NULL, N'asc'
-- will also return records for DB Mirroring endpoints
-- also, this won't work if error log was cycled
GO
-- Using currently connected connections:
@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"
@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 / 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 / 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 / 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 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
You can’t perform that action at this time.