Calculate Avg BLOB size
select *, gigabytes / 1024.0 as terabytes
from (
select *, megabytes / 1024.0 as gigabytes
from (
select *, kilobytes / 1024.0 as megabytes
from (
select *, bytes / 1024.0 as kilobytes
from (
select avg(datalength([Value])) as bytes
from <schema>.<yourtable>
) a
) b
) c
) d
EXEC msdb.dbo.sp_add_alert @name=N'1480 - AG Role Change',
@message_id=1480,
@severity=0,
@enabled=1,
@delay_between_responses=1,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'35264 - AG Data Movement - Suspended',
@message_id=35264,
@severity=0,
@enabled=1,
@delay_between_responses=1,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'35265 - AG Data Movement - Resumed',
@message_id=35265,
@severity=0,
@enabled=1,
@delay_between_responses=1,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'41404 - AG is offline',
@message_id=41404,
@severity=0,
@enabled=1,
@delay_between_responses=1,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'41405 - AG is not ready for automatic failover',
@message_id=41405,
@severity=0,
@enabled=1,
@delay_between_responses=1,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GOUSE ['<database_name, sysname, sample_database>'];
WITH CTE_LIST_GRANTED_SECURITY
(
UserName
, UserType
, DatabaseUserName
, Role
, PermissionType
, PermissionState
, ObjectType
, ObjectName
, ColumnName
)
AS
(
SELECT
[UserName] = CASE princ.[type]
WHEN 'S' THEN princ.[name]
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[DatabaseUserName] = princ.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM sys.database_principals princ
LEFT JOIN sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN sys.columns col ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
princ.[type] in ('S','U')
UNION
SELECT
[UserName] = CASE memberprinc.[type]
WHEN 'S' THEN memberprinc.[name]
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE memberprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[DatabaseUserName] = memberprinc.[name],
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM sys.database_role_members members
JOIN sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.columns col on col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
SELECT
[UserName] = '{All Users}',
[UserType] = '{All Users}',
[DatabaseUserName] = '{All Users}',
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM sys.database_principals roleprinc
LEFT JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.columns col on col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
INNER JOIN sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
roleprinc.[type] = 'R' AND
roleprinc.[name] = 'public' AND
obj.is_ms_shipped = 0
)
SELECT
UserName
, UserType
, DatabaseUserName
, Role
, PermissionType
, PermissionState
, ObjectType
, ObjectName
, ColumnName
FROM
CTE_LIST_GRANTED_SECURITY
ORDER BY
UserName,
UserType,
PermissionType,
PermissionState
GOGet the status of the last package execution
USE SSISDB
GO
;WITH CTE_STATUS (s_id, s_created, s_active)
AS
(SELECT s_id, s_created, s_active
FROM (VALUES
(1, 'Created', 'Active')
,(2, 'Running', 'Active')
,(3, 'Canceled', 'Failed')
,(4, 'Failed', 'Failed')
,(5, 'Pending', 'Active')
,(6, 'Ended unexpectedly', 'Failed')
,(7, 'Succeeded', 'Succeeded')
,(8, 'Stopping', 'Active')
,(9, 'Completed', 'Failed')) AS S(s_id, s_created, s_active)
)
SELECT TOP (1)
E.execution_id
, E.folder_name
, E.project_name
, E.package_name
, E.executed_as_name
, E.created_time
, E.status
, s.s_active
, cast(E.start_time as datetime) as start_time
, cast(E.end_time as datetime) as end_time
, DATEDIFF(minute,cast(E.start_time as datetime), isnull(cast(e.end_time as datetime), GETDATE())) as duration_minutes
, E.caller_name
, E.process_id
, E.stopped_by_name
, E.server_name
, E.machine_name
, E.total_physical_memory_kb
, E.available_physical_memory_kb
, E.total_page_file_kb
, E.available_page_file_kb
, E.cpu_count
, F.folder_id
, F.name
, F.description
, F.created_by_name
, F.created_time
, P.project_id
, P.folder_id
FROM catalog.executions AS E
INNER JOIN ssisdb.catalog.folders AS F ON F.name = E.folder_name
INNER JOIN SSISDB.catalog.projects AS P
ON P.folder_id = F.folder_id
AND P.name = E.project_name
LEFT JOIN CTE_STATUS AS S on E.status = s.s_id
WHERE E.package_name = '<your pacakage>'
ORDER BY E.execution_id DESC
GO