🐌

Tristan Otterpohl Otterpohl

🐌

Otterpohl / RemovePort.yaml

Created November 9, 2023 11:11

Prometheus relabel config to remove port number

Otterpohl / mac file remove quarantine

Created May 31, 2023 15:05

xattr -d com.apple.quarantine /path/to/file

Otterpohl / pvc troubleshooter

Created April 18, 2023 16:41

Otterpohl / AddSpacerTileToDock.sh

Created June 14, 2022 10:18

	# Left side
	defaults write com.apple.dock persistent-apps -array-add '{tile-type="spacer-tile";}'
	killall Dock

	# Right Side
	defaults write com.apple.dock persistent-others -array-add '{tile-data={}; tile-type="spacer-tile";}'
	killall Dock

Otterpohl / Set-MaxServerMemory.sql

Created May 13, 2022 16:26

Calculate and set the max memory allocation

	DECLARE @OS_TotalMemory INT
	,@OS_CPUCount INT
	,@OS_Reserved INT
	,@SQL_MAlloc INT

	SELECT @OS_TotalMemory = CAST(total_physical_memory_kb AS INT) / 1024 / 1024 FROM sys.dm_os_sys_memory
	SELECT @OS_CPUCount = cpu_count FROM sys.dm_os_sys_info
	SELECT @OS_Reserved = 4
	SELECT @SQL_MAlloc = ((@OS_TotalMemory - (@OS_CPUCount * 0.5) - @OS_Reserved) - (@OS_TotalMemory - (@OS_CPUCount * 0.5) - @OS_Reserved) % 2) * 1024

Otterpohl / Set-MaxDOP

Created May 13, 2022 16:23

Set MaxDOP to match number of cores

	EXEC sys.sp_configure N'show advanced options', 1
	GO
	RECONFIGURE WITH OVERRIDE
	GO

	DECLARE @DOP INT

	SELECT @DOP = CASE
	WHEN cpu_count > 16 THEN 8
	else cpu_count / 2

Otterpohl / Set-DatabaseOwnerToSA.sql

Created May 13, 2022 16:22

Set the database owner to sa

	SELECT 'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(d.[name]) + ' to sa'
	FROM [sys].[databases] d
	INNER JOIN [sys].[server_principals] s ON s.sid = d.owner_sid
	WHERE d.[name] NOT IN ('master','msdb','model','tempdb')
	AND s.[name] <> 'sa'

Otterpohl / Set-AutoShrinkOff

Created May 13, 2022 16:18

Set AutoShrink to disabled for all databases where it is enabled

	SELECT 'ALTER DATABASE ' + QUOTENAME(name) + ' SET AUTO_SHRINK OFF WITH NO_WAIT;' AS [To Execute]
	FROM sys.databases
	WHERE is_auto_shrink_on = 1;

Otterpohl / Set-AgentJobOwnerToSA

Created May 13, 2022 16:17

Sets all agent jobs owner to sa account

	SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N''' + CAST(j.job_id AS VARCHAR(150)) + ''', @owner_login_name=N''sa'' '
	FROM msdb.dbo.sysjobs AS j
	INNER JOIN master.sys.syslogins AS L
	ON j.owner_sid = L.sid
	INNER JOIN msdb.dbo.syscategories AS C
	ON C.category_id = j.category_id
	WHERE msdb.dbo.SQLAGENT_SUSER_SNAME(j.owner_sid) <> 'sa';

Otterpohl / Script-DropSchemaObjects

Created May 13, 2022 16:06

Generate script to drop all schema objects

	SET NOCOUNT ON
	DECLARE @SchemaName NVARCHAR(100) = 'mipl'

	SELECT 'DROP ' +
	CASE WHEN type IN ('P','PC') THEN 'PROCEDURE'
	WHEN type = 'U' THEN 'TABLE'
	WHEN type IN ('IF','TF','FN') THEN 'FUNCTION'
	WHEN type = 'V' THEN 'VIEW'
	END +
	' ' + QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(name) as DropStatement