Tested on server version: SQL Server 2016 Developer Edition
Reference info accurate on: January, 2018

One way to identify active tractions, is using DBCC OPENTRAN. DBCC OPENTRAN helps to identify active transactions that may be preventing log truncation. DBCC OPENTRAN displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the transaction log of the specified database. Results are displayed only if there is an active transaction that exists in the log or if the database contains replication information. An informational message is displayed if there are no active transactions in the log.

This method requires membership in the sysadmin fixed server role or the db_owner fixed database role.

Note: DBCC OPENTRAN is not supported for non- SQL Server Publishers.

DBCC OPENTRAN

Use the above command to get the oldest active transaction. This is what the output will look like. I ran this on 10/24, so this oldest transaction is a couple days old.

Transaction information for database 'ColumnStats'.

Oldest active transaction:
    SPID (server process ID): 55
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (76:12857:255)
    Start time    : Oct 21 2017 10:53:42:970PM
    SID           : 0x010500000000000515000000c9dcc61262e2e3b646e13723e9030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Another way to identify active transactions for the instance of SQL Server is with the [sys].[dm_tran_active_transactions] dynamic management view.

To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_tran_active_transactions.

SELECT [transaction_id]
      ,[name]
      ,[transaction_begin_time]
      ,[transaction_type]
      ,[transaction_uow]
      ,[transaction_state]
      ,[transaction_status]
      ,[transaction_status2]
      ,[dtc_state]
      ,[dtc_status]
      ,[dtc_isolation_level]
      ,[filestream_transaction_id]
  FROM [sys].[dm_tran_active_transactions]
 
 
--- Snippet #B562BC454B93B17E316D55E7F9040A45 (TSQL) 
--- The latest version of this code is at:  
--- https://www.sevendaysofschema.com/tsql-examples/identify-active-transactions/

Questions or comments about this script? Be a part of the conversation. It only takes a minute to post a comment.

In this Script

DBCC OPENTRAN     SELECT     FROM     DBCC     USER    

DBCC OPENTRAN (Transact-SQL)

DBCC OPENTRAN helps to identify active transactions that may be preventing log truncation. DBCC OPENTRAN displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the transaction log of the specified database. Results are displayed only if there is an active transaction that exists in the log or if the database contains replication information. An informational message is displayed if there are no active transactions in the log.

NOTE: DBCC OPENTRAN is not supported for non-SQL Server Publishers.

Permissions: Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

DBCC OPENTRAN   
[   
    ( [ database_name | database_id | 0 ] ) ]  
    { [ WITH TABLERESULTS ]  
      [ , [ NO_INFOMSGS ] ]  
    }  
]

/* 

 Result Sets

*/


No active open transactions.  
DBCC execution completed. If DBCC printed error messages, contact your system administrator.  





/* 

 A. Returning the oldest active transaction

*/

  
CREATE TABLE T1(Col1 int, Col2 char(3));  
GO  
BEGIN TRAN  
INSERT INTO T1 VALUES (101, 'abc');  
GO  
DBCC OPENTRAN;  
ROLLBACK TRAN;  
GO  
DROP TABLE T1;  
GO  





/* 

 B. Specifying the WITH TABLERESULTS option

*/

  
-- 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

Related Topics:   BEGIN TRANSACTION   COMMIT TRANSACTION   DB_ID   DBCC   ROLLBACK TRANSACTION

T-SQL Database Console Commands:   DBCC CHECKALLOC   DBCC CHECKCATALOG   DBCC CHECKCONSTRAINTS   DBCC CHECKDB   DBCC CHECKFILEGROUP   DBCC CHECKIDENT   DBCC CHECKTABLE   DBCC CLEANTABLE   DBCC DBREINDEX   DBCC DROPCLEANBUFFERS   DBCC FLUSHAUTHCACHE   DBCC FREEPROCCACHE   DBCC FREESESSIONCACHE   DBCC FREESYSTEMCACHE   DBCC HELP   DBCC INDEXDEFRAG   DBCC INPUTBUFFER   DBCC OUTPUTBUFFER   DBCC PDW_SHOWEXECUTIONPLAN   DBCC PDW_SHOWPARTITIONSTATS

TOP

SELECT Clause (Transact-SQL)

Retrieves one or more rows or columns from the database. The UNION, EXCEPT, and INTERSECT operators can be used between queries to combine or compare their results into one result set. <Rick’s Tip> The SELECT syntax used for Azure SQL Data Warehouse and Parallel Data Warehouse differs from that used for SQL Server and Azure SQL Database.

Permissions: Selecting data requires SELECT permission on the table or view, which could be inherited from a higher scope such as SELECT permission on the schema or CONTROL permission on the table. Or requires membership in the db_datareader or db_owner fixed database roles, or the sysadmin fixed server role. Creating a new table using SELECTINTO also requires both the CREATETABLE permission, and the ALTERSCHEMA permission on the schema that owns the new table.

-- Syntax for SQL Server and Azure SQL Database  
  
<SELECT statement> ::=    
    [ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> [,...n] ] } ]  
    <query_expression>   
    [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }   
  [ ,...n ] ]   
    [ <FOR Clause>]   
    [ OPTION ( <query_hint> [ ,...n ] ) ]   
<query_expression> ::=   
    { <query_specification> | ( <query_expression> ) }   
    [  { UNION [ ALL ] | EXCEPT | INTERSECT }  
        <query_specification> | ( <query_expression> ) [...n ] ]   
<query_specification> ::=   
SELECT [ ALL | DISTINCT ]   
    [TOP ( expression ) [PERCENT] [ WITH TIES ] ]   
    < select_list >   
    [ INTO new_table ]   
    [ FROM { <table_source> } [ ,...n ] ]   
    [ WHERE <search_condition> ]   
    [ <GROUP BY> ]   
    [ HAVING < search_condition > ]   
  
  
  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
[ WITH <common_table_expression> [ ,...n ] ]  
SELECT <select_criteria>  
[;]  
  
<select_criteria> ::=  
    [ TOP ( top_expression ) ]   
    [ ALL | DISTINCT ]   
    { * | column_name | expression } [ ,...n ]   
    [ FROM { table_source } [ ,...n ] ]  
    [ WHERE <search_condition> ]   
    [ GROUP BY <group_by_clause> ]   
    [ HAVING <search_condition> ]   
    [ ORDER BY <order_by_expression> ]  
    [ OPTION ( <query_option> [ ,...n ] ) ]

/* 

 A. Using SELECT to retrieve rows and columns

*/

  
SELECT *  
FROM DimEmployee  
ORDER BY LastName;  





/* 

 B. Using SELECT with column headings and calculations

*/

  
SELECT FirstName, LastName, BaseRate, BaseRate * 40 AS GrossPay  
FROM DimEmployee  
ORDER BY LastName;  





/* 

 C. Using DISTINCT with SELECT

*/

  
SELECT DISTINCT Title  
FROM DimEmployee  
ORDER BY Title;  





/* 

 D. Using GROUP BY

*/

  
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY OrderDateKey  
ORDER BY OrderDateKey;  





/* 

 E. Using GROUP BY with multiple groups

*/

  

SELECT OrderDateKey, PromotionKey, AVG(SalesAmount) AS AvgSales, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY OrderDateKey, PromotionKey  
ORDER BY OrderDateKey;   





/* 

 F. Using GROUP BY and WHERE

*/

  
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
WHERE OrderDateKey > '20020801'  
GROUP BY OrderDateKey  
ORDER BY OrderDateKey;  





/* 

 G. Using GROUP BY with an expression

*/

  
SELECT SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY (OrderDateKey * 10);  





/* 

 H. Using GROUP BY with ORDER BY

*/

  
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY OrderDateKey  
ORDER BY OrderDateKey;  





/* 

 I. Using the HAVING clause

*/

  
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY OrderDateKey  
HAVING OrderDateKey > 20010000  
ORDER BY OrderDateKey;

Related Topics:   Hints   SELECT Examples

T-SQL Query Elements:   CONTAINS   EXPLAIN   FREETEXT   FROM   GROUP BY   HAVING   IS NULL   PIVOT and UNPIVOT   PREDICT   READTEXT   TOP   UPDATE   UPDATETEXT   WHERE   WRITETEXT

TOP

FROM (Transact-SQL)

Specifies the tables, views, derived tables, and joined tables used in DELETE, SELECT, and UPDATE statements in SQL Server 2017. In the SELECT statement, the FROM clause is required except when the select list contains only constants, variables, and arithmetic expressions (no column names).

Permissions: Requires the permissions for the DELETE, SELECT, or UPDATE statement.

-- Syntax for SQL Server and Azure SQL Database  
  
[ FROM { <table_source> } [ ,...n ] ]   
<table_source> ::=   
{  
    table_or_view_name [ [ AS ] table_alias ]   
        [ <tablesample_clause> ]   
        [ WITH ( < table_hint > [ [ , ]...n ] ) ]   
    | rowset_function [ [ AS ] table_alias ]   
        [ ( bulk_column_alias [ ,...n ] ) ]   
    | user_defined_function [ [ AS ] table_alias ]  
    | OPENXML <openxml_clause>   
    | derived_table [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ]   
    | <joined_table>   
    | <pivoted_table>   
    | <unpivoted_table>  
    | @variable [ [ AS ] table_alias ]  
    | @variable.function_call ( expression [ ,...n ] )   
        [ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]  
    | FOR SYSTEM_TIME <system_time>   
}  
<tablesample_clause> ::=  
    TABLESAMPLE [SYSTEM] ( sample_number [ PERCENT | ROWS ] )   
        [ REPEATABLE ( repeat_seed ) ]   
  
<joined_table> ::=   
{  
    <table_source> <join_type> <table_source> ON <search_condition>   
    | <table_source> CROSS JOIN <table_source>   
    | left_table_source { CROSS | OUTER } APPLY right_table_source   
    | [ ( ] <joined_table> [ ) ]   
}  
<join_type> ::=   
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]  
    JOIN  
  
<pivoted_table> ::=  
    table_source PIVOT <pivot_clause> [ [ AS ] table_alias ]  
  
<pivot_clause> ::=  
        ( aggregate_function ( value_column [ [ , ]...n ])   
        FOR pivot_column   
        IN ( <column_list> )   
    )   
  
<unpivoted_table> ::=  
    table_source UNPIVOT <unpivot_clause> [ [ AS ] table_alias ]  
  
<unpivot_clause> ::=  
    ( value_column FOR pivot_column IN ( <column_list> ) )   
  
<column_list> ::=  
    column_name [ ,...n ]   
  
<system_time> ::=  
{  
       AS OF <date_time>  
    |  FROM <start_date_time> TO <end_date_time>  
    |  BETWEEN <start_date_time> AND <end_date_time>  
    |  CONTAINED IN (<start_date_time> , <end_date_time>)   
    |  ALL  
}  
  
    <date_time>::=  
        <date_time_literal> | @date_time_variable  
  
    <start_date_time>::=  
        <date_time_literal> | @date_time_variable  
  
    <end_date_time>::=  
        <date_time_literal> | @date_time_variable  
  
  
  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
FROM { <table_source> [ ,...n ] }  
  
<table_source> ::=   
{  
    [ database_name . [ schema_name ] . | schema_name . ] table_or_view_name [ AS ] table_or_view_alias  
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]  
    | <joined_table>  
}  
  
<joined_table> ::=   
{  
    <table_source> <join_type> <table_source> ON search_condition   
    | <table_source> CROSS JOIN <table_source> 
    | left_table_source { CROSS | OUTER } APPLY right_table_source   
    | [ ( ] <joined_table> [ ) ]   
}  
  
<join_type> ::=   
    [ INNER ] [ <join hint> ] JOIN  
    | LEFT  [ OUTER ] JOIN  
    | RIGHT [ OUTER ] JOIN  
    | FULL  [ OUTER ] JOIN  
  
<join_hint> ::=   
    REDUCE  
    | REPLICATE  
    | REDISTRIBUTE

/* 

 Arguments

*/


SELECT p.ProductID, v.BusinessEntityID  
FROM Production.Product AS p   
JOIN Purchasing.ProductVendor AS v  
ON (p.ProductID = v.ProductID);  
  





/* 

 A. Using a simple FROM clause

*/

    
SELECT TerritoryID, Name  
FROM Sales.SalesTerritory  
ORDER BY TerritoryID ;  





/* 

 B. Using the TABLOCK and HOLDLOCK optimizer hints

*/

    
BEGIN TRAN  
SELECT COUNT(*)   
FROM HumanResources.Employee WITH (TABLOCK, HOLDLOCK) ;  





/* 

 C. Using the SQL-92 CROSS JOIN syntax

*/

wql    
SELECT e.BusinessEntityID, d.Name AS Department  
FROM HumanResources.Employee AS e  
CROSS JOIN HumanResources.Department AS d  
ORDER BY e.BusinessEntityID, d.Name ;  





/* 

 D. Using the SQL-92 FULL OUTER JOIN syntax

*/

  
-- The OUTER keyword following the FULL keyword is optional.  
SELECT p.Name, sod.SalesOrderID  
FROM Production.Product AS p  
FULL OUTER JOIN Sales.SalesOrderDetail AS sod  
ON p.ProductID = sod.ProductID  
ORDER BY p.Name ;  





/* 

 E. Using the SQL-92 LEFT OUTER JOIN syntax

*/

    
SELECT p.Name, sod.SalesOrderID  
FROM Production.Product AS p  
LEFT OUTER JOIN Sales.SalesOrderDetail AS sod  
ON p.ProductID = sod.ProductID  
ORDER BY p.Name ;  





/* 

 F. Using the SQL-92 INNER JOIN syntax

*/

    
-- By default, SQL Server performs an INNER JOIN if only the JOIN   
-- keyword is specified.  
SELECT p.Name, sod.SalesOrderID  
FROM Production.Product AS p  
INNER JOIN Sales.SalesOrderDetail AS sod  
ON p.ProductID = sod.ProductID  
ORDER BY p.Name ;  





/* 

 G. Using the SQL-92 RIGHT OUTER JOIN syntax

*/

    
SELECT st.Name AS Territory, sp.BusinessEntityID  
FROM Sales.SalesTerritory AS st   
RIGHT OUTER JOIN Sales.SalesPerson AS sp  
ON st.TerritoryID = sp.TerritoryID ;  





/* 

 H. Using HASH and MERGE join hints

*/

    
SELECT p.Name AS ProductName, v.Name AS VendorName  
FROM Production.Product AS p   
INNER MERGE JOIN Purchasing.ProductVendor AS pv   
ON p.ProductID = pv.ProductID  
INNER HASH JOIN Purchasing.Vendor AS v  
ON pv.BusinessEntityID = v.BusinessEntityID  
ORDER BY p.Name, v.Name ;  





/* 

 I. Using a derived table

*/

    
SELECT RTRIM(p.FirstName) + ' ' + LTRIM(p.LastName) AS Name, d.City  
FROM Person.Person AS p  
INNER JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID   
INNER JOIN  
   (SELECT bea.BusinessEntityID, a.City   
    FROM Person.Address AS a  
    INNER JOIN Person.BusinessEntityAddress AS bea  
    ON a.AddressID = bea.AddressID) AS d  
ON p.BusinessEntityID = d.BusinessEntityID  
ORDER BY p.LastName, p.FirstName;  





/* 

 J. Using TABLESAMPLE to read data from a sample of rows in a table

*/

    
SELECT *  
FROM Sales.Customer TABLESAMPLE SYSTEM (10 PERCENT) ;  





/* 

 K. Using APPLY

*/


SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary  
FROM Departments d CROSS APPLY dbo.GetReports(d.DeptMgrID) ;  





/* 

 L. Using CROSS APPLY

*/


USE master;  
GO  
SELECT dbid, object_id, query_plan   
FROM sys.dm_exec_cached_plans AS cp   
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);   
GO  





/* 

 M. Using FOR SYSTEM_TIME

*/


SELECT DepartmentNumber,   
    DepartmentName,   
    ManagerID,   
    ParentDepartmentNumber   
FROM DEPARTMENT  
FOR SYSTEM_TIME AS OF '2014-01-01'  
WHERE ManagerID = 5;





/* 

 N. Using the INNER JOIN syntax

*/


-- Uses AdventureWorks  
  
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName  
FROM FactInternetSales AS fis 
INNER JOIN DimProduct AS dp  
    ON dp.ProductKey = fis.ProductKey;  





/* 

 O. Using the LEFT OUTER JOIN and RIGHT OUTER JOIN syntax

*/


-- Uses AdventureWorks  
  
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName  
FROM FactInternetSales AS fis 
LEFT OUTER JOIN DimProduct AS dp  
    ON dp.ProductKey = fis.ProductKey;  





/* 

 P. Using the FULL OUTER JOIN syntax

*/


-- Uses AdventureWorks  
  
SELECT dst.SalesTerritoryKey, dst.SalesTerritoryRegion, fis.SalesOrderNumber  
FROM DimSalesTerritory AS dst 
FULL OUTER JOIN FactInternetSales AS fis  
    ON dst.SalesTerritoryKey = fis.SalesTerritoryKey  
ORDER BY fis.SalesOrderNumber;  





/* 

 Q. Using the CROSS JOIN syntax

*/


-- Uses AdventureWorks  
  
SELECT dst.SalesTerritoryKey, fis.SalesOrderNumber  
FROM DimSalesTerritory AS dst 
CROSS JOIN FactInternetSales AS fis  
ORDER BY fis.SalesOrderNumber;  





/* 

 R. Using a derived table

*/


-- Uses AdventureWorks  
  
SELECT CustomerKey, LastName  
FROM  
   (SELECT * FROM DimCustomer  
    WHERE BirthDate > '01/01/1970') AS DimCustomerDerivedTable  
WHERE LastName = 'Smith'  
ORDER BY LastName;  





/* 

 S. REDUCE join hint example

*/


-- Uses AdventureWorks  
  
EXPLAIN SELECT SalesOrderNumber  
FROM  
   (SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName  
    FROM DimProduct AS dp   
      INNER REDUCE JOIN FactInternetSales AS fis   
          ON dp.ProductKey = fis.ProductKey  
   ) AS dTable  
ORDER BY SalesOrderNumber;  





/* 

 T. REPLICATE join hint example

*/


-- Uses AdventureWorks  
  
EXPLAIN SELECT SalesOrderNumber  
FROM  
   (SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName  
    FROM DimProduct AS dp   
      INNER REPLICATE JOIN FactInternetSales AS fis  
          ON dp.ProductKey = fis.ProductKey  
   ) AS dTable  
ORDER BY SalesOrderNumber;  





/* 

 U. Using the REDISTRIBUTE hint to guarantee a Shuffle move for a distribution incompatible join

*/


-- Uses AdventureWorks  
  
EXPLAIN  
SELECT dp.ProductKey, fis.SalesOrderNumber, fis.TotalProductCost  
FROM DimProduct AS dp 
INNER REDISTRIBUTE JOIN FactInternetSales AS fis  
    ON dp.ProductKey = fis.ProductKey;

Related Topics:   CONTAINSTABLE   DELETE   FREETEXTTABLE   INSERT   OPENQUERY   OPENROWSET   Operators   UPDATE   WHERE

T-SQL Query Elements:   CONTAINS   EXPLAIN   FREETEXT   GROUP BY   HAVING   IS NULL   PIVOT and UNPIVOT   PREDICT   READTEXT   TOP   UPDATE   UPDATETEXT   WHERE   WRITETEXT

TOP

DBCC (Transact-SQL)

The T-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.

Database Console Command statements are grouped into the following categories.

Database Console Commands:   CHECKPOINT   KILL   KILL QUERY NOTIFICATION SUBSCRIPTION   KILL STATS JOB   RECONFIGURE   SHUTDOWN

TOP

USER (Transact-SQL)

Allows a system-supplied value for the database user name of the current user to be inserted into a table when no default value is specified.

USER

/* 

 A. Using USER to return the database user name

*/

  
DECLARE @usr char(30)  
SET @usr = user  
SELECT 'The current user''s database username is: '+ @usr  
GO  





/* 

 B. Using USER with DEFAULT constraints

*/

  
USE AdventureWorks2012;  
GO  
CREATE TABLE inventory22  
(  
 part_id int IDENTITY(100, 1) NOT NULL,  
 description varchar(30) NOT NULL,  
 entry_person varchar(30) NOT NULL DEFAULT USER   
)  
GO  
INSERT inventory22 (description)  
VALUES ('Red pencil')  
INSERT inventory22 (description)  
VALUES ('Blue pencil')  
INSERT inventory22 (description)  
VALUES ('Green pencil')  
INSERT inventory22 (description)  
VALUES ('Black pencil')  
INSERT inventory22 (description)  
VALUES ('Yellow pencil')  
GO  





/* 

 C. Using USER in combination with EXECUTE AS

*/

  
SELECT USER;  
GO  
EXECUTE AS USER = 'Mario';  
GO  
SELECT USER;  
GO  
REVERT;  
GO  
SELECT USER;  
GO

Related Topics:   ALTER TABLE   CREATE TABLE   CURRENT_TIMESTAMP   CURRENT_USER   Security Functions   SESSION_USER   SYSTEM_USER   USER_NAME

T-SQL Functions:   CAST and CONVERT   CERT_ID   CERTPROPERTY   CHOOSE   COLLATIONPROPERTY   CRYPT_GEN_RANDOM   HAS_DBACCESS   IIF   LOGINPROPERTY   PUBLISHINGSERVERNAME   SESSIONPROPERTY   TERTIARY_WEIGHTS   TEXTPTR   TEXTVALID

About the Archive This script is part of my personal code snippet library, which I’ve been posting to this site using the WordPress automation processes I’m working on.