ghotz

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
	DROP TABLE #tmp;

CREATE TABLE #tmp (
	[database_name] sysname NOT NULL
,	collation_name	sysname NOT NULL
,	table_type		sysname NOT NULL
,	num_tables		int		NOT NULL
,	num_columns		int		NOT NULL
,	PRIMARY KEY ([database_name], collation_name, table_type)

ghotz

# quick fix for  Copy-DbaAgentAlert not copying categories (and  Copy-DbaAgentAlertCategories not existing)
param ([string] $Source, $Destination)
 Get-DbaAgentAlertCategory -SqlInstance $Source | % { New-DbaAgentAlertCategory -SqlInstance $Destination -Category $_.Name};
 Copy-DbaAgentAlert -Source $Source -Destination $Destination;

ghotz

# requires dbatools module
Get-DbaRegServer -SqlInstance localhost | % { write-host "testing $($_.ServerName)"; Invoke-DbaQuery -SqlInstance $_.ServerName -Query "DECLARE @sub nvarchar(max) = 'Test message from server ' + @@SERVERNAME, @bod nvarchar(max) = 'Test message sent by server ' + @@SERVERNAME + '.'; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL Server Admin', @recipients = '[email protected]',  @subject = @sub, @body = @bod;" }

ghotz

IF OBJECT_ID('tempdb..#tmp1') IS NOT NULL DROP TABLE #tmp1;
CREATE TABLE #tmp1 (DatabaseName sysname, ParentObject NVARCHAR(MAX), [Object] NVARCHAR(MAX), Field NVARCHAR(MAX), VALUE NVARCHAR(MAX));
EXEC sys.sp_MSforeachdb N'IF OBJECT_ID(''#tmp2'') IS NOT NULL DROP TABLE #tmp2; CREATE TABLE #tmp2 (ParentObject NVARCHAR(MAX), [Object] NVARCHAR(MAX), Field NVARCHAR(MAX), VALUE NVARCHAR(MAX)); INSERT #tmp2 EXEC(''DBCC DBINFO(''''?'''') WITH TABLERESULTS''); INSERT #tmp1 SELECT ''?'', * FROM #tmp2 WHERE Field = ''dbi_dbccLastKnownGood'''
SELECT DatabaseName, VALUE AS dbi_dbccLastKnownGood FROM #tmp1 ORDER BY 2 ASC;

ghotz

$Commands = @(); $Source = "oldsqlname"; $Destination = "newsqlname";
Get-DbaAgentJob -SqlInstance $Source | % { $Commands += "Set-DbaAgentJob -SqlInstance $Destination -Job `"$($_.Name)`" $(if ($_.Enabled) { `"-Enabled;`" } else { `"-Disabled;`" })" };
Write-Output @Commands

ghotz

SELECT
	session_id, command, start_time, percent_complete, 
    estimated_completion_time AS estimated_completion_time_ms, 
    estimated_completion_time / (1000 * 60) AS estimated_completion_time_min
FROM
	sys.dm_exec_requests
WHERE
	command IN ('BACKUP DATABASE', 'BACKUP LOG', 'RESTORE DATABASE', 'RESTORE LOG', 'RESTORE HEADERONLY', 'RESTORE FILELISTONLY')
ORDER BY estimated_completion_time;

ghotz

DECLARE	@pkname		sysname = (SELECT [name] FROM sys.key_constraints WHERE [type] = 'PK' AND parent_object_id = OBJECT_ID(N'dbo.CounterDetails'));
DECLARE	@sqlstmt	nvarchar(max) = N'ALTER TABLE dbo.CounterDetails DROP CONSTRAINT [' + @pkname + ']';
EXEC (@sqlstmt);

CREATE CLUSTERED COLUMNSTORE INDEX cci_CounterDetails ON dbo.CounterDetails
WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0, MAXDOP = 1);

--ALTER TABLE dbo.CounterDetails
--ADD CONSTRAINT PK_CounterDetails PRIMARY KEY NONCLUSTERED (CounterID);
GO

ghotz

-- filter columns https://docs.microsoft.com/en-us/sql/relational-databases/sql-trace/sql-trace?view=sql-server-ver15#use-data-columns-to-describe-returned-events
-- filter logical and comparison operators https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-trace-setfilter-transact-sql
SELECT * FROM sys.traces					-- get trace info
EXEC sp_trace_setstatus ?, 0				-- stop trace
EXEC sp_trace_setfilter ?,  8, 0, 0, ''						-- Hostname, AND, =
EXEC sp_trace_setfilter ?, 10, 7, 0, 'SQL Server Profiler'	-- ApplicationName, , NOT LIKE
EXEC sp_trace_setfilter ?, 12, 0, 0, ??						-- SPID, AND, =

SELECT * FROM fn_trace_getfilterinfo(?)		-- check filters
EXEC sp_trace_setstatus ?, 1				-- start trace

ghotz

WITH jobs_runstats AS
(
	SELECT
		J.job_id AS job_id
	,	J.[name] AS job_name
	,	CAST(CAST(H.run_date AS varchar(8)) + ' ' + STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(H.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') AS datetime) AS start_execution_date
	,	DATEADD(second, (CAST(LEFT(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 2) as int) * 3600) + (CAST(SUBSTRING(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 3, 2) AS int) * 60) + CAST(RIGHT(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 2) AS int), CAST(CAST(H.run_date AS varchar(8)) + ' ' + STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(H.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') AS datetime)) AS stop_execution_date
	,	(CAST(LEFT(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 2) as int) * 3600) + (CAST(SUBSTRING(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 3, 2) AS int) * 60) + CAST(RIGHT(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varch

ghotz

DECLARE	@baseline_start_date datetime	= '2021-11-01';
DECLARE	@baseline_end_date datetime		= '2021-11-05';
DECLARE	@compare_start_date datetime	= '2021-11-08';
DECLARE	@compare_end_date datetime		= '2021-11-12';
DECLARE	@seconds_threshold int = 30;

WITH jobs_runstats_baseline AS
(
	SELECT
		J.job_id AS job_id