EitanBlumin

/*
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Date: 2020-05-31
Description:
This script iterates through every populated full-text catalog in every accessible database and recycles its crawl log.
Recommended to run this script as a weekly/monthly scheduled maintenance job.

More info:
https://www.sqlskills.com/blogs/jonathan/recycle-fulltext-catalog-log-files/
*/

EitanBlumin

/*
Author: Eitan Blumin | @EitanBlumin, https://www.eitanblumin.com
Create Date: 2016-06-03
Last Update: 2020-05-19
Description:
This procedure creates a partitioned view on top of identically-named tables that exist in multiple databases.

Parameters:

@DBNamePattern		- Database name pattern to use for filtering the relevant databases

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

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

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

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