ronascentes

DECLARE @session_id INT, @sql NVARCHAR (4000);
DECLARE database_curs CURSOR FAST_FORWARD FOR 
                                            SELECT c.session_id
                                            FROM sys.dm_exec_connections AS c  
                                            JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
                                            WHERE c.session_id <> @@SPID AND s.is_user_process = 1;
OPEN database_curs;    
FETCH NEXT FROM database_curs INTO @session_id;
WHILE (@@fetch_status <> -1)
BEGIN

ronascentes

;WITH StatTables AS(
    SELECT so.schema_id AS 'schema_id', so.name AS 'TableName', so.object_id AS 'object_id', ISNULL(sp.rows,0) AS 'ApproximateRows', ISNULL(sp.modification_counter,0) AS 'RowModCtr'
    FROM sys.objects so (NOLOCK) JOIN sys.stats st (NOLOCK) ON so.object_id=st.object_id
    CROSS APPLY sys.dm_db_stats_properties(so.object_id, st.stats_id) AS sp
    WHERE so.is_ms_shipped = 0 AND st.stats_id<>0
    AND so.object_id NOT IN (SELECT major_id FROM sys.extended_properties (NOLOCK) WHERE name = N'microsoft_database_tools_support')
),
StatTableGrouped AS(
    SELECT ROW_NUMBER() OVER(ORDER BY TableName) AS seq1, ROW_NUMBER() OVER(ORDER BY TableName DESC) AS seq2, TableName, cast(Max(ApproximateRows) AS bigint) AS ApproximateRows,
      cast(Max(RowModCtr) AS bigint) AS RowModCtr, count(*) AS StatCount, schema_id,object_id

ronascentes

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

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

;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

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

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

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

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

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