ghotz

SELECT
	FLOOR(transferred_size_bytes * 1. / NULLIF(database_size_bytes, 0) * 100) AS Perc
,	transfer_rate_bytes_per_second / 1024 / 1024. AS MBSec
,	internal_state_desc
,	start_time_utc, estimate_time_complete_utc
,	FLOOR(transferred_size_bytes / 1024. / 1024. / 1024.) AS transferred_size_GB
,	FLOOR(database_size_bytes / 1024. / 1024. / 1024.) AS database_size_bytes_GB
,	*
FROM sys.dm_hadr_physical_seeding_stats

ghotz

WITH cte AS
(
	SELECT
		CASE
		WHEN	PATINDEX('SQL Server (%', servicename) > 0
		THEN	'Engine'
		WHEN	PATINDEX('SQL Server Agent (%', servicename) > 0
		THEN	'Agent'
		WHEN	PATINDEX('SQL Full-text Filter Daemon Launcher (%', servicename) > 0
		THEN	'FullText'

ghotz

WITH cte AS
(
	SELECT
		RH.destination_database_name, RH.restore_type
	,	ROW_NUMBER() OVER (PARTITION BY RH.destination_database_name, RH.restore_type ORDER BY RH.restore_history_id DESC) AS rn
	,	RH.restore_date, BS.backup_start_date, BS.backup_finish_date
	,	CASE WHEN CONVERT(varchar, DATEDIFF(ms, backup_start_date, backup_finish_date) / 1000 / 86400) > 0 THEN CONVERT(varchar, DATEDIFF(ms, backup_start_date, backup_finish_date) / 1000 / 86400) + ' day(s) ' ELSE '' END + CONVERT(varchar, DATEADD(ms, DATEDIFF(ms, backup_start_date, backup_finish_date), 0), 114) AS elapsed_time
	,	FORMAT(BS.backup_size / 1073741824., 'N2') AS backup_size_gb
	,	FORMAT(BS.compressed_backup_size / 1073741824., 'N2') AS compressed_backup_size_gb
	FROM	msdb.dbo.restorehistory AS RH

ghotz

SELECT
	JO.[name] AS job_name, JO.[enabled]
,	JS.step_id, JS.step_name, JS.subsystem, JS.command
FROM	msdb.dbo.sysjobs AS JO
JOIN	msdb.dbo.sysjobsteps as JS
  ON	JO.job_id = JS.job_id
WHERE	JS.command like N'%something%'

ghotz

EXEC sp_msforeachdb 'USE [?];
SELECT
	DB_NAME()
,	S.[object_id], S.[stats_id]
,	FORMATMESSAGE(
		N''[%s].[%s]''
	,	OBJECT_SCHEMA_NAME(S.[object_id])
	,	OBJECT_NAME(S.[object_id])
	)AS object_full_name
,	QUOTENAME(S.[name]) AS stat_name

ghotz

# Windows Sandbox
Disable-WindowsOptionalFeature -FeatureName "Containers-DisposableClientVM" -Online -NoRestart

# WSL
Disable-WindowsOptionalFeature -FeatureName "VirtualMachinePlatform" -Online -NoRestart
Disable-WindowsOptionalFeature -FeatureName "Microsoft-Windows-Subsystem-Linux" -Online -NoRestart

# Hyper-V
Disable-WindowsOptionalFeature -FeatureName "Microsoft-Hyper-V-All" -Online -NoRestart

ghotz

SELECT
	J1.[name] AS job_name
,	C1.[name] AS category_name, S1.[name] as [owner_name]
,	CASE
	WHEN S1.[name] = N'sa' OR S1.[name] LIKE '##%'
	THEN NULL
	ELSE	'EXEC msdb..sp_update_job @job_name = ''' + J1.[name] + ''''
		+	', @owner_login_name = ''sa'' -- was ' + QUOTENAME(S1.[name])
	END	AS alter_command
FROM	msdb.dbo.sysjobs AS J1

ghotz

SELECT
	D1.[name] AS database_name, S1.[name] as [owner_name]
,	CASE
	WHEN S1.[name] = N'sa'
	THEN NULL
	--ELSE	'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(D1.[name]) + ' TO [sa] -- was ' + QUOTENAME(S1.[name])
	ELSE	'USE ' + QUOTENAME(D1.[name]) + '; EXEC sp_changedbowner ''sa'' -- was ' + QUOTENAME(S1.[name])
	END	AS alter_command
FROM	sys.databases AS D1
LEFT

ghotz

SELECT	
	QUOTENAME(S1.[name]) AS [schema_name]
,	QUOTENAME(O1.[name]) AS [table_name]
,	QUOTENAME(C1.[name]) AS [constraint_name]
,	N'FOREIGN_KEY_CONSTRAINT' AS [constraint_type]
,	'ALTER TABLE ' + QUOTENAME(S1.[name]) + '.' + QUOTENAME(O1.[name])
+	' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(C1.[name]) AS sql_stmt
FROM sys.foreign_keys AS C1
JOIN sys.objects AS O1 ON C1.parent_object_id = O1.[object_id]
JOIN sys.schemas AS S1 ON O1.[schema_id] = S1.[schema_id]

ghotz

SELECT
	[name] AS database_name, page_verify_option_desc
,	CASE
	WHEN page_verify_option_desc = N'CHECKSUM'
	THEN NULL
	ELSE	'ALTER DATABASE [' + [name] + ']'
		+	' SET PAGE_VERIFY CHECKSUM;'
	END	AS alter_command
FROM	sys.databases
WHERE	[name] NOT IN ('master','model','msdb','tempdb')