ghotz

USE master;
GO
-- enable external scripting at instance level
EXEC sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO

ghotz

Get-NetFirewallRule | ? {$_.Name -like "HNS Container Networking - DNS (UDP-In)*" } | Remove-NetFirewallRule
Get-NetFirewallRule | ? {$_.Name -like "HNS Container Networking - ICS DNS (TCP-In)*" } | Remove-NetFirewallRule

ghotz

-- Search statistics with leading column name
SELECT
		OBJECT_SCHEMA_NAME(C2.[object_id]) AS [schema_name]
	,	OBJECT_NAME(C2.[object_id]) AS [table_name]
	,	S1.[name] AS stats_name
	,	S1.auto_created
	,	P1.*
FROM	sys.stats AS S1
JOIN	sys.stats_columns AS C1 ON S1.[object_id] = C1.[object_id] AND S1.stats_id = C1.stats_id
JOIN	sys.columns AS C2 ON C1.[object_id] = C2.[object_id] AND C1.column_id = C2.column_id

ghotz

DROP TABLE IF EXISTS #tmp;

CREATE TABLE #tmp (
	[database_name]		sysname			NOT NULL
,	[user_name]			sysname			NOT NULL
,	[type_desc]			nvarchar(120)	NOT NULL
,	[user_sid]			varbinary(85)	NULL
,	[login_name]		sysname			NULL
);
GO

ghotz

exec sp_msforeachdb 'USE [?]; exec sp_updatestats;'
exec sp_msforeachdb 'DBCC CHECKALLOC([?])'

ghotz

-- Notes
--
-- 1) you may find that the allocated pages toward the end of files belong to
-- one or more system objects, in which case you can't rebuild/reorg them, so you
-- may need to shrink the files to move at least these and then resume from the next
-- non system object
--
-- 2) rebuilding heaps is not implemented, it's just a matter to add a CASE, I'll do it
-- the first time I have to so that I can test it
--

ghotz

/*
IF EXISTS (SELECT * FROM sys.dm_xe_sessions WHERE [name] = 'classify_recompiles')
	DROP EVENT SESSION [classify_recompiles] ON SERVER;

CREATE EVENT SESSION [classify_recompiles] ON SERVER
    ADD EVENT sqlserver.sql_statement_recompile(
		WHERE ([source_database_id]>(4)) -- skip system databases
		)
    ADD TARGET package0.histogram
    (SET filtering_event_name = N'sqlserver.sql_statement_recompile', source = N'recompile_cause', source_type = (0))

ghotz

Exiftool snippets

General

To recurse sub-directories, add -r, specify . and filter by extension.

Filtering only some extensions:

• photos -ext jpg -ext orf -ext ori -ext dng -ext heic -ext PEF -ext RAF -ext SRF -ext NEF -ext X3F -ext KDC
• videos -ext mp4 -ext mov -ext mts -ext avi

ghotz

select left(appdomain_name, 19) as appdomain_name, [state]
, sum(total_allocated_memory_kb) / 1024. AS total_allocated_memory_MB
, sum(survived_memory_kb) / 1024. as survived_memory_MB
from sys.dm_clr_appdomains
group by left(appdomain_name, 19), [state]

--select * from sys.dm_clr_properties

--select * from sys.dm_clr_appdomains
--select * from sys.dm_clr_loaded_assemblies

ghotz

DROP PROCEDURE IF EXISTS dbo.SetDatabaseSingleUser
GO
CREATE PROCEDURE dbo.SetDatabaseSingleUser
	@DatabaseName	 sysname
,	@debug			 tinyint = 0
AS
IF @debug = 1 PRINT FORMATMESSAGE('%s %s: stored procedure starting', convert(varchar(25), getdate(), 120), QUOTENAME(OBJECT_NAME(@@PROCID)));
IF @debug = 1 PRINT FORMATMESSAGE('%s %s: parameter @DatabaseName=''%s''', convert(varchar(25), getdate(), 120), QUOTENAME(OBJECT_NAME(@@PROCID)), @DatabaseName);
SET NOCOUNT ON;
SET XACT_ABORT ON;