Skip to content

Instantly share code, notes, and snippets.

🕵️‍♂️
Figuring it out

Eitan Blumin EitanBlumin

🕵️‍♂️
Figuring it out
View GitHub Profile
@EitanBlumin
EitanBlumin / GetTempDBAvailableSpace.sql
Last active May 18, 2020
Stored procedure to inspect and return the current available space in TempDB, including space for auto-growth
View GetTempDBAvailableSpace.sql
/*
Copyright 2020 @EitanBlumin, https://eitanblumin.com
Source: https://bit.ly/TempDBFreeSpace
Full URL: https://gist.github.com/EitanBlumin/afed2587e89e260698c4753fcc5d1917
License: MIT (https://opensource.org/licenses/MIT)
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
@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
Last active Apr 25, 2020
Investigate AlwaysOn_health extended events using T-SQL
View investigate AlwaysOn_health extended events.sql
DECLARE
@FromDate DATETIME2(3) = DATEADD(hh,-24,GETDATE())
,@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 May 31, 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
/*
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Date: March, 2020
Description:
Run DBCC CHECKDB on all databases which are either standalone, or SECONDARY in AG.
Supports non-readable secondaries by creating DB snapshots.
*/
DECLARE @CurrDB SYSNAME, @IsInAG BIT, @CMD NVARCHAR(MAX);
-- Find all databases which are either standalone, or SECONDARY in AG
@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 Jun 22, 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
Last Update: 2020-06-22
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 / TSQLEventsCollector_XE_Buffer.sql
Last active Jun 7, 2020
Collect T-SQL Events using an Extended Events Buffer
View TSQLEventsCollector_XE_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
You can’t perform that action at this time.