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;