Skip to content

Instantly share code, notes, and snippets.

Avatar
🕵️‍♂️
Figuring it out

Eitan Blumin EitanBlumin

🕵️‍♂️
Figuring it out
View GitHub Profile
@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 / 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 / CaptureTSQLEvents_XE_Buffer.sql
Last active Jul 15, 2020
Collect T-SQL Events using an Extended Events Buffer
View CaptureTSQLEvents_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
View extended_events_track_failed_logins.sql
CREATE EVENT SESSION [TrackFailedLogins] ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.nt_username,sqlserver.database_id,sqlserver.session_id)
WHERE (([severity]=(20) OR [severity]=(14) OR [severity]=(16))
AND ([error_number]=(18056)
OR [error_number]=(17892)
OR [error_number]=(18061)
OR [error_number]=(18452)
OR [error_number]=(11248)
OR [error_number]=(17806)
@EitanBlumin
EitanBlumin / Shrink_Database_File_in_Specified_Increments.sql
Last active Jul 28, 2020
Shrink a database file in specified increments down to a specific size or percentage of used space
View Shrink_Database_File_in_Specified_Increments.sql
/*
----------------------------------------------------------------------------
Shrink a Database File in Specified Increments
----------------------------------------------------------------------------
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Creation Date: 2020-01-05
Last Update: 2020-07-28
----------------------------------------------------------------------------
Description:
This script uses small intervals to shrink a file (in the current database)
@EitanBlumin
EitanBlumin / TempDB Sizing Check and Remediation.sql
Last active Aug 4, 2020
TempDB Sizing Check and Remediation script
View TempDB Sizing Check and Remediation.sql
/*
-----------------------------------
TempDB Sizing Check and Remediation
-----------------------------------
Author: Eitan Blumin | https://www.eitanblumin.com
Description:
This script makes sure that all TempDB files are equally sized, based on a calculation that takes into consideration
the disk volume where the TempDB files are located.
This check only works when TempDB files are isolated from other databases and exist on their own dedicated volume.
@EitanBlumin
EitanBlumin / helper_script_for_adding_schemabinding_to_scalar_functions.sql
Last active Mar 23, 2020
Helper T-SQL script for adding SCHEMABINDING on scalar functions (checks in ALL databases on the server)
View helper_script_for_adding_schemabinding_to_scalar_functions.sql
/*
Author: Eitan Blumin | https://www.eitanblumin.com
Description:
Helper T-SQL script for adding SCHEMABINDING on scalar functions (checks in ALL databases on the server)
Added support for Azure SQL DB: Performs the same check across schemas instead of across databases
Instructions:
1. Run the script to detect all scalar functions with disabled SCHEMABINDING, that can potentially have it enabled.
View Check Index Fragmentation Based on Page Fullness and Fill Factor.sql
/**************************************************************************
Check Index Fragmentation based on Page Fullness and Fill Factor
***************************************************************************
Author: Eitan Blumin | https://www.eitanblumin.com
Version History:
2020-01-07 First version
Description:
This script was inspired by Erik Darling's blog post here:
https://www.erikdarlingdata.com/2019/10/because-your-index-maintenance-script-is-measuring-the-wrong-thing/
@EitanBlumin
EitanBlumin / Generate recommendations for clustered indexes.sql
Last active Jul 14, 2020
Use existing non-clustered index usage stats, and missing index stats, to generate clustered-index recommendations for heap tables (more info: https://eitanblumin.com/2019/12/30/resolving-tables-without-clustered-indexes-heaps/ )
View Generate recommendations for clustered indexes.sql
-------------------------------------------------------
------ Generate Clustered Index Recommendations -------
-------------------------------------------------------
-- Author: Eitan Blumin | https://www.eitanblumin.com
-- More info: https://eitanblumin.com/2019/12/30/resolving-tables-without-clustered-indexes-heaps/
-------------------------------------------------------
-- Description:
-- ------------
-- This script finds all heap tables, and "guestimates" a clustered index recommendation for each.
-- The script implements the following algorithm:
@EitanBlumin
EitanBlumin / ultimate_compression_savings_estimation_whole_database.sql
Last active May 6, 2020
This ultimate script performs compression savings estimation check for an ENTIRE database, includes comparison between PAGE and ROW compression types, cautionary table size checks, AND it generates the remediation scripts for you! (more info: https://eitanblumin.com/2020/02/18/ultimate-compression-savings-estimation-script-entire-database/ )
View ultimate_compression_savings_estimation_whole_database.sql
----------------------------------------------------------------
-------- Ultimate Compression Savings Estimation Check ---------
----------------------------------------------------------------
-- Author: Eitan Blumin | https://www.eitanblumin.com
-- Create Date: 2019-12-08
-- Last Update: 2020-03-30
-- Source: http://bit.ly/SQLCompressionEstimation
-- Full Link: https://gist.github.com/EitanBlumin/85cf620f7267b234d677f9c3027fb7ce
----------------------------------------------------------------
-- Description:
You can’t perform that action at this time.