ghotz

WITH cte_locks AS
(
	SELECT
		XQ.event_node.value('(@name)[1]', 'varchar(50)') AS event_name
	,	XQ.event_node.value('(@package)[1]', 'varchar(50)') AS package_name
	,	DATEADD(hh , DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), XQ.event_node.value('(@timestamp)[1]', 'datetime2')) AS [timestamp]
	,	XQ.event_node.value('(data[@name="database_name"]/value)[1]', 'sysname') as [database_name]
	,	XQ.event_node.value('(data[@name="duration"]/value)[1]', 'bigint') as [duration_μs]
	,	XQ.event_node.value('(data[@name="object_id"]/value)[1]', 'int') as [object_id]
	,	XQ.event_node.value('(data[@name="index_id"]/value)[1]', 'int') as [index_id]

ghotz

WITH cte AS
(
	SELECT	
		B1.[database_name]
	,	CASE B1.[type] WHEN 'D' THEN 'FULL' WHEN 'L' THEN 'LOG' END backup_type
	,	backup_size / 1024. / 1024. AS backup_size_mb
	,	compressed_backup_size / 1024. / 1024. AS compressed_backup_size_mb
	,	CAST(backup_finish_date as date) AS backup_date
	,	'Day ' + CAST(8 - DATEDIFF(day, backup_finish_date, GETDATE()) as varchar) AS backup_day
	FROM	msdb.dbo.backupset AS B1

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

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

-- 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

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

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

$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

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

# 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;" }