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

CLI snippets

Image Conversion

Convert all JPGs in a directory to JPG XL with lossless conversion

for /R %i in (*.jpg) do cjxl.exe --lossless_jpeg=1 "%i" "%~dpni.JXL"

Convert all TIFFs in a directory to JPG XL with lossless conversion copying metadata (ImageMagick, worse)

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;

ghotz

$cred = Get-Credential;
Get-DbaRegisteredServer -Group "Monitored\SQLAuth" | % {
        Write-Host "connecting to $($_.ServerName)";
        $srv = new-object Microsoft.sqlserver.management.smo.server $_.ServerName;
        $srv.ConnectionContext.LoginSecure = $false;
        $srv.ConnectionContext.Login = $Cred.UserName;
        $srv.ConnectionContext.set_SecurePassword($cred.Password);
        $srv.JobServer.Alerts | % {
            if ($_.DelayBetweenResponses -eq 0) { $_.DelayBetweenResponses = 60; $_.Alter(); } 
        } 

ghotz

-- Show different ways to represent the duration of a time interval starting form elapsed milliseconds
-- adapted from  https://stackoverflow.com/a/26347259
DECLARE @x bigint = (24*60*60*1000)-2; -- milliseconds
SELECT
    CONVERT(varchar, @x / 1000 / 86400) + ':' + CONVERT(varchar, DATEADD(ms, (@x % 86400) * 1000, 0), 108) AS [DD:HH:MM:SS]
,    CONVERT(varchar, @x / 1000 / 86400) + ':' + CONVERT(varchar, DATEADD(ms, @x, 0), 114)  AS [DD:HH:MM:SS.MS]
,    CASE WHEN CONVERT(varchar, @x / 1000 / 86400) > 0 THEN CONVERT(varchar, @x / 1000 / 86400) + ':' ELSE '' END + CONVERT(varchar, DATEADD(ms, (@x % 86400) * 1000, 0), 108) AS [{DD:}HH:MM:SS]
,    CASE WHEN CONVERT(varchar, @x / 1000 / 86400) > 0 THEN CONVERT(varchar, @x / 1000 / 86400) + ':' ELSE '' END + CONVERT(varchar, DATEADD(ms, @x, 0), 114) AS [{DD:}HH:MM:SS.MS]
,    CONVERT(varchar, @x / 1000 / 86400) + ' day(s) ' + CONVERT(varchar, DATEADD(ms, (@x % 86400) * 1000, 0), 108) AS [DD day(s) HH:MM:SS]
,    CONVERT(varchar, @x / 1000 / 86400) + ' day(s) ' + 

ghotz

-------------------------------------------------------------------------------
-- search schema catalog for column names
-- example template showing how to execute for each database
-------------------------------------------------------------------------------
DECLARE	@Databases			TABLE (DatabaseName sysname primary key);
DECLARE	@DatabaseName		sysname;
DECLARE @sqlstmt_replaced	nvarchar(max);

-- temp table to hold merged results instead of multiple results sets
DROP TABLE IF EXISTS #tmp;

ghotz

# extract counters first with relog.exe .\myfile.blg -q > counters.txt
$currentPath = $PSScriptRoot                                                                                                     # AzureDevOps, Powershell
if (!$currentPath) { $currentPath = Split-Path $pseditor.GetEditorContext().CurrentFile.Path -ErrorAction SilentlyContinue }     # VSCode
if (!$currentPath) { $currentPath = Split-Path $psISE.CurrentFile.FullPath -ErrorAction SilentlyContinue }                       # PsISE
if ($currentPath) { Set-Location $currentPath }

#$Databases = ("databasename");
$Databases = @(Get-Content (Join-Path $currentPath "databases.txt"));

$LinesRead = 0;

ghotz

USE AdventureWorks2017;
GO

SELECT * FROM Person.Person;

BEGIN TRANSACTION;

WITH cte AS
(
	SELECT