Details of current running query

Use below query to check the details of current running Query on SQL Server.

SELECT R.Session_ID, R.Request_ID AS [Session Request ID], CASE WHEN S.Login_Name = S.Original_Login_Name THEN S.Login_Name ELSE S.Login_Name + ' (' + S.Original_Login_Name + ')' END AS [Login Name], S.Program_Name AS [Program Name], DB_Name(R.Database_ID) AS [Database Name], R.Status, S.Host_Name AS [Host Name], C.Client_Net_Address AS [Client Net Address], R.Command, ST.Text AS [Query Text], QP.Query_Plan AS [XML Query Plan], R.Last_Wait_Type AS [Last Wait Type], R.Wait_Type AS [Current Wait Type], R.Blocking_Session_ID AS [Blocking Session ID], R.Row_Count AS [Row Count], 
R.Granted_Query_Memory AS [Granted Query Memory], R.Open_Transaction_Count AS [Open Transaction Count], R.User_ID AS [User ID], R.Percent_Complete AS [% Complete], R.Start_Time AS [Start Time], R.CPU_Time AS [CPU Time], R.Estimated_Completion_Time AS [Estimated Completion Time], CASE R.Transaction_Isolation_Level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' ELSE CAST(R.Transaction_Isolation_Level AS VARCHAR(32)) END AS [Transaction Isolation Level]
FROM SYS.DM_EXEC_REQUESTS AS R LEFT OUTER JOIN SYS.DM_EXEC_SessionS AS S ON S.Session_ID = R.Session_ID LEFT OUTER JOIN SYS.DM_EXEC_ConnectionS AS C ON C.Connection_ID = R.Connection_ID CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_Handle) AS ST CROSS APPLY SYS.DM_EXEC_Query_Plan(R.Plan_Handle) AS QP 
WHERE R.STATUS NOT IN ('Background','Sleeping')

Identity column information

Use below query to get information for all identity columns in  a database:

SELECT QUOTENAME(SCHEMA_NAME(T.SCHEMA_ID)) + '.' + QUOTENAME(T.NAME) AS TABLENAME, C.NAME AS COLUMNNAME, CASE C.SYSTEM_TYPE_ID WHEN 56 THEN 'INT' WHEN 52 THEN 'SMALLINT' WHEN 48 THEN 'TINYINT' END AS 'DATATYPE', IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS CURRENTIDENTITYVALUE, IDENT_INCR(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS IDENTITYINCREMENT, IDENT_SEED(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS IDENTITYSEED, CASE C.SYSTEM_TYPE_ID WHEN 56 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 2147483647 WHEN 52 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 32767 WHEN 48 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 255 END AS 'PERCENTAGEUSED'
FROM SYS.COLUMNS AS C INNER JOIN SYS.TABLES AS T ON T.[OBJECT_ID] = C.[OBJECT_ID] AND C.IS_IDENTITY = 1 ORDER BY PERCENTAGEUSED DESC

Error 0xC0014062: Unable to complete login process due to delay in opening server connection

Sometimes you may get an error log when your backup maintenance plan job gets fails with the following error on a server with many databases or connections:

Message:
Executed as user: xyz\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.0.5500.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  01:00:05 AM  Could not load package "Maintenance Plans\xyz" because of error 0xC0014062.  
Description: The LoadFromSQLServer method has encountered OLE DB error code 0-80004005 (Unable to complete login process due to delay in opening server connection).  The SQL statement that was issued has failed.  
Source:   Started:  01:00:05 AM  Finished: 01:01:02 AM  Elapsed:  57.175 seconds.  
The package could not be loaded.  The step failed.

This indicates that the maintenance plan which uses SSIS (integrated services) and msdb databases is delay in connection. To resolve this issue general recommendation could be

Change the host file
Which is generally located at “C:\WINDOWS\system32\drivers\etc\host” add server name and an IP address.

Change the number of retry for the specific job to greater number (eg. 5 or more) and also can change the retry interval to (0). This way if this job fails with delay in connection, will retry again till specified number of times and mostly it gets succeeded.

T-Log space used by which transaction

Sometime T-Log space is increasing very fast and we are not aware that which transaction is culprit. You can use below DMV to check which transaction is culprit.

SELECT ST.Session_ID AS [Session ID], DT.Transaction_ID AS [Transaction ID], DB_NAME(DT.Database_ID) AS [DataBase Name], 
CASE Database_Transaction_Type WHEN 1 THEN 'Read / Write' WHEN 2 THEN 'Read Only' WHEN 3 THEN 'System' END AS [Transaction Type], 
CASE Database_Transaction_State WHEN 1 THEN 'Not Initialized' WHEN 3 THEN 'Transaction No Log' WHEN 4 THEN 'Transaction With Log' WHEN 5 THEN 'Transaction Prepared' WHEN 10 THEN 'Commited' WHEN 11 THEN 'Rolled Back' WHEN 12 THEN 'Commited And Log Generated' END AS [Transaction State], 
Database_Transaction_Begin_Time AS [Transaction Begin Time], SP.HostName AS [Host Name], SQLT.Text, SP.LogiName AS [Login Name], SP.LastWaitType AS [Last Wait Type], SP.Status, 
Database_Transaction_Log_Bytes_Reserved AS [Log Bytes Reserved], Database_Transaction_Log_Record_Count AS [Log Record Count], Database_Transaction_Log_Bytes_Used AS [Log Bytes Used], 
(Database_Transaction_Log_Bytes_Used + Database_Transaction_Log_Bytes_Reserved )/1024 AS [Total Log Space Used KB]
FROM SYS.DM_TRAN_Database_TransactionS AS DT JOIN SYS.DM_TRAN_SESSION_TransactionS AS ST ON DT.Transaction_ID=ST.Transaction_ID JOIN SYS.SYSPROCESSES AS SP ON SP.SPID = ST.SESSION_ID CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) SQLT 
WHERE DT.Transaction_ID > 1000 AND ST.Session_ID > 50

Change Batch Separator

By default GO is the batch separator in SQL Server. GO is not a Transact SQL statement. It is a command recognized by SQL server as batch separator. You can change the batch separator (GO) in SQL server.

Follow below Steps to change the batch separator in SSMS:

• Start SSMS
• Go to Tools –> Options
• Click on the “Query Execution” node
• Notice that we have an option to change the Batch Separator
• Change the batch separator
• Click “OK”