August 2014 | SansSQL

Thursday, August 28, 2014

T-SQL to get running sessions and its progress details

Here is a handy T-SQL to get running sessions and its progress details.

SELECT 
  ExecSessions.session_id AS SessionID
 ,ExecRequests.blocking_session_id AS BlockingSessionID
 ,db_name(ExecRequests.database_id) AS DatabaseName
 ,ExecSessions.login_name AS LoginName
 ,ExecSessions.memory_usage AS MemoryUsage
 ,CONVERT(dec(6,3), (ExecRequests.wait_time/60000.00)) AS [WaitTime (mins)]
 ,ExecRequests.[status] AS [Status]
 ,ExecRequests.percent_complete AS PercentComplete
 ,[Text]
FROM sys.dm_exec_sessions AS ExecSessions INNER JOIN sys.dm_exec_requests AS ExecRequests 
ON ExecSessions.session_id = ExecRequests.session_id
CROSS APPLY sys.dm_exec_sql_text (sql_handle) 
WHERE ExecSessions.status = 'running' AND ExecSessions.session_id <> @@SPID
ORDER BY ExecSessions.session_id

Friday, August 22, 2014

A connection was successfully established with the server, but then an error occurred during the login process.

Recently I came across an issue when connecting to SQL Server instance using SQL Authentication.
The error says,
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error:0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error:233)


To fix the issue, Disable "Shared Memory" protocol in the SQL Server Configuration Manager.


To find the status of SQL Server Protocols using T-SQL, refer to the below link
http://www.sanssql.com/2011/04/t-sql-query-to-find-sql-server.html