Nov 08 2017

Verifica le connessioni aperte nel DB

Category: TecnologiaAntonio @ 23:35


if object_id (‘tempdb..#OpenTranStatus’) is not null
        drop table #OpenTranStatus;
 
— Create the temporary table to accept the results.
CREATE TABLE #OpenTranStatus (
   ActiveTransaction varchar(25 ),
   Details sql_variant
   );
— Execute the command, putting the results in the table.
INSERT INTO #OpenTranStatus
   EXEC ( ‘DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS’ );
 
— Display the results.
SELECT * FROM #OpenTranStatus ;
GO


Nov 08 2017

SqlServer – Processi in esecuzione e in stato di blocco

Category: TecnologiaAntonio @ 23:30


Per verificare cosa attualmente è in esecuzione su DB
SELECT SUBSTRING (st. text, ( r .statement_start_offset / 2 ) + 1 ,
              ( ( CASE WHEN r .statement_end_offset <= 0
                       THEN DATALENGTH (st. text)
              ELSE r .statement_end_offset END
       r.statement_start_offset ) / 2 ) + 1 ) AS statement_text
     ,*
FROM   sys .dm_exec_requests r
       CROSS APPLY sys. dm_exec_sql_text(sql_handle ) st
where st .text not like ‘%SUBSTRING(st.text, ( r.statement_start_offset / 2 ) + 1%


Per verificare i processi in esecuzione ma che stanno bloccando altri processi
SELECT session_id , command , ‘BLOCCATO’, blocking_session_id , wait_type , wait_time , wait_resource, t. TEXT
FROM sys .dm_exec_requests
CROSS apply sys. dm_exec_sql_text( sql_handle ) AS t
WHERE session_id > 50
AND blocking_session_id > 0
UNION
SELECT session_id , , ‘IN ESECUZIONE’, , , , , t .TEXT
FROM sys .dm_exec_connections
CROSS apply sys. dm_exec_sql_text( most_recent_sql_handle ) AS t
WHERE session_id IN (SELECT blocking_session_id
                    FROM sys .dm_exec_requests
                    WHERE blocking_session_id > 0