Posts
33
Comments
186
Trackbacks
0
Monday, March 4, 2013
SQL Server - get last executed SQL statement
We all know the usage of sp_who2 command in SQL server, its life-saver when you need to know what's causing your query take longer time to execute or in other words who's blocking you r query.

But, SQL server has many in-built functions which lets you sneak a peek inside the sql server and we can use them to our advantage. One of them is 

DBCC INPUTBUFFER(SP_ID)

Using this function, we can see the last executed sql statement in that process id.

There are several ways to find out what is the latest run query from system table sys.sysprocesses.

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext sql_handle
FROM sys.sysprocesses
WHERE spid 61
SELECT TEXT
FROM 
sys.dm_exec_sql_text(@sqltext)
GO



Posted On Monday, March 4, 2013 3:32 PM | Comments (9)
SQL server - get list of active connections to each database
Many times, we want list of all users who are connected to the SQL server - to determine it  use the below query. It returns the Database, Number of open connections and logged-in user credentials.

SELECT DB_NAME(dbid) as Database, COUNT(dbid) as Number Of Open Connections,
loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

Hope this helps.
Posted On Monday, March 4, 2013 3:24 PM | Comments (6)