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

ghotz

USE AdventureWorks2017;
GO

SELECT
	LastName
,	CRYPT_GEN_RANDOM(4) AS crypt_gen_rand_value		-- only available SQL Server >= 2012
,	CAST(NEWID() AS VARBINARY(4)) AS newid_value
,	LEFT(CONVERT(nvarchar(128), HASHBYTES('SHA2_512', LastName), 2), 20) AS hashbytes_value
,	LEFT(CONVERT(nvarchar(128), HASHBYTES('SHA2_512', CAST(LastName AS VARBINARY) + CRYPT_GEN_RANDOM(4)), 2), 20) AS hashbytes_crypt_gen_rand_append_value
,	LEFT(CONVERT(nvarchar(128), HASHBYTES('SHA2_512', CAST(LastName AS VARBINARY) + CAST(NEWID() AS VARBINARY(4))), 2) , 20) AS hashbytes_newsid_append_value

ghotz

WITH cte_sessions AS
(
	SELECT
		exs.session_id
	,	exs.database_id
	,	exs.last_request_start_time
	,	exs.last_request_end_time
	,	exs.memory_usage
	,	ssu.user_objects_alloc_page_count +
		ssu.internal_objects_alloc_page_count AS tempdb_page_allocations

ghotz

-- convert Hex LSN to decimal (e.g. from fb_dblog() to backup sets)
-- credit https://stackoverflow.com/questions/70213942/convert-hex-current-lsn-to-decimal-converter
WITH s as (
    select Replace('00000016:000001a5:0001',':','.') lsn
)
select 
 Concat (
    Convert(bigint,Convert(varbinary,Concat('0x',ParseName(lsn,3)),1)) * 1000000,
    Convert(int,Convert(varbinary,Concat('0x',ParseName(lsn,2)),1)) * 10000,
    Convert(int,Convert(varbinary,Concat('0x',ParseName(lsn,1)),1))

ghotz

DROP TABLE IF EXISTS #tmp
CREATE TABLE #tmp
(
	[database_name] [NVARCHAR](128) NULL,
	[database_id] [INT] NULL,
	[file_id] [SMALLINT] NULL,
	[filegroup_id] [SMALLINT] NULL,
	[total_page_count] [BIGINT] NULL,
	[allocated_extent_page_count] [BIGINT] NULL,
	[unallocated_extent_page_count] [BIGINT] NULL,

ghotz

Install-WindowsFeature RSAT-AD-PowerShell
Install-ADServiceAccount gMSAsqlservice
Test-ADServiceAccount gMSAsqlservice