ronascentes

SELECT CONVERT(xml, event_data).query('/event/data/value/child::*') as xml1,
       CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime') as Execution_Time
into #temp
FROM sys.fn_xe_file_target_read_file('system_health*.xel', null, null, null)
WHERE object_name like 'xml_deadlock_report'

select Row_Number() OVER(ORDER BY execution_time desc) AS RowNumber,*  into #temp2 from #temp

ronascentes

USE master
GO
DECLARE @memoryNeeded INT = 16
SELECT percentNeeded = @memoryNeeded / 
		ROUND(((committed_target_kb/1024)/1024) * 
		(CASE  
			WHEN (committed_target_kb/1024)/1024 <= 8 THEN 0.7
			WHEN (committed_target_kb/1024)/1024 <= 16 THEN 0.75
			WHEN (committed_target_kb/1024)/1024 <= 32 THEN 0.8
			WHEN (committed_target_kb/1024)/1024 <= 96 THEN 0.85

ronascentes

SELECT ag.name as "Availability Group", ar.replica_server_name as "When Primary Replica Is",
        rl.routing_priority as "Routing Priority", ar2.replica_server_name as "RO Routed To",
        ar.secondary_role_allow_connections_desc, ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
        inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id
        inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id
        inner join sys.availability_groups ag on ar.group_id = ag.group_id
ORDER BY ag.name, ar.replica_server_name, rl.routing_priority

ronascentes

# download the module to local folder
Invoke-WebRequest -Uri powershellgallery.com/api/v2/package/sqlserver -Out C:\temp\sqlserver.zip

# unzip the file
Expand-Archive -LiteralPath 'C:\temp\sqlserver.zip' -DestinationPath C:\temp\sqlserver

# remove powershell gallery related files
Push-Location C:\temp\sqlserver
Remove-Item .\_rels\ -Recurse -Force
Remove-Item .\package\ -Recurse -Force

ronascentes

Get-winEvent -ComputerName <server_name> -filterHashTable @{logname ='Microsoft-Windows-FailoverClustering/Operational'; id=1641}| ft -AutoSize -Wrap

ronascentes

SET NOCOUNT ON;
DECLARE @body nvarchar(max)

SET @body = N'
<style type=''text/css''>
	TABLE{border-width: 1px;border-style: solid;background-color: #E8E8E8; border-color: black;border-collapse: collapse;}
	TH{color:black; border-width: 1px;font-size: 11px; padding: 3px;border-style: solid;border-color: black;}
	TD{color:black; border-width: 1px;font-size: 11px; padding: 3px;border-style: solid;border-color: black;}
</style>' +
N'<h5> *** This is an automatic email, please do not reply *** </h5>' +

ronascentes

try{
  Get-Childitem c:\Foo -ErrorAction stop
}
catch [System.Management.Automation.ItemNotFoundException]{
  'oops, I guess that folder was not there'
}

ronascentes

;WITH RingBufferConnectivity as
(   SELECT
        records.record.value('(/Record/@id)[1]', 'int') AS [RecordID],
        records.record.value('(/Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(max)') AS [RecordType],
        records.record.value('(/Record/ConnectivityTraceRecord/RecordTime)[1]', 'datetime') AS [RecordTime],
        records.record.value('(/Record/ConnectivityTraceRecord/SniConsumerError)[1]', 'int') AS [Error],
        records.record.value('(/Record/ConnectivityTraceRecord/State)[1]', 'int') AS [State],
        records.record.value('(/Record/ConnectivityTraceRecord/Spid)[1]', 'int') AS [Spid],
        records.record.value('(/Record/ConnectivityTraceRecord/RemoteHost)[1]', 'varchar(max)') AS [RemoteHost],
        records.record.value('(/Record/ConnectivityTraceRecord/RemotePort)[1]', 'varchar(max)') AS [RemotePort],

ronascentes

SELECT DB_NAME(database_id) DatabaseName,
OBJECT_NAME(object_id) ProcedureName,
cached_time, last_execution_time, execution_count,
total_elapsed_time/execution_count AS avg_elapsed_time,
type_desc, datetime=getdate()
FROM sys.dm_exec_procedure_stats
where database_id=6
and OBJECT_NAME(object_id)='ProcExtendedSessionUpsertV5'

ronascentes

$ipAddress= "192.168.1.54"
[System.Net.Dns]::GetHostByAddress($ipAddress).Hostname