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 / detect table subsets.sql
Created Apr 3, 2020
Script to detect table subsets based on foreign key dependencies
View detect table subsets.sql
DROP TABLE IF EXISTS #Tree;
CREATE TABLE #Tree
(
object_id INT PRIMARY KEY WITH(IGNORE_DUP_KEY=ON),
subset_group_id INT,
referenced_object_id INT NULL
);
-- Insert 1st level tables
INSERT INTO #Tree
@EitanBlumin
EitanBlumin / drop jobs and jobs_internal schemas.sql
Created Apr 3, 2020
Generate commands to drop the "jobs" and "jobs_internal" schemas and all of their objects
View drop jobs and jobs_internal schemas.sql
SELECT
'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id))
+ ' DROP CONSTRAINT ' + QUOTENAME(name)
FROM sys.foreign_keys
WHERE schema_id IN ( SCHEMA_ID('jobs'), SCHEMA_ID('jobs_internal') )
ORDER BY
CASE schema_id WHEN SCHEMA_ID('jobs') THEN 1 ELSE 2 END ASC
SELECT
'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)
@EitanBlumin
EitanBlumin / investigate AlwaysOn_health extended events.sql
Created Apr 1, 2020
Investigate AlwaysOn_health extended events using T-SQL
View investigate AlwaysOn_health extended events.sql
DECLARE
@FromDate DATETIME2(3) = NULL
,@ToDate DATETIME2(3) = NULL
DECLARE @FileName NVARCHAR(4000)
SELECT @FileName = target_data.value('(EventFileTarget/File/@name)[1]','nvarchar(4000)')
FROM (SELECT CAST(target_data AS XML) target_data FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address
WHERE s.name = N'AlwaysOn_health') ft
@EitanBlumin
EitanBlumin / Grow a database file in specified increments.sql
Last active Apr 8, 2020
Grow a database file in specified increments up to a specific size or percentage of used space
View Grow a database file in specified increments.sql
/*
----------------------------------------------------------------------------
Grow a Database File in Specified Increments
----------------------------------------------------------------------------
Author: Eitan Blumin | https://www.eitanblumin.com
Creation Date: 2020-03-30
----------------------------------------------------------------------------
Description:
This script uses small intervals to grow a file (in the current database)
@EitanBlumin
EitanBlumin / CHECKDB on non-readable AG secondaries.sql
Last active Apr 2, 2020
Run DBCC CHECKDB on all databases which are either standalone, or SECONDARY in AG. Supports non-readable secondaries by creating DB snapshots.
View CHECKDB on non-readable AG secondaries.sql
DECLARE @CurrDB SYSNAME, @IsInAG BIT, @CMD NVARCHAR(MAX);
-- Find all databases which are either standalone, or SECONDARY in AG
DECLARE dbs CURSOR
LOCAL FAST_FORWARD
FOR
SELECT [name], CASE WHEN replica_id IS NULL THEN 0 ELSE 1 END
FROM sys.databases
WHERE database_id <> 2
AND state_desc = 'ONLINE'
@EitanBlumin
EitanBlumin / ReNumber Identity Column.sql
Last active Apr 2, 2020
Re-number the identity column for a table that has very large number gaps
View ReNumber Identity Column.sql
/*
Re-Number Identity Column
=================================
Author: Eitan Blumin | https://www.eitanblumin.com
Create Date: 2020-03-24
Description:
Use this script to re-number a table with an identity column, which has very large number gaps.
The specified parameter @ChunkSize must be smaller than the current minimum value
in the table.
*/
View visualize_page_allocation_compact.sql
SELECT
databse_name = DB_NAME()
, file_name
, check_file_total_size = file_total_size
, check_file_total_used_space = file_total_used_space
, check_file_total_unused_pages = file_total_unused_pages
, agg_file_total_reserved_pages = file_total_reserved_pages
, agg_file_total_unused_pages = SUM(pt.consecutive_unused_pages) OVER (PARTITION BY file_id)
, pt.*
@EitanBlumin
EitanBlumin / Find SHRINK sessions with lock info.sql
Last active Mar 25, 2020
SQL queries to troubleshoot a long-running DBCC SHRINK operation
View Find SHRINK sessions with lock info.sql
/*
Author: Eitan Blumin | https://www.eitanblumin.com
Create Date: 2020-03-18
Description:
This script will detect currently running sessions in your database which are running DBCC SHRINK commands.
It will also output the name of any tables and indexes the session is currently locking.
Use this query to find out what causes a SHRINK to run for too long.
You may need to run it multiple times to "catch" the relevant info.
*/
@EitanBlumin
EitanBlumin / Collect T-SQL Events using an Extended Events Buffer.sql
Last active Apr 7, 2020
Collect T-SQL Events using an Extended Events Buffer
View Collect T-SQL Events using an Extended Events Buffer.sql
-- Author: Eitan Blumin | https://www.eitanblumin.com
-- Date: 2020-02-26
-- Last Update: 2020-04-07
-- Description: Collect T-SQL Events using an Extended Events Buffer
SET NOCOUNT ON;
DECLARE
@SourceLinkedServer SYSNAME
, @MinimumDurationMilliSeconds BIGINT
@EitanBlumin
EitanBlumin / generate_rename_system_named_default_constraints.sql
Created Feb 26, 2020
Generate rename commands for all system-named default constraints
View generate_rename_system_named_default_constraints.sql
-- Author: Eitan Blumin | https://www.eitanblumin.com
-- Date: 2020-02-26
-- Description: This is a query to generate rename commands for all system-named default constraints within the current database.
-- The constraints are renamed based on convention of "DF_{TableName}_{ColumnName}"
-- Simply run this query and then copy & paste the entire remediationCommand column to get the script(s).
SELECT
schemaName = sch.[name],
tableName = tab.[name],
columnName = col.[name],
You can’t perform that action at this time.