EitanBlumin

param (
[version] $BuildNumber = "9.00.5324"
)
#Run this script with -ExecutionPolicy Bypass

Install-Module -Name SQLServerUpdatesModule
Import-Module SQLServerUpdatesModule

$ErrorActionPreference = "SilentlyContinue"

EitanBlumin

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

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

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;

EitanBlumin

/*
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

 ----------------------------------------------------------------------------------
 -- 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 (InstancePropertiesGenerateForCompare.sql).
 --
 -- Instructions:
 --		Run InstancePropertiesGenerateForCompare.sql on each server. Save output to a CSV file.
 --		Use this script ( InstancePropertiesComparison.sql ) to load the files into a table, and output any differences
 --		Don't forget to change file paths accordingly.

EitanBlumin

-- 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

-- 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

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

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';'