Find files that have percent growth-Key database properties for all databases on instance-Changing selected database properties

-- Find files that have percent growth
--------------------------------------------------------------------------------------------


SELECT DB_NAME([database_id]) AS [Database Name],
       [file_id], name, physical_name, type_desc, state_desc,
   is_percent_growth, growth,
   CONVERT(bigint, growth/128.0) AS [Growth in MB],
       CONVERT(bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE is_percent_growth = 1
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);


-- Key database properties for all databases on instance
------------------------------------------------------------------------
SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model], db.containment_desc,
db.[compatibility_level] AS [DB Compatibility Level], db.page_verify_option_desc,
db.is_auto_create_stats_on, db.is_auto_update_stats_on, db.is_auto_update_stats_async_on,
db.is_auto_create_stats_incremental_on, db.is_parameterization_forced,
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
db.is_auto_close_on, db.is_auto_shrink_on, db.target_recovery_time_in_seconds, db.is_cdc_enabled,
db.is_published, db.group_database_id, db.replica_id,
db.is_memory_optimized_elevate_to_snapshot_on, db.delayed_durability_desc   
FROM sys.databases AS db WITH (NOLOCK);



-- Changing selected database properties
--------------------------------------------------------------------------

USE [master]
GO

-- Enable auto update statistsics asynchronously
ALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT;
GO

-- Enable delayed durability
ALTER DATABASE [AdventureWorks] SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT;
GO

-- Enable CHECKSUM for the page verify option
ALTER DATABASE [AdventureWorks] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT;
GO


--These are the common settings which we should change

-- Get configuration values for instance
SELECT name, value, value_in_use, [description]
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);


-- Set Instance-level options to more appropriate values

-- Enable backup checksum default
EXEC sys.sp_configure 'backup checksum default', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO


-- Enable backup compression default
EXEC sys.sp_configure 'backup compression default', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO


-- Change cost threshold for parallelism to a higher value
EXEC sys.sp_configure 'cost threshold for parallelism', X;
GO
RECONFIGURE WITH OVERRIDE;
GO

-- Set max server memory to XMB
EXEC sys.sp_configure 'max server memory (MB)', X;
GO
RECONFIGURE WITH OVERRIDE;
GO


-- Change max degree of parallelism to X (number of physical cores in a NUMA node)
EXEC sys.sp_configure 'max degree of parallelism', X;
GO
RECONFIGURE WITH OVERRIDE;
GO


-- Enable optimize for ad hoc workloads
EXEC sys.sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE WITH OVERRIDE;
GO


-- Enable remote admin connections
EXEC sys.sp_configure 'remote admin connections', 1;
RECONFIGURE WITH OVERRIDE;
GO

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