Skip to main content

Active Processes

This time I'll show you a script developed to cature running processes details.

There are a number of similar scripts out of there, but I've tried to use a single query based on DMVs only (no more sys.sysprocesses).

Exluding the deprecated sys.sysprocesses was the only tricky task, because there is no straight way to obtain the database_id unless we are using Sql Server 2012 which added this column to sys.dm_exec_sessions.

Fortunately sys.dm_tran_locks contains the information we need.

SET TRANSACTION isolation level READ uncommitted

         WHEN ER.total_elapsed_time > 0 THEN
         CONVERT(VARCHAR, (ER.total_elapsed_time - ER.total_elapsed_time %
         3600000) /
         + ':'
         + RIGHT('00' + CONVERT(VARCHAR, (ER.total_elapsed_time -
         % 60000) % 3600000 /
         60000), 2)
         + ':'
         + RIGHT('00' + CONVERT(VARCHAR, (ER.total_elapsed_time -
         % 1000) % 60000 / 1000), 2)
         + '.'
         + RIGHT('000' + CONVERT(VARCHAR, ER.total_elapsed_time % 1000), 3)
       NULLIF(ER.blocking_session_id, 0)
       Db_name(COALESCE(ER.database_id, TL.resource_database_id, 1))
       ES.reads                                                      total_reads
         WHEN Isnull(ER.open_transaction_count, 0) > 0 THEN
         WHEN ST.open_tran > 0 THEN ST.open_tran
       CASE Isnull(ER.transaction_isolation_level,
         WHEN 0 THEN 'Unspecified'
         WHEN 1 THEN 'ReadUncomitted'
         WHEN 2 THEN 'ReadCommitted'
         WHEN 3 THEN 'Repeatable'
         WHEN 4 THEN 'Serializable'
         WHEN 5 THEN 'Snapshot'
       CASE AT.transaction_type
         WHEN 1 THEN 'read/write'
         WHEN 2 THEN 'read-only'
         WHEN 3 THEN 'system'
         WHEN 4 THEN 'distributed'
       CASE AT.transaction_state
         WHEN 0 THEN 'not completely initialized'
         WHEN 1 THEN 'initialized but not started'
         WHEN 2 THEN 'active'
         WHEN 3 THEN 'read-only transaction has ended'
         WHEN 4 THEN 'commit initiated'
         WHEN 5 THEN 'prepared and waiting resolution'
         WHEN 6 THEN 'committed'
         WHEN 7 THEN 'being rolled back'
         WHEN 8 THEN 'been rolled back'
FROM   sys.dm_exec_connections EC
       INNER JOIN sys.dm_exec_sessions ES
               ON ES.session_id = EC.session_id
       LEFT JOIN sys.dm_exec_requests ER
              ON ER.session_id = EC.session_id
       LEFT JOIN (SELECT Count(*) open_tran,
                  FROM   sys.dm_tran_session_transactions XT
                  GROUP  BY XT.session_id) ST
              ON ST.session_id = ES.session_id
       LEFT JOIN sys.dm_tran_active_transactions AT
              ON AT.transaction_id = ER.transaction_id
       OUTER apply sys.Dm_exec_sql_text(Isnull(ER.sql_handle,
                                        EC.most_recent_sql_handle)) ET
       OUTER apply (SELECT TOP 1 XL.resource_database_id
                    FROM   sys.dm_tran_locks XL
                    WHERE  XL.resource_type = 'DATABASE'
                           AND XL.request_session_id = EC.session_id) TL
WHERE  EC.most_recent_sql_handle > 0x00
ORDER  BY ER.total_elapsed_time DESC,

This script will be included in the next version of SqlDbAid.


Popular posts from this blog

SqlDbAid DBA tool

Latest version (v2.6.1.1 - 2018-02-10)
SqlDbAid is an intuitive standalone application that will enable you to easily script database items and data.

SqlDbAid is free for personal and commercial usage.

Main Features SQL Server 2005+ supportText search inside code definition with highlitghtingDBA reports (missing indexes, missing foreign key indexes, indexes status, top queries, table MBytes)One file per object or single file scripts (tables, views, triggers, procedures, functions, indexes...)Select, Insert and Update scriptsTable data insert script creationTable/View data exportOffline database compare tool
Requirements .Net Framework 4.5+ For questions and suggestions write to Miken.

Database Permissions Script

This time I want to share a simple script to extract database permissions.
The code is based on sys.database_persmissions system view.
It basically contains the object ids, principal ids and related permissions (GRANT, DENY, ...).

SELECTCASEWHEN P.class IN(0,3)THEN P.class_desc ELSE O.type_desc END type_desc,CASEWHEN P.class IN(0,3)THEN''ELSE SCHEMA_NAME(O.schema_id)END object_schema,CASEWHEN P.class =3THEN SCHEMA_NAME(P.major_id)WHEN P.class =1THEN OBJECT_NAME(P.major_id)ELSE''END object_name, ISNULL(,'') column_name, P.class_desc, USER_NAME(P.grantee_principal_id) grantee, P.permission_name, P.state_desc, USER_NAME(P.grantor_principal_id) grantor,CASE P.state WHEN'W'THEN'GRANT'ELSE P.state_desc END+' '+CASEWHEN P.class =0THEN P.permission_name +' TO '+ QUOTENAME(USER_NAME(P.grantee_principal_id)) COLLATE database_default WHEN P.class =3THEN P.permission_name +' ON SCHEMA::'+ QUOTE…

Columns Inconsintency Check

Another hopefully useful script  from my toolbox.

Recently I had to check a quite denormalized database at the end of its development phase.

Frequently data types, length and other field attributes change during development, so I wrote a simple script that highlights possible mistakes (same column name but different definition).

UPDATE: system objects skipped

;WITH cte AS (SELECT c.NAME cl_name ,Schema_name(t.schema_id) +'.'+ t.NAME tb_name ,p.NAME [type],c.max_length ,c.scale ,c.PRECISION FROM sys.tables t INNER JOIN sys.columns c ON c.object_id = t.object_id INNER JOIN sys.types p ON p.user_type_id = c.user_type_id AND p.system_type_id = c.system_type_id WHERENOT( EXISTS (SELECT1FROM sys.extended_properties EP WHERE EP.major_id = t.object_id AND ='microsoft_database_tools_support')OR t.is_ms_shipped =1))SELECT DISTINCT x.*FROM cte x INNER JOIN cte y ON y.tb_name != x.tb_name AND y.cl_name = x.cl_name AND( y.max_length != x.max_length O…