Thursday, August 21, 2014
Query for user tables without Primary Keys
SELECT sch.name AS SchemaName, obj.name AS TableName
FROM sys.objects obj
INNER JOIN sys.schemas sch
ON obj.schema_id = sch.schema_id
LEFT JOIN sys.objects pk
ON pk.type = 'PK'
AND pk.parent_object_id = obj.object_id
WHERE obj.is_ms_shipped = 0
AND pk.object_id IS NULL
AND obj.type = 'U'
Monday, July 7, 2014
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
Tuesday, June 25, 2013
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.
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
)
CREATE PROCEDURE [dbo].[vspKillDBSessions]
(
@databaseName VARCHAR(200)
)
AS
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
Monday, June 27, 2011
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.
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.
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.
Monday, February 8, 2010
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.
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.
Thursday, February 4, 2010
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 */
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 */
Subscribe to:
Posts (Atom)