DMV query to find current executing command of a session

SELECT  er.session_id,
                 er.status,
                 er.command,
                 er.cpu_time,
                 er.total_elapsed_time,
                 sqltext.TEXT
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
where er.session_id = -- Your Session ID here

Stored Procedure to kill all database sessions

I am sure a lot of us would have seen that "database in use" error while restoring database over an existing database or detaching a database. The stored proc in this post will help with this situation. You can use it to kill all the sessions in the database. It takes database name as a parameter.
 

CREATE PROCEDURE [dbo].[vspKillDBSessions]

(

@databaseName   VARCHAR(200)

)
AS

 SET NOCOUNT ON

 DECLARE @killStr NVARCHAR(4000);

 WITH killList (killStr)
 AS(
  SELECT ' KILL ' + CAST(spid AS VARCHAR(5))  + CHAR(10) FROM master..sysprocesses
  WHERE dbid = db_id(@databaseName)
  FOR XML PATH(''), TYPE
   )

 

SELECT @killStr = CAST (killStr AS NVARCHAR(4000) ) FROM killList;

EXEC sp_executesql @killStr

Firewall configuration for a SQL Server 2008 Analysis Services Named Instance

1. Open TCP port 2382. This port is used by SQL Browser to handle instance name resolution for OLAP.

2. If Analysis Services is configured for dynamic port (default configuration), then add Analysis Services to firewall exception list.Find the path name of msmdsrv.exe, usually it will be below -

C:\Program Files\Microsoft SQL Server\MSAS10.{Instance Name}\OLAP\bin\msmdsrv.exe

Add above to the firewall exception list. This will enable AS to communicate with clients on any port it’s running on through SQL Browser.

If Analysis Services is configured to listen on a fixed port, then instead of adding msmdsrv.exe to exclusion list, open the inbound communication for the TCP port.

Firewall configuration for SQL 2008/2005 Named Instance

1. Open TCP and UDP port 1434. UDP port 1434 is used by SQL Browser to handle instance name resolution for SQL Server. TCP Port 1434 is used for SQL server dedicated Admin connections.

2. If SQL Server has been configured for dynamic port (Default configuration) then add SQL server service to firewall exception list. Find the path name of sqlserver.exe, usually it will be below (Standard install)-

C:\Program Files\Microsoft SQL Server\MSSQL10.{Instance Name}\MSSQL\Binn\sqlservr.exe

Add above to the firewall exception list. This will enable SQL server to communicate with clients on any port it’s running on through SQL Browser.

If SQL Server is configured to listen on a fixed port, then instead of adding sqlservr.exe to the exception list, you can open the Inbound communication for the TCP port.

Dedicated Admin Connection feature for SQL 2005/2008

DBAs would run into situations where SQL server becomes unresponsive to the point that it doesn't allow new connections. Since we cannot login , we won't be able to figure out the rouge query (or queries :-)) that caused the problem.

SQL 2008 & 2005 allow you to connect to the sql server even under these circumstances using a Dedicated Admin Connection (or DAC).

To use it, you would need to first enbale it using below statements -

sp_configure 'REMOTE ADMIN CONNECTIONS', 1;
GO
RECONFIGURE;
GO

Then connect to the server using SSMS new query Or SqlCmd. You will have to use 'ADMIN:' before the server name. For example if your server name is xyz,
then for DAC connection the server name would be ADMIN:xyz

DAC connection doesn't work with SSMS object explorer and only 1 DAC connection is allowed at a time.

DMV Query For Current Executing SQL Statement

Often times for troublshooting a running big batch of query (for example a migration script) you would need to know the current sql statement executing. You can use below query after identifying the session id (spid) for the batch :

SELECT SUBSTRING(t.[text],
1+(r.statement_start_offset/2),
1+(
(
CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(t.[text])
ELSE r.statement_end_offset
END - r.statement_start_offset
)/2
)
)
FROM
sys.dm_exec_sessions s LEFT JOIN
sys.dm_exec_requests r ON (r.session_id = s.session_id) CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id = ? /* Session id that you need to investigate */

Agents Runnig fine but red X in replication monitor

Recently came accross a problem in a non prod environment where replication replication monitor was marked with red X. It was a SQL 2000 server and replication agents were running fine without error. Server had undergone restores from prod backups and replication was rebuilt after the restores. One of my collegue found the solution in http://www.replicationanswers.com.

Running sp_MSload_replication_status did the trick.

The link has solution for SQL 2005 as well.