Skip to content

Instantly share code, notes, and snippets.

View EitanBlumin's full-sized avatar
🕵️‍♂️
Figuring it out

Eitan Blumin EitanBlumin

🕵️‍♂️
Figuring it out
62 followers · 10 following
View GitHub Profile
@EitanBlumin
EitanBlumin / Query window 1.sql
Last active November 3, 2025 06:29
Brute force database out of SINGLE_USER mode back into MULTI_USER mode
-- run this in query window 1:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET DEADLOCK_PRIORITY HIGH;
GO
DECLARE @CMD nvarchar(max) = ''
SELECT @CMD = CONCAT(@CMD, CHAR(10), 'KILL ', session_id)
FROM sys.dm_exec_requests
WHERE database_id = DB_ID('MyDB') -- replace MyDB with your DB name
@EitanBlumin
EitanBlumin / Misconfigured jobs with retries and output file.sql
Last active July 4, 2025 15:23
Detect misconfigured jobs with retries and output file
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT
Msg = CONCAT(N'Job "', j.name, N'" step ', s.step_id, N' ("', s.step_name,N'") ',
, CARE WHEN s.retry_attempts > 0 THEN N'has retries enabled but ' ELSE N'' END
, CASE WHEN (s.flags & 2) = 0 THEN N'Append is disabled (which may cause loss of info) ' ELSE N'Append is enabled ' END
, CASE WHEN
s.output_file_name NOT LIKE '%(TIME)%'
AND s.output_file_name NOT LIKE '%(STRTTM)%'
AND s.output_file_name NOT LIKE '%(STRTDT)%'
@EitanBlumin
EitanBlumin / DatabaseIntegrityCheck - Allocation and Catalog Checks Only.sql
Last active December 12, 2024 05:39
DatabaseIntegrityCheck - Allocation and Catalog Checks Only
DECLARE @MaxEndTime datetime = DATEADD(HOUR, 2, GETDATE())
DECLARE @TimeLimitSeconds int;
SET @TimeLimitSeconds = DATEDIFF(second, GETDATE(), @MaxEndTime)
EXEC dbo.DatabaseIntegrityCheck
@Databases = 'ALL_DATABASES',
@CheckCommands = 'CHECKALLOC,CHECKCATALOG',
--@PhysicalOnly = 'Y',
@TimeLimit = @TimeLimitSeconds,
@EitanBlumin
EitanBlumin / DatabaseIntegrityCheck - Incremental Object-Level Checks.sql
Last active October 28, 2024 14:01
DatabaseIntegrityCheck - Incremental Object-Level Checks
DECLARE @EndTime datetime = DATEADD(hour, 2, GETDATE()) -- Adjust the time limit as needed
DECLARE @OlaHallengrenDBName sysname = DB_NAME() -- This script must run within the context of the database where Ola's maintenance solution was installed
DECLARE @DBName sysname, @ObjNameFull nvarchar(4000), @ObjNameLean sysname, @SchName sysname
DECLARE @CheckTime datetime, @LastCheckDate datetime, @ObjType sysname
IF OBJECT_ID('tempdb..#Objects') IS NOT NULL DROP TABLE #Objects;
CREATE TABLE #Objects
(
DBName sysname,
@EitanBlumin
EitanBlumin / ALTER TABLE SWITCH demo errors 4907 and 4908.sql
Last active August 5, 2023 08:13
ALTER TABLE SWITCH demo for errors 4907 and 4908
/*
https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16#switch--partition-source_partition_number_expression--to--schema_name--target_table--partition-target_partition_number_expression-
*/
SET NOCOUNT ON;
GO
CREATE PARTITION FUNCTION PF1 (int) AS RANGE RIGHT FOR VALUES (0, 100)
CREATE PARTITION SCHEME PS1 AS PARTITION PF1 ALL TO ([PRIMARY]);
GO
CREATE PARTITION FUNCTION PF2 (int) AS RANGE RIGHT FOR VALUES (0, 100, 200)
CREATE PARTITION SCHEME PS2 AS PARTITION PF2 ALL TO ([PRIMARY]);
@EitanBlumin
EitanBlumin / system_named_constraints_demo.sql
Last active March 12, 2023 20:36
Demo creation of tables with system-named constraints
DROP TABLE IF EXISTS [dbo].[TestTable2];
DROP TABLE IF EXISTS [dbo].[TestTable1];
DROP TABLE IF EXISTS dbo.TestEdgeTable;
DROP TABLE IF EXISTS dbo.TestNodeTable1;
DROP TABLE IF EXISTS dbo.TestNodeTable2;
DROP TABLE IF EXISTS dbo.TestNodeTable3;
GO
IF SCHEMA_ID('EitanTest') IS NOT NULL DROP SCHEMA EitanTest;
GO
CREATE SCHEMA EitanTest AUTHORIZATION dbo;
@EitanBlumin
EitanBlumin / SQLDatabaseYAMLBuildConfiguration.yml
Last active May 8, 2022 08:20 — forked from jpvelasco/SQLDatabaseYAMLBuildConfiguration.yml
SQL YAML Build Configuration for Azure Pipelines
# SQL Database Project Build Configuration
trigger:
- master
pool:
vmImage: 'VS2017-Win2016'
variables:
solution: '**/*.sln'
@EitanBlumin
EitanBlumin / SQLSentry_Heartbeat_Monitoring.sql
Last active April 26, 2022 13:31
Example job command to implement self-monitoring for SQL Sentry monitoring service hearbeats
-- Run this in the right SQL Sentry database
--USE [SentryOne];
--USE [SQLSentry];
GO
IF OBJECT_ID('[dbo].[heartbeat_log]') IS NULL
BEGIN
CREATE TABLE [dbo].[heartbeat_log](
[servername] [nvarchar](300) CONSTRAINT PK_Heartbeat_Log PRIMARY KEY CLUSTERED WITH(IGNORE_DUP_KEY=ON,DATA_COMPRESSION=PAGE),
[heartbeatdate] [datetime] NULL,
[ActualHeartbeatDate] [datetime] NULL
@EitanBlumin
EitanBlumin / Low PAGE Compression Success Rates.sql
Last active January 16, 2022 09:53
Check for Low PAGE Compression Success Rates
/*
Check for low PAGE compression success rates
============================================
Author: Eitan Blumin
Date: 2022-01-13
Based on blog post by Paul Randal:
https://www.sqlskills.com/blogs/paul/the-curious-case-of-tracking-page-compression-success-rates/
*/
DECLARE
/* threshold parameters: */
@EitanBlumin
EitanBlumin / Hypothetical Indexes - Example Usage.sql
Last active March 26, 2024 06:31
Hypothetical Indexes - Example Usage script
SET SHOWPLAN_XML ON;
GO
/* TODO: Add your test query here to get its estimated plan WITHOUT the hypothetical indexes */
GO
SET SHOWPLAN_XML OFF;
GO