Please find below a collection of the scripts and tools I have done up to manage the SQL Server and it's databases.

Database Server Configuration

Show Advanced Options

To enable Advanced Options on the server, run this script:

----------------------------------------------------------------------------------------------------------------------------------------------------
 
-- DATABASE ADMINISTRATION SCRIPTS - TURN ON ADVANCED OPTIONS
-- Created By: Matthew Senz
-- Description: Turns on Advanced Options
-- Website: http://help.senzdigital.com
 
----------------------------------------------------------------------------------------------------------------------------------------------------
 
-- Declare Variables and settings
SET NOCOUNT ON;
 
-- Configure Setting
EXEC sp_configure 'show advanced options',1;
RECONFIGURE;
 
PRINT 'Process has completed successfully';
----------------------------------------------------------------------------------------------------------------------------------------------------

Detect and Optimise for Ad-Hoc Workloads

This script evaluates and determines if enabling or disabling ad-hoc workloads will improve overall performance

----------------------------------------------------------------------------------------------------------------------------------------------------
 
-- DATABASE ADMINISTRATION SCRIPTS - DETECT AND SET THE AD-HOC WORKLOADS SETTING
-- Created By: Matthew Senz
-- Description: Detects and sets the best setting for Ad-Hoc Workloads
-- Website: http://help.senzdigital.com
 
----------------------------------------------------------------------------------------------------------------------------------------------------
 
 -- Declare Variables and settings
SET NOCOUNT ON;
DECLARE @AdHocSizeInMB DECIMAL(14, 2);
DECLARE @TotalSizeInMB DECIMAL(14, 2);
DECLARE @ObjType NVARCHAR(34);
DECLARE @Optimise BIT;

-- Reconfigure the optimise for ad-hoc workloads setting
IF EXISTS (
	SELECT [value] FROM [sys].[configurations] WHERE NAME = 'optimize for ad hoc workloads'
)
BEGIN
	SELECT	@AdHocSizeInMB = SUM(CAST((CASE WHEN usecounts = 1 AND LOWER(objtype) = 'adhoc' THEN size_in_bytes ELSE 0 END) AS DECIMAL(14, 2))) / 1048576,
			@TotalSizeInMB = SUM(CAST(size_in_bytes AS DECIMAL(14, 2))) / 1048576
	FROM [sys].[dm_exec_cached_plans];

	-- Query the cache and determine if ad-hoc optimisation can improve performance.
	WITH CTE AS (
	SELECT	'SQL Server Configuration' AS GROUP_TYPE,
			' Total cache plan size (MB): ' + cast(@TotalSizeInMB AS VARCHAR(max)) + '. Current memory occupied by adhoc plans only used once (MB):' + cast(@AdHocSizeInMB AS VARCHAR(max)) + '.  Percentage of total cache plan occupied by adhoc plans only used once :' + cast(CAST((@AdHocSizeInMB / @TotalSizeInMB) * 100 AS DECIMAL(14, 2)) AS VARCHAR(max)) + '%' + ' ' AS COMMENTS,
			' ' + CASE WHEN @AdHocSizeInMB > 200 OR ((@AdHocSizeInMB / @TotalSizeInMB) * 100) > 25 -- 200MB or > 25%
					THEN 1
					ELSE 0
					END + ' ' AS RECOMMENDATIONS
	)
	SELECT @Optimise = RECOMMENDATIONS FROM CTE
	;

	-- Enable / Disable Ad-Hoc Optimisation depending on result.
	EXEC sp_configure 'optimize for ad hoc workloads',@Optimise;
	RECONFIGURE;
	PRINT 'Process has completed successfully';
END
----------------------------------------------------------------------------------------------------------------------------------------------------

Memory Configuration

Sets the minimum and maximum memory configuration on the server, leaving a headroom of 4GB for general desktop use for remote desktop administration.

----------------------------------------------------------------------------------------------------------------------------------------------------
 
-- DATABASE ADMINISTRATION SCRIPTS - SET THE DATABASE MEMORY SETTINGS
-- Created By: Matthew Senz
-- Description: Sets the database memory settings to leave a headroom of 4GB for Remote Desktop Administration
-- Website: http://help.senzdigital.com
 
----------------------------------------------------------------------------------------------------------------------------------------------------
 
-- Declare Variables and settings
SET NOCOUNT ON;
DECLARE @max_mem int;
DECLARE @memory_spare int;
SET @memory_spare = 4194304; -- KB (4GB)

-- Reconfigure the database memory settings
SELECT	@max_mem = CASE WHEN total_physical_memory_kb>@memory_spare THEN CAST(total_physical_memory_kb-@memory_spare-((total_physical_memory_kb-4194304)/8) AS int) ELSE CAST(total_physical_memory_kb-@memory_spare AS int) END
FROM [master].[sys].[dm_os_sys_memory]
;

IF (SELECT CASE WHEN total_physical_memory_kb>(@memory_spare+1048576) THEN 1 ELSE 0 END as MemoryPass FROM [master].[sys].[dm_os_sys_memory])=1
	BEGIN
		SET @max_mem = @max_mem / 1024; -- Convert KB to MB
		EXEC sp_configure 'min server memory', 0; -- MB
		EXEC sp_configure 'max server memory', @max_mem; -- MB
		RECONFIGURE;
		PRINT 'Process has completed successfully';
	END
ELSE
	BEGIN
		EXEC sp_configure 'min server memory', 0; -- MB
		EXEC sp_configure 'max server memory', 1024; -- MB (1GB)
		RECONFIGURE;
		PRINT 'Process has completed successfully';
	END
----------------------------------------------------------------------------------------------------------------------------------------------------

Enable Database Backup Compression

Setting the compression to "On" by default

----------------------------------------------------------------------------------------------------------------------------------------------------
 
-- DATABASE ADMINISTRATION SCRIPTS - SET BACKUP COMPRESSION SETTINGS TO ON
-- Created By: Matthew Senz
-- Description: Sets the setting for backup compression to on by default
-- Website: http://help.senzdigital.com
 
---------------------------------------------------------------------------------------------------------------------------------------------------- 
-- Declare Variables and settings
SET NOCOUNT ON;

-- Reconfigure the default database backup compression settings
IF (SELECT value FROM sys.configurations WHERE name = 'backup compression default')<>1
BEGIN
	EXEC sp_configure 'backup compression default', 1; -- Turn on Backup Compression
	RECONFIGURE WITH OVERRIDE;
	PRINT 'Process has completed successfully';
END
ELSE
BEGIN
	PRINT 'Backup Compression Default already enabled, no action required.';
END
----------------------------------------------------------------------------------------------------------------------------------------------------

Set default ownership on all databases

This script sets the default owner on all databases to the system default of 'SA'

----------------------------------------------------------------------------------------------------------------------------------------------------

-- DATABASE ADMINISTRATION SCRIPTS - SET DEFAULT OWNER ON ALL DATABASES TO SA
-- Created By: Matthew Senz
-- Description: Sets the owners on all database to the default SA
-- Website: http://help.senzdigital.com

----------------------------------------------------------------------------------------------------------------------------------------------------

-- Declare Variables and settings
SET NOCOUNT ON;
DECLARE @CurrentDatabase varchar(1000);
DECLARE @DBMaxIndex int;
DECLARE @DBCurrentIndex int;
DECLARE @SQL varchar(4000);
DECLARE @CompatibilityLevel int;

CREATE TABLE #Databases ([DatabaseName] varchar(1000), [ProcessIndex] int);

-- Create List of Databases
INSERT INTO #Databases ([DatabaseName],[ProcessIndex])
SELECT	D.[name],
		ROW_NUMBER() OVER (ORDER BY D.[name])
FROM [sys].[databases] D
LEFT OUTER JOIN [sys].[server_principals] SP ON D.owner_sid=SP.[sid]
WHERE SP.[name]<>'sa'
;

-- Process to set compatibility level to server version on all databases
SET @DBCurrentIndex = 1;
SET @DBMaxIndex = (SELECT MAX([ProcessIndex]) as [MaxIndex] FROM #Databases);

IF ( @DBMaxIndex ) > 0
BEGIN
	WHILE ( @DBCurrentIndex <= @DBMaxIndex )
	BEGIN
		SET @CurrentDatabase = (SELECT [DatabaseName] FROM #Databases WHERE ProcessIndex = @DBCurrentIndex);

		SET @SQL = 'ALTER AUTHORIZATION ON DATABASE::[' + @CurrentDatabase + '] TO [sa]';
		EXEC (@SQL);

		SET @DBCurrentIndex = @DBCurrentIndex + 1;
	END
	PRINT 'Process has completed successfully';
END
ELSE
BEGIN
	PRINT 'Database ownership have all been set to [sa], no action required.';
END

-- Clear Temp Tables
DROP TABLE #Databases;

Set default recovery model on all databases

This script sets the default recovery model on all databases to 'Simple'

----------------------------------------------------------------------------------------------------------------------------------------------------

-- DATABASE ADMINISTRATION SCRIPTS - SET RECOVERY MODEL TO SIMPLE ON ALL DATABASES
-- Created By: Matthew Senz
-- Description: Sets the recovery model on all databases to simple
-- Website: http://help.senzdigital.com

----------------------------------------------------------------------------------------------------------------------------------------------------

-- Declare Variables and settings
SET NOCOUNT ON;
DECLARE @CurrentDatabase varchar(1000);
DECLARE @DBMaxIndex int;
DECLARE @DBCurrentIndex int;
DECLARE @SQL varchar(4000);

CREATE TABLE #Databases ([DatabaseName] varchar(1000), [ProcessIndex] int);

-- Create List of Databases
INSERT INTO #Databases ([DatabaseName],[ProcessIndex])
SELECT	[name] as [DatabaseName],
		ROW_NUMBER() OVER (ORDER BY [database_id]) as [ProcessIndex]
FROM [sys].[databases]
WHERE recovery_model_desc<>'SIMPLE'
;

-- Process to set recovery model to simple on all databases
SET @DBCurrentIndex = 1;
SET @DBMaxIndex = (SELECT MAX([ProcessIndex]) as [MaxIndex] FROM #Databases);

IF ( @DBMaxIndex ) > 0
BEGIN
	WHILE ( @DBCurrentIndex <= @DBMaxIndex )
	BEGIN
		SET @CurrentDatabase = (SELECT [DatabaseName] FROM #Databases WHERE ProcessIndex = @DBCurrentIndex);

		SET @SQL = 'ALTER DATABASE [' + @CurrentDatabase + '] SET RECOVERY SIMPLE';
		EXEC (@SQL);

		SET @DBCurrentIndex = @DBCurrentIndex + 1;
	END
	PRINT 'Process has completed successfully';
END
ELSE
BEGIN
	PRINT 'All databases have been set to Simple for their recovery model. No action required.';
END

-- Clear Temp Tables
DROP TABLE #Databases;

Set default compatibility level on all databases

This script sets the default compatibility level on all databases to the server version

----------------------------------------------------------------------------------------------------------------------------------------------------

-- DATABASE ADMINISTRATION SCRIPTS - SET COMPATIBILITY LEVEL ON ALL DATABASES TO SERVER VERSION
-- Created By: Matthew Senz
-- Description: Sets the compatibility on all database to the server version
-- Website: http://help.senzdigital.com

----------------------------------------------------------------------------------------------------------------------------------------------------

-- Declare Variables and settings
SET NOCOUNT ON;
DECLARE @CurrentDatabase varchar(1000);
DECLARE @DBMaxIndex int;
DECLARE @DBCurrentIndex int;
DECLARE @SQL varchar(4000);
DECLARE @CompatibilityLevel int;

CREATE TABLE #Databases ([DatabaseName] varchar(1000), [ProcessIndex] int);

SET @CompatibilityLevel = (
	SELECT 
		CASE 
			WHEN SERVERPROPERTY('ProductMajorVersion')=8 THEN 80		-- SQL Server 2000
			WHEN SERVERPROPERTY('ProductMajorVersion')=9 THEN 90		-- SQL Server 2005
			WHEN SERVERPROPERTY('ProductMajorVersion')=10 THEN 100		-- SQL Server 2008
			WHEN SERVERPROPERTY('ProductMajorVersion')=10.5 THEN 100	-- SQL Server 2008 R2
			WHEN SERVERPROPERTY('ProductMajorVersion')=11 THEN 110		-- SQL Server 2012
			WHEN SERVERPROPERTY('ProductMajorVersion')=12 THEN 120		-- SQL Server 2014
			WHEN SERVERPROPERTY('ProductMajorVersion')=13 THEN 130		-- SQL Server 2016
			WHEN SERVERPROPERTY('ProductMajorVersion')=14 THEN 140		-- SQL Server 2017
			WHEN SERVERPROPERTY('ProductMajorVersion')=15 THEN 150		-- SQL Server 2019
			ELSE (SELECT max([compatibility_level]) FROM master.sys.databases)
		END as [CompatibilityLevel]
	);

-- Create List of Databases
INSERT INTO #Databases ([DatabaseName],[ProcessIndex])
SELECT	[name] as [DatabaseName],
		ROW_NUMBER() OVER (ORDER BY [database_id]) as [ProcessIndex]
FROM [sys].[databases]
WHERE compatibility_level<>@CompatibilityLevel
;

-- Process to set compatibility level to server version on all databases
SET @DBCurrentIndex = 1;
SET @DBMaxIndex = (SELECT MAX([ProcessIndex]) as [MaxIndex] FROM #Databases);

IF ( @DBMaxIndex ) > 0
BEGIN
	WHILE ( @DBCurrentIndex <= @DBMaxIndex )
	BEGIN
		SET @CurrentDatabase = (SELECT [DatabaseName] FROM #Databases WHERE ProcessIndex = @DBCurrentIndex);

		SET @SQL = 'ALTER DATABASE [' + @CurrentDatabase + '] SET COMPATIBILITY_LEVEL = ' + @CompatibilityLevel;
		EXEC (@SQL);

		SET @DBCurrentIndex = @DBCurrentIndex + 1;
	END
	PRINT 'Process has completed successfully';
END
ELSE
BEGIN
	PRINT 'All databases compatibility level have been set to the server version. No action required';
END

-- Clear Temp Tables
DROP TABLE #Databases;

Set default owners on all schemas

This script sets the default owner on all schemas in all databases

----------------------------------------------------------------------------------------------------------------------------------------------------

-- DATABASE ADMINISTRATION SCRIPTS - DEFAULT SCHEMA OWNER FIX
-- Created By: Matthew Senz
-- Description: Fixes the schema owner on system and user created schemas in all user databases to be the default
-- Website: http://help.senzdigital.com

----------------------------------------------------------------------------------------------------------------------------------------------------

-- Declare Variables and settings
SET NOCOUNT ON;
DECLARE @CurrentDatabase varchar(1000);
DECLARE @DBMaxIndex int;
DECLARE @DBCurrentIndex int;
DECLARE @CurrentSchema varchar(1000);
DECLARE @ScMaxIndex int;
DECLARE @ScCurrentIndex int;
DECLARE @SQL varchar(4000);

CREATE TABLE #Databases ([DatabaseName] varchar(1000), [ProcessIndex] int);
CREATE TABLE #Schemas ([SchemaName] varchar(1000), [ProcessIndex] int);

-- Create List of User Databases
INSERT INTO #Databases ([DatabaseName],[ProcessIndex])
SELECT	[name] as [DatabaseName],
		ROW_NUMBER() OVER (ORDER BY [name]) as [ProcessIndex]
FROM [sys].[databases]
WHERE database_id>4
;

-- Process to Reset Schemas to Default
SET @DBCurrentIndex = 1;
SET @DBMaxIndex = (SELECT MAX([ProcessIndex]) as [MaxIndex] FROM #Databases);

WHILE ( @DBCurrentIndex <= @DBMaxIndex )
BEGIN
	TRUNCATE TABLE #Schemas;

	SET @CurrentDatabase = (SELECT [DatabaseName] FROM #Databases WHERE ProcessIndex = @DBCurrentIndex);
	
	SET @SQL = '
			USE [' + @CurrentDatabase + '];

			-- Reset System Schemas to Default Owners
			ALTER AUTHORIZATION ON SCHEMA::[db_accessadmin] TO [db_accessadmin];
			ALTER AUTHORIZATION ON SCHEMA::[db_backupoperator] TO [db_backupoperator];
			ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [db_datareader];
			ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [db_datawriter];
			ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [db_ddladmin];
			ALTER AUTHORIZATION ON SCHEMA::[db_denydatareader] TO [db_denydatareader];
			ALTER AUTHORIZATION ON SCHEMA::[db_denydatawriter] TO [db_denydatawriter];
			ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [db_owner];
			ALTER AUTHORIZATION ON SCHEMA::[db_securityadmin] TO [db_securityadmin];

			-- Create User Schema List that are not default owned
			INSERT INTO #Schemas ([SchemaName],[ProcessIndex])
			SELECT	s.[name] as [Schema],
					ROW_NUMBER() OVER (ORDER BY s.[name]) as [ProcessIndex]
			FROM sys.schemas s, sys.database_principals u
			WHERE s.principal_id = u.principal_id
				AND s.[name] NOT IN (''db_accessadmin'',''db_backupoperator'',''db_datareader'',''db_datawriter'',''db_ddladmin'',''db_denydatareader'',''db_denydatawriter'',''db_owner'',''db_securityadmin'',''guest'',''INFORMATION_SCHEMA'',''sys'')
				AND u.[name] <> ''dbo''
			ORDER BY s.[name]
			;
		'
	;
	EXEC (@SQL);

	SET @ScMaxIndex = (SELECT MAX([ProcessIndex]) FROM #Schemas);
	IF ( @ScMaxIndex ) > 0
	BEGIN
		SET @ScCurrentIndex=1;
		WHILE ( @ScCurrentIndex <= @ScMaxIndex )
		BEGIN
			SET @CurrentSchema = (SELECT [SchemaName] FROM #Schemas WHERE ProcessIndex=@ScCurrentIndex);
		
			SET @SQL = '
				USE [' + @CurrentDatabase + '];

				ALTER AUTHORIZATION ON SCHEMA::[' + @CurrentSchema + '] TO [dbo];
			';
			EXEC (@SQL);

			SET @ScCurrentIndex = @ScCurrentIndex + 1;
		END
	END

	SET @DBCurrentIndex = @DBCurrentIndex + 1; 
END

PRINT 'Process has completed successfully';

-- Clear Temp Tables
DROP TABLE #Databases;
DROP TABLE #Schemas;

Set default schema on all users and groups in all databases

This script sets the default schema to 'dbo' on all users and groups in all databases

----------------------------------------------------------------------------------------------------------------------------------------------------

-- DATABASE ADMINISTRATION SCRIPTS - USER / GROUP DEFAULT SCHEMA FIX
-- Created By: Matthew Senz
-- Description: Fixes the default schema for users and groups in all user databases to be the default
-- Website: http://help.senzdigital.com

----------------------------------------------------------------------------------------------------------------------------------------------------

-- Declare Variables and settings
SET NOCOUNT ON;
DECLARE @CurrentDatabase varchar(1000);
DECLARE @DBMaxIndex int;
DECLARE @DBCurrentIndex int;
DECLARE @CurrentUserGroup varchar(1000);
DECLARE @UgMaxIndex int;
DECLARE @UgCurrentIndex int;
DECLARE @SQL varchar(4000);

CREATE TABLE #Databases ([DatabaseName] varchar(1000), [ProcessIndex] int);
CREATE TABLE #UsersGroups ([UserName] varchar(1000), [ProcessIndex] int);

-- Create List of User Databases
INSERT INTO #Databases ([DatabaseName],[ProcessIndex])
SELECT	[name] as [DatabaseName],
		ROW_NUMBER() OVER (ORDER BY [name]) as [ProcessIndex]
FROM [sys].[databases]
WHERE database_id>4
;

-- Process to Reset Schemas to Default
SET @DBCurrentIndex = 1;
SET @DBMaxIndex = (SELECT MAX([ProcessIndex]) as [MaxIndex] FROM #Databases);

WHILE ( @DBCurrentIndex <= @DBMaxIndex )
BEGIN
	TRUNCATE TABLE #UsersGroups;

	SET @CurrentDatabase = (SELECT [DatabaseName] FROM #Databases WHERE ProcessIndex = @DBCurrentIndex);
	
	SET @SQL = '
			USE [' + @CurrentDatabase + '];

			-- Create User List that are not default
			INSERT INTO #UsersGroups ([UserName],[ProcessIndex])
			SELECT	[name],
					ROW_NUMBER() OVER (ORDER BY [name]) as [ProcessIndex]
			FROM [sys].[database_principals]
			WHERE [type] IN (''S'',''U'',''G'')
				AND [name] NOT IN (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'')
				AND COALESCE([default_schema_name],''NULL'') NOT IN (''dbo'')
			;
		'
	;
	EXEC (@SQL);

	SET @UgMaxIndex = (SELECT MAX([ProcessIndex]) FROM #UsersGroups);
	IF ( @UgMaxIndex ) > 0
	BEGIN
		SET @UgCurrentIndex=1;
		WHILE ( @UgCurrentIndex <= @UgMaxIndex )
		BEGIN
			SET @CurrentUserGroup = (SELECT [UserName] FROM #UsersGroups WHERE ProcessIndex=@UgCurrentIndex);
		
			SET @SQL = '
				USE [' + @CurrentDatabase + '];

				ALTER USER [' + @CurrentUserGroup + '] WITH DEFAULT_SCHEMA = [dbo];
			';
			EXEC (@SQL);

			SET @UgCurrentIndex = @UgCurrentIndex + 1;
		END
	END

	SET @DBCurrentIndex = @DBCurrentIndex + 1; 
END

PRINT 'Process has completed successfully';

-- Clear Temp Tables
DROP TABLE #Databases;
DROP TABLE #UsersGroups;

Remove Windows Users from Server and Databases

This script removes Domain Users from the server and databases. Run this script if you intend to use only Domain Groups to manage access to the database.

----------------------------------------------------------------------------------------------------------------------------------------------------

-- DATABASE ADMINISTRATION SCRIPTS - REMOVE WINDOWS USERS
-- Created By: Matthew Senz
-- Description: Removes windows users so that only domain groups are in the server and databases
-- Website: http://help.senzdigital.com

----------------------------------------------------------------------------------------------------------------------------------------------------

-- Declare Variables and settings
SET NOCOUNT ON;
DECLARE @CurrentDatabase varchar(1000);
DECLARE @DBMaxIndex int;
DECLARE @DBCurrentIndex int;
DECLARE @CurrentUser varchar(1000);
DECLARE @UsMaxIndex int;
DECLARE @UsCurrentIndex int;
DECLARE @SQL varchar(4000);

CREATE TABLE #Databases ([DatabaseName] varchar(1000), [ProcessIndex] int);
CREATE TABLE #Users ([UserName] varchar(1000), [ProcessIndex] int);

-- Create List of Databases
INSERT INTO #Databases ([DatabaseName],[ProcessIndex])
SELECT	[name] as [DatabaseName],
		ROW_NUMBER() OVER (ORDER BY [database_id]) as [ProcessIndex]
FROM [sys].[databases]
;

-- Process to remove windows users from databases
SET @DBCurrentIndex = 1;
SET @DBMaxIndex = (SELECT MAX([ProcessIndex]) as [MaxIndex] FROM #Databases);

WHILE ( @DBCurrentIndex <= @DBMaxIndex )
BEGIN
	TRUNCATE TABLE #Users;
	SET @CurrentDatabase = (SELECT [DatabaseName] FROM #Databases WHERE ProcessIndex = @DBCurrentIndex);

	SET @SQL = '
			USE [' + @CurrentDatabase + '];

			-- Create User List that are not default
			INSERT INTO #Users ([UserName],[ProcessIndex])
			SELECT	[name] as Username,
					ROW_NUMBER() OVER (ORDER BY [name]) as [ProcessIndex]
			FROM [sys].[database_principals]
			WHERE [type] IN (''U'')
				  AND [sid] IS NOT NULL
				  AND [name] != ''guest''
				  AND [name] NOT LIKE ''%SVC%'' -- Service Accounts
			ORDER BY username;
		'
	;
	EXEC (@SQL);

	SET @UsMaxIndex = ( SELECT MAX([ProcessIndex]) FROM #Users );
	SET @UsCurrentIndex = 1;

	IF ( @UsMaxIndex ) > 0
	BEGIN
		WHILE ( @UsCurrentIndex <= @UsMaxIndex )
		BEGIN
			SET @CurrentUser = ( SELECT UserName FROM #Users WHERE ProcessIndex=@UsCurrentIndex );

			SET @SQL = 'USE [' + @CurrentDatabase + ']; DROP USER [' + @CurrentUser + ']';
			EXEC (@SQL);

			SET @UsCurrentIndex = @UsCurrentIndex + 1;
		END
	END
	SET @DBCurrentIndex = @DBCurrentIndex + 1;
END

-- Process to remove windows users from server
TRUNCATE TABLE #Users;

INSERT INTO #Users
SELECT	[name],
		ROW_NUMBER() OVER (ORDER BY [name]) as [ProcessIndex]
FROM [sys].[server_principals] SP
WHERE SP.[type] IN ('U')
	AND [name] NOT LIKE 'NT AUTHORITY%'
	AND [name] NOT LIKE 'NT SERVICE%'
	AND [name] NOT LIKE '%SVC%' -- Service Accounts
ORDER BY SP.[name]
;

SET @UsMaxIndex = ( SELECT MAX([ProcessIndex]) FROM #Users );
SET @UsCurrentIndex = 1;

IF ( @UsMaxIndex ) > 0
BEGIN
	WHILE ( @UsCurrentIndex <= @UsMaxIndex )
	BEGIN
		SET @CurrentUser = ( SELECT UserName FROM #Users WHERE ProcessIndex=@UsCurrentIndex );

		SET @SQL = 'DROP LOGIN [' + @CurrentUser + ']';
		EXEC (@SQL);

		SET @UsCurrentIndex = @UsCurrentIndex + 1;
	END
END

PRINT 'Process has completed successfully';

-- Clear Temp Tables
DROP TABLE #Databases;
DROP TABLE #Users;

Remove Multiple Files (NDF) and FileGroups from database

This script removes the multiple files (ndf) and FileGroups by reverting the default table storage location to primary and then removing multiple files and filegroups. This script is run when an improper implementation has been done on a database / server from a previous DBA and the FileGroups / Files all exist on the same disk or disk interface (IO Device).

For more information, read: SQL Server Geeks: When To Create Multiple Files and Multiple File Groups

Quote from website

Multiple files are created in a single filegroup to achieve better performance provided you place each file on a different disk, since SQL Server uses proportional fill method the data will get split and then written to Files.

----------------------------------------------------------------------------------------------------------------------------------------------------

-- DATABASE ADMINISTRATION SCRIPTS - REMOVE MULTIPLE FILES / FILEGROUPS
-- Created By: Matthew Senz
-- Description: Removes multiple files / filegroups when improper implementation has occured
-- Website: http://help.senzdigital.com

----------------------------------------------------------------------------------------------------------------------------------------------------

-- Use Database Declaration (Please set before running)
USE AdventureWorks2017;

-- Declare Variables and settings
SET NOCOUNT ON;
DECLARE @CurrentDatabase varchar(1000) = (SELECT DB_NAME());
DECLARE @CurrentSchema varchar(1000);
DECLARE @CurrentTable varchar(1000);
DECLARE @CurrentTableType varchar(200);
DECLARE @TaMaxIndex int;
DECLARE @TaCurrentIndex int;
DECLARE @CurrentIndex varchar(8000);
DECLARE @InCurrentIndex int;
DECLARE @InMaxIndex int;
DECLARE @SQL varchar(4000);

CREATE TABLE #Tables ( [SchemaName] varchar(1000), [TableName] varchar(1000), [IndexType] varchar(200), [ProcessIndex] int);
CREATE TABLE #Indexes ( [IndexScript] varchar(8000), [ProcessIndex] int);
CREATE TABLE #Files ( [FileID] int, [FileGroup] int, TotalExtents int, UsedExtents int, [name] varchar(1000), [FileName] varchar(1000), [ProcessIndex] int );
CREATE TABLE #FileGroups ([DataSpaceID] int, [Name] varchar(1000), [TypeDesc] varchar(1000), [ProcessIndex] int );

-- Relocate table storage to PRIMARY
TRUNCATE TABLE #Tables;
INSERT INTO #Tables ([SchemaName], [TableName], [IndexType], [ProcessIndex])
SELECT	S.[name],
		T.[name],
		I.[type_desc],
		ROW_NUMBER() OVER (ORDER BY S.[name], T.[name]) as [ProcessIndex]
FROM [sys].[tables] AS T
INNER JOIN [sys].[indexes] AS I ON I.[object_id] = T.[object_id] and I.[index_id] < 2
LEFT OUTER JOIN [sys].[schemas] AS S ON T.[schema_id] = S.[schema_id] 
LEFT OUTER JOIN [sys].[data_spaces] AS D ON D.[data_space_id] = I.[data_space_id]
WHERE D.[name] NOT IN ('PRIMARY')
;

SET @TaMaxIndex = (SELECT COALESCE(MAX([ProcessIndex]),0) MaxIndex FROM #Tables);
SET @TaCurrentIndex = 1;
	
IF ( @TaMaxIndex ) > 0
BEGIN
	WHILE ( @TaCurrentIndex <= @TaMaxIndex  )
	BEGIN
		SET @CurrentSchema = (SELECT [SchemaName] FROM #Tables WHERE ProcessIndex = @TaCurrentIndex);
		SET @CurrentTable = (SELECT [TableName] FROM #Tables WHERE ProcessIndex = @TaCurrentIndex);
		SET @CurrentTableType = (SELECT [IndexType] FROM #Tables WHERE ProcessIndex = @TaCurrentIndex);

		IF ( @CurrentTableType ) = 'HEAP'
		BEGIN
			RAISERROR ('HEAP Table Index Error Found',1,1);
			PRINT 'HEAP Table Found: [' + @CurrentSchema + '].[' + @CurrentTable + '] , Please create clustered index / primary key on this table before re-running script';
			GOTO Finish;
		END

		TRUNCATE TABLE #Indexes;

		INSERT INTO #Indexes (IndexScript,ProcessIndex)
		SELECT
			CASE si.index_id WHEN 0 THEN N'/* No create statement (Heap) */'
			ELSE
				CASE is_primary_key WHEN 1 THEN
					N'ALTER TABLE ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N' ADD CONSTRAINT ' + QUOTENAME(si.name) + N' PRIMARY KEY ' +
						CASE WHEN si.index_id > 1 THEN N'NON' ELSE N'' END + N'CLUSTERED '
					ELSE N'CREATE ' + 
						CASE WHEN si.is_unique = 1 then N'UNIQUE ' ELSE N'' END +
						CASE WHEN si.index_id > 1 THEN N'NON' ELSE N'' END + N'CLUSTERED ' +
						N'INDEX ' + QUOTENAME(si.name) + N' ON ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N' '
				END +
				/* key def */ N'(' + key_definition + N')' +
				/* includes */ CASE WHEN include_definition IS NOT NULL THEN 
					N' INCLUDE (' + include_definition + N')'
					ELSE N''
				END +
				/* filters */ CASE WHEN filter_definition IS NOT NULL THEN 
					N' WHERE ' + filter_definition ELSE N''
				END +
				/* with clause - compression goes here */
				CASE WHEN row_compression_partition_list IS NOT NULL OR page_compression_partition_list IS NOT NULL 
					THEN N' WITH (' +
						CASE WHEN row_compression_partition_list IS NOT NULL THEN
							N'DATA_COMPRESSION = ROW ' + CASE WHEN psc.name IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + row_compression_partition_list + N')' END
						ELSE N'' END +
						CASE WHEN row_compression_partition_list IS NOT NULL AND page_compression_partition_list IS NOT NULL THEN N', ' ELSE N'' END +
						CASE WHEN page_compression_partition_list IS NOT NULL THEN
							N'DATA_COMPRESSION = PAGE ' + CASE WHEN psc.name IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + page_compression_partition_list + N')' END
						ELSE N'' END
					+ N')'
					ELSE N''
				END +
				/* ON where? filegroup? partition scheme? */
				' WITH (DROP_EXISTING = ON) ON [PRIMARY];'
			END AS index_create_statement,
			ROW_NUMBER() OVER (ORDER BY si.index_id)
		FROM sys.indexes AS si
		JOIN sys.tables AS t ON si.object_id=t.object_id
		JOIN sys.schemas AS sc ON t.schema_id=sc.schema_id
		LEFT JOIN sys.dm_db_index_usage_stats AS stat ON 
			stat.database_id = DB_ID() 
			and si.object_id=stat.object_id 
			and si.index_id=stat.index_id
		LEFT JOIN sys.partition_schemes AS psc ON si.data_space_id=psc.data_space_id
		LEFT JOIN sys.partition_functions AS pf ON psc.function_id=pf.function_id
		LEFT JOIN sys.filegroups AS fg ON si.data_space_id=fg.data_space_id
		/* Key list */ OUTER APPLY ( SELECT STUFF (
			(SELECT N', ' + QUOTENAME(c.name) +
				CASE ic.is_descending_key WHEN 1 then N' DESC' ELSE N'' END
			FROM sys.index_columns AS ic 
			JOIN sys.columns AS c ON 
				ic.column_id=c.column_id  
				and ic.object_id=c.object_id
			WHERE ic.object_id = si.object_id
				and ic.index_id=si.index_id
				and ic.key_ordinal > 0
			ORDER BY ic.key_ordinal FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS keys ( key_definition )
		/* Partitioning Ordinal */ OUTER APPLY (
			SELECT MAX(QUOTENAME(c.name)) AS column_name
			FROM sys.index_columns AS ic 
			JOIN sys.columns AS c ON 
				ic.column_id=c.column_id  
				and ic.object_id=c.object_id
			WHERE ic.object_id = si.object_id
				and ic.index_id=si.index_id
				and ic.partition_ordinal = 1) AS partitioning_column
		/* Include list */ OUTER APPLY ( SELECT STUFF (
			(SELECT N', ' + QUOTENAME(c.name)
			FROM sys.index_columns AS ic 
			JOIN sys.columns AS c ON 
				ic.column_id=c.column_id  
				and ic.object_id=c.object_id
			WHERE ic.object_id = si.object_id
				and ic.index_id=si.index_id
				and ic.is_included_column = 1
			ORDER BY c.name FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS includes ( include_definition )
		/* Partitions */ OUTER APPLY ( 
			SELECT 
				COUNT(*) AS partition_count,
				CAST(SUM(ps.in_row_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_in_row_GB,
				CAST(SUM(ps.lob_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_LOB_GB,
				SUM(ps.row_count) AS row_count
			FROM sys.partitions AS p
			JOIN sys.dm_db_partition_stats AS ps ON
				p.partition_id=ps.partition_id
			WHERE p.object_id = si.object_id
				and p.index_id=si.index_id
			) AS partition_sums
		/* row compression list by partition */ OUTER APPLY ( SELECT STUFF (
			(SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))
			FROM sys.partitions AS p
			WHERE p.object_id = si.object_id
				and p.index_id=si.index_id
				and p.data_compression = 1
			ORDER BY p.partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS row_compression_clause ( row_compression_partition_list )
		/* data compression list by partition */ OUTER APPLY ( SELECT STUFF (
			(SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))
			FROM sys.partitions AS p
			WHERE p.object_id = si.object_id
				and p.index_id=si.index_id
				and p.data_compression = 2
			ORDER BY p.partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS page_compression_clause ( page_compression_partition_list )
		WHERE si.type IN (0,1,2)
			AND sc.[name]=@CurrentSchema
			AND t.[name]=@CurrentTable
		;

		SET @InMaxIndex = (SELECT COALESCE(MAX([ProcessIndex]),0) MaxIndex FROM #Indexes);
		SET @InCurrentIndex = 1;

		IF ( @InMaxIndex ) > 0
		BEGIN
			WHILE ( @InCurrentIndex <= @InMaxIndex  )
			BEGIN
				SET @CurrentIndex = ( SELECT [IndexScript] FROM #Indexes WHERE ProcessIndex = @InCurrentIndex );
				SET @SQL = @CurrentIndex;
				EXEC (@SQL);
				SET @InCurrentIndex = @InCurrentIndex + 1;
			END
		END

		SET @TaCurrentIndex = @TaCurrentIndex + 1;
	END
END

-- Process and remove NDF File Storage
DECLARE @FiCurrentIndex int;
DECLARE @FiMaxIndex int;
DECLARE @CurrentFile varchar(1000);

INSERT INTO #Files ([FileID],[FileGroup],[TotalExtents],[UsedExtents],[name],[FileName]) EXEC ('DBCC showfilestats');

DELETE FROM #Files WHERE [FileID]=1 AND [FileGroup]=1;

UPDATE F
SET F.ProcessIndex=FU.NewIndex
FROM #Files F
INNER JOIN (SELECT *, ROW_NUMBER() OVER (ORDER BY [FileGroup],[FileID]) as NewIndex FROM #Files) FU ON F.FileID=FU.FileID
;

SET @FiMaxIndex = (SELECT MAX(ProcessIndex) FROM #Files);
SET @FiCurrentIndex = 1;

IF (@FiMaxIndex) > 0
BEGIN
	WHILE ( @FiCurrentIndex <= @FiMaxIndex  )
	BEGIN
		SET @CurrentFile = (SELECT [name] FROM #Files WHERE ProcessIndex = @FiCurrentIndex);

		SET @SQL = 'DBCC SHRINKFILE (' + @CurrentFile + ', EMPTYFILE)';
		EXEC (@SQL);

		SET @SQL = 'ALTER DATABASE [' + @CurrentDatabase + '] REMOVE FILE [' + @CurrentFile + ']';
		EXEC (@SQL);

		SET @FiCurrentIndex = @FiCurrentIndex + 1;
	END
END

-- Set PRIMARY as default FileGroup
IF ( SELECT COALESCE(data_space_id,0) FROM [sys].[filegroups] WHERE data_space_id=1 AND is_default<>1 ) > 0
BEGIN
	SET @SQL = 'ALTER DATABASE [' + @CurrentDatabase + '] MODIFY FILEGROUP [PRIMARY] DEFAULT';
	EXEC (@SQL);
END

-- Process and remove additional filegroups
DECLARE @FgCurrentIndex int;
DECLARE @FgMaxIndex int;
DECLARE @CurrentFileGroup varchar(1000);

INSERT INTO #FileGroups ([DataSpaceID],[Name],[TypeDesc],[ProcessIndex])
SELECT	[data_space_id],
		[name],
		[type_desc],
		ROW_NUMBER() OVER (ORDER BY [data_space_id]) as [ProcessIndex]
FROM [sys].[filegroups]
WHERE data_space_id>1
;

SET @FgMaxIndex = (SELECT MAX(ProcessIndex) FROM #FileGroups);
SET @FgCurrentIndex = 1;

IF (@FgMaxIndex) > 0
BEGIN
	WHILE ( @FgCurrentIndex <= @FgMaxIndex  )
	BEGIN
		SET @CurrentFileGroup = (SELECT [name] FROM #FileGroups WHERE ProcessIndex = @FgCurrentIndex);

		SET @SQL = 'ALTER DATABASE [' + @CurrentDatabase + '] REMOVE FILEGROUP [' + @CurrentFileGroup + ']';  
		EXEC (@SQL);

		SET @FgCurrentIndex = @FgCurrentIndex + 1;
	END
END

PRINT 'Process has completed successfully';

-- Clear Temp Tables
Finish:
DROP TABLE #Tables;
DROP TABLE #Indexes;
DROP TABLE #Files;
DROP TABLE #FileGroups;

Page tree

Database Administration Scripts / Tools