EitanBlumin

-- 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

/*
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

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

/*
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.
*/

EitanBlumin

/*
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

/*
----------------------------------------------------------------------------
		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

/*
AlwaysOn Availability Group Error Events
========================================
Author: Eitan Blumin
Date: 2020-05-31
This alert check the contents of the AlwaysOn_Health extended events session for data suspension, role changes, and other errors.
 
For more info:
https://docs.microsoft.com/sql/database-engine/availability-groups/windows/always-on-extended-events
*/

EitanBlumin

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

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

/*
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: