EitanBlumin

-- Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
-- Date: 2020-05-31
-- Last Update: 2020-07-15
-- Description: Collect T-SQL Error Events using an Extended Events Buffer
--		The script automatically detects whether you're in an Azure SQL DB, or a regular SQL Server instance.

SET NOCOUNT ON;

DECLARE
  @SourceLinkedServer SYSNAME

EitanBlumin

SELECT UndoCompressionCommand = N'ALTER INDEX ' + QUOTENAME(ix.name) + ' ON '
+ QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name)
+ ' REBUILD WITH(DATA_COMPRESSION=NONE,ONLINE=ON,SORT_IN_TEMPDB=ON);'
FROM sys.partitions AS p
INNER JOIN sys.indexes AS ix
ON p.object_id = ix.object_id
AND p.index_id = ix.index_id
AND data_compression <> 0
INNER JOIN sys.tables AS t
ON t.object_id = ix.object_id

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:

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

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

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

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

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

-- Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
-- Description: Get a single row per each high severity error from the SQL Server Error Log
DECLARE
  @SampleTime DATETIME = DATEADD(MINUTE,-30,SYSDATETIME())
, @MinimumSeverity INT = 17
, @MaximumSeverity INT = 25;

IF OBJECT_ID(N'tempdb..#errors') IS NOT NULL   
 DROP TABLE #errors;

EitanBlumin

/*
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Date: 2020-01-28
Description:
This script generates commands to implement a minimal standardization of all database files in the instance.
List of implemented standards:
	1. Files must not be allowed to have percentage growth (defaults to 1GB growth instead as a replacement)
	2. Files must all have UNLIMITED max size
	3. Log files must be at least 64MB in size
	4. Log file auto-growth must be in power multiples of 2 between 64MB and 2048MB (i.e. 64,128,256,512,1024,2048) (defaults to 1GB growth instead as a replacement)