Finding Out the Connection Leaks in Sql Server

This Query (Q1) will show the connections that are in sleep mode
e.g. the datareaders associated with this Query might be not closed

(Q1)


SELECT ec.session_id, last_read,
 last_write, text, client_net_address, program_name, host_process_id, login_name
FROM sys.dm_exec_connections  ec
JOIN sys.dm_exec_sessions es
  ON ec.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS dest
where es.status = 'sleeping'

Q2 will show the number of open connections by the Database Users


(Q2 )



SELECT DB_NAME(dbid) as 'DataBase Name',
COUNT(dbid) as 'Total Connections'
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid

Query to Find Duplicate Rows from TABLE - SQL SERVER

SELECT  Office_Name , DUPCOUNT FROM (
SELECT  OFFICE_NAME , ROW_NUMBER() OVER (PARTITION BY OFFICE_NAME ORDER BY OFFICE_NAME) AS DUPCOUNT FROM Mast_Office ) OBJ
WHERE OBJ.DUPCOUNT > 1

Finding tables in DataBase which does not have Primary Key SQL Server

USE MyDataBase
GO
SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID)FROM SYS.INDEXES
WHERE INDEX_ID = 0 AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
ORDER BY [TABLE]
GO