Eitan Blumin EitanBlumin
🕵️♂️
EitanBlumin
/ GeneratePeriods_Inline.sql
Last active
November 16, 2021 08:57
Table Function to generate periods for time series, based on an end date, period type, and number of periods back
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| Author: Eitan Blumin (t: @EitanBlumin | b: https://eitanblumin.com) | |
| Date Created: 2013-09-01 | |
| Last Update: 2020-07-28 | |
| Description: | |
| CTE-based Inline Table Function to generate periods for time series, based on an end date, period type, and number of periods back. | |
| Supported period types: | |
| MI - Minute | |
| H - Hour |
sql-williamd
/ Relocate AG Database Files.ps1
Last active
January 26, 2023 11:41
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ############## Setup ############## | |
| $AGName = '' | |
| $AGPrimary = '' | |
| $AGSecondary = '' | |
| # This allows you to process just a subset of databases using the name (wildcards are possible) | |
| $DBNamePattern = 'AdventureW*' | |
| $TargetFolder = 'K:\Data\' |
EitanBlumin
/ CHECKDB on non-readable AG secondaries.sql
Last active
September 3, 2020 00:52
Run DBCC CHECKDB on all databases which are either standalone, or SECONDARY in AG. Supports non-readable secondaries by creating DB snapshots.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| 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
/ Find SHRINK sessions with lock info.sql
Last active
July 8, 2023 06:32
SQL queries to troubleshoot a long-running DBCC SHRINK operation
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| 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. | |
| Optionally, set @RunUntilCaughtLockInfo to 1 to continuously run until a session with object lock info was caught. |
EitanBlumin
/ CaptureTSQLEvents_XE_Buffer.sql
Last active
December 5, 2021 12:14
Collect T-SQL Events using an Extended Events Buffer
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com) | |
| -- Date: 2020-05-31 | |
| -- Last Update: 2021-04-22 | |
| -- Description: Collect T-SQL Events using an Extended Events Buffer | |
| SET NOCOUNT ON; | |
| DECLARE | |
| @SourceLinkedServer SYSNAME | |
| , @MinimumDurationMilliSeconds BIGINT |
EitanBlumin
/ extended_events_track_failed_logins.sql
Last active
January 12, 2022 21:27
Extended event session to track all failed logins with full details (more info: https://eitanblumin.com/2020/03/09/finding-details-missing-sql-server-failed-logins-audit/ )
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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
/ Shrink_Database_File_in_Specified_Increments.sql
Last active
September 3, 2021 14:46
Shrink a database file in specified increments down to a specific size or percentage of used space
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| ---------------------------------------------------------------------------- | |
| Shrink a Database File in Specified Increments | |
| ---------------------------------------------------------------------------- | |
| Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com) | |
| Creation Date: 2020-01-05 | |
| Last Update: 2020-08-23 | |
| ---------------------------------------------------------------------------- | |
| Description: | |
| This script uses small intervals to shrink a file (in the current database) |
EitanBlumin
/ TempDB Sizing Check and Remediation.sql
Last active
July 12, 2021 13:41
TempDB Sizing Check and Remediation script
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| ----------------------------------- | |
| 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
/ helper_script_for_adding_schemabinding_to_scalar_functions.sql
Last active
December 24, 2020 12:06
Helper T-SQL script for adding SCHEMABINDING on scalar functions (checks in ALL databases on the server)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| 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. |
EitanBlumin
/ Check_Index_Fragmentation_Based_on_Page_Fullness_and_Fill_Factor.sql
Last active
July 13, 2023 15:14
Check Index Fragmentation Based on Page Fullness and Fill Factor. Inspired by: https://www.erikdarlingdata.com/2019/10/because-your-index-maintenance-script-is-measuring-the-wrong-thing/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /************************************************************************** | |
| Check Index Fragmentation based on Page Fullness and Fill Factor | |
| *************************************************************************** | |
| Author: Eitan Blumin | https://www.eitanblumin.com | |
| Version History: | |
| 2020-10-20 Added @MaxDOP parameter, and better comments & indentation | |
| 2020-01-07 First version | |
| Description: | |
| This script was inspired by Erik Darling's blog post here: |
NewerOlder