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

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

USE msdb
GO

WITH cte AS
(
	SELECT
		[database_name], [type] AS backup_type
	,	backup_size, compressed_backup_size
	,	backup_start_date, backup_finish_date
	,	CAST(backup_finish_date - backup_start_date AS time(0)) elapsed

ghotz

SELECT
	DB_NAME(database_id) AS database_name
,	SUM(CASE WHEN type_desc = 'ROWS' THEN size / 128.8 ELSE 0 END) space_data_mb
,	SUM(CASE WHEN type_desc = 'LOG' THEN size / 128.8 ELSE 0 END) space_log_mb
,	SUM(CASE WHEN type_desc NOT IN('ROWS', 'LOG') THEN size / 128.8 ELSE 0 END) space_other_mb
FROM	sys.master_files
GROUP BY	
	DB_NAME(database_id)
ORDER BY space_data_mb DESC;

ghotz

-- verify IFI status

-- check with DMV where available
SELECT
	@@SERVERNAME as server_name
,	servicename AS [service_name]
,	instant_file_initialization_enabled
FROM	sys.dm_server_services;

-- check with undocumented SP otherwise

ghotz

<#  
.SYNOPSIS  
	Save a Relive video given the URL for an activity
.DESCRIPTION
    Quick hack to save Relive videos from the URL sent via e-mail after creating it.
    Along with the video, a file with .url extension is created with the original URL for future reference.
    
    Filenames are standardized replacing invalid characters with _ and using the following name template:
        YYYYmmdd - title of the video.{mp4|url}
    

ghotz

# Extract DV AVI Creation date/time, set it to file creation/modified date time and add it as a prefix to filename
# Needs Medianfo CLI executable from https://mediaarea.net/en/MediaInfo/Download/Windows
# Note: -LiteralPath is used because directory names have [] characters
dir -LiteralPath 'Z:\Videos' -Include *.avi | % {
    $CreationDate = & C:\utils\mediainfo\MediaInfo.exe --Inform="General;%Recorded_Date%" "$($_.FullName)";
    $_.CreationTime = [datetime]$CreationDate;
    $_.LastWriteTime = [datetime]$CreationDate;
    $prefix = $CreationDate -replace '-|:|.000','' -replace " ", "-";
    Rename-Item -LiteralPath $_.FullName ($prefix + '-' + $_.Name);
}

ghotz

EXEC sp_executesql N'
	DECLARE @sqlstmt nvarchar(max) = (
	SELECT
		N''SELECT	UT.SYS_CHANGE_VERSION, UT.SYS_CHANGE_OPERATION, STRING_AGG(CAST(UT.COLUMN_NAME AS nvarchar(max)), N'''','''') AS CHANGED_COLUMNS_LIST ''
	+	N'' FROM (SELECT SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION,''
	+	STRING_AGG(N''CHANGE_TRACKING_IS_COLUMN_IN_MASK('' + CAST(C1.column_id as nvarchar(max)) + N'', SYS_CHANGE_COLUMNS) AS ['' + C1.[name] + '']'', N'','')
	+	N'' FROM CHANGETABLE(CHANGES '' + @table_name + '', '' + CAST(@version as nvarchar) + '') AS CT) AS PT''
	+	N'' UNPIVOT (COLUMN_CHANGED FOR COLUMN_NAME IN (''
	+	STRING_AGG(CAST(C1.[name] as nvarchar(max)), N'','')
	+	N'')) AS UT WHERE UT.COLUMN_CHANGED = 1 GROUP BY UT.SYS_CHANGE_VERSION, UT.SYS_CHANGE_OPERATION'' AS sqlstmt

ghotz

WITH DefaultTrace AS
(
	SELECT
		DF1.DatabaseName AS database_name
	,	DF1.[Filename] AS database_file_name
	,	TE1.[name] AS event_type
	,	DF1.StartTime AS event_start_time
	,	DF1.EndTime AS event_end_time
	,	CAST(DF1.Duration / 1000. AS bigint) AS duration_ms
	,	CAST(DF1.Duration / 1000000. AS decimal(10, 3)) AS duration_sec

ghotz

DECLARE	@OperatorName	sysname = N'OperatorName';
DECLARE	@MailProfile	sysname = N'MailProfile'
DECLARE	@DatabaseName	sysname = N'DatabaseName';
DECLARE	@MaxMinutesGap	int = 4 * 60;

DECLARE	@MinutesGap	int = (
	SELECT	DATEDIFF(minute, MAX(backup_finish_date), GETDATE())
	FROM	msdb.dbo.backupset AS B1
	JOIN	msdb.dbo.backupmediaset AS B2
	ON		B1.media_set_id = B2.media_set_id