Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

What SQL Statements Are Currently Executing? Expand / Collapse
Author
Message
Posted Tuesday, October 28, 2008 6:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:37 PM
Points: 127, Visits: 553
This is very Useful..
Thanks Very Much!



Post #592802
Posted Tuesday, October 28, 2008 6:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 2, 2009 5:32 AM
Points: 3, Visits: 9
I got that same message when trying this script on a SQL2000 server. This script is only for SQL2005.
Post #592813
Posted Tuesday, October 28, 2008 6:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 31, 2008 1:25 PM
Points: 2, Visits: 20
That explains it! Thanks!
Post #592817
Posted Tuesday, October 28, 2008 7:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 1, 2011 5:12 PM
Points: 5, Visits: 144
How to change the compatibility mode?
Post #592881
Posted Tuesday, October 28, 2008 8:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 8, 2008 2:05 PM
Points: 14, Visits: 67
This is great. It will be very useful in nailing down those sneaky, long running processes that you can't get a peek at with the standard tools. Thanks!
Post #592962
Posted Tuesday, October 28, 2008 9:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:46 PM
Points: 79, Visits: 1,331
You will need 'VIEW SERVER STATE' permission to run the query that is in the proc.
Post #593015
Posted Tuesday, October 28, 2008 9:54 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 3:19 PM
Points: 21, Visits: 167
This was a good one , Helped me quite a bit.
Post #593036
Posted Tuesday, October 28, 2008 10:26 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:29 PM
Points: 752, Visits: 920
Very nice article and very useful query.

Just out of curiosity, why did you decide to make it a procedure instead of a view?

Naturally you would lose that order by, but since you may wish to see it ordered by other things such as User or Status anyway it seems the view would be more flexible.


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #593073
Posted Tuesday, October 28, 2008 10:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 7:53 AM
Points: 49, Visits: 850
Hi,

I’m glad you liked the article, it’s quite interesting to see what is happening with SQL Server and SQL.

I hadn’t really thought about creating a view, I typically create utilities as procedures by default, but maybe the advantages of permissioning and sorting make a view more appropriate…

Thanks
Ian
Post #593091
Posted Tuesday, October 28, 2008 2:17 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:46 PM
Points: 79, Visits: 1,331
I am planning on using a bit of a modified version that will filter to a specific DB. As a developer I am in need of just seeing what is on the DB I am working on. Here is a crack at a revision to the code to allow this.

IF OBJECT_ID(N'[dbo].[dba_WhatSQLIsExecuting]') IS NULL 
BEGIN
EXEC ('CREATE PROCEDURE
[dbo].[dba_WhatSQLIsExecuting]
AS BEGIN SELECT ''STUB'' END');
END;
GO
ALTER PROC [dbo].[dba_WhatSQLIsExecuting]
--Inputs
@specificDB nvarchar(128) = NULL
AS
/*--------------------------------------------------------------------
Purpose: Shows what individual SQL statements are currently executing.
----------------------------------------------------------------------
Parameters: None.
Revision History:
24/07/2008 Ian_Stirk@yahoo.com Initial version
28/10/2008 added filter for a specific DB
Example Usage:
To get data from all DBs on that instance
1. exec YourServerName.master.dbo.dba_WhatSQLIsExecuting

To get data from a specific DB
2. DECLARE @thisDB nvarchar(128); SET @thisDB = DB_NAME();
exec YourServerName.master.dbo.dba_WhatSQLIsExecuting @thisDB
---------------------------------------------------------------------*/
BEGIN
DECLARE @thisSPID int;

SET @thisSPID = @@SPID;
-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- What SQL Statements Are Currently Running?
SELECT
session_Id [SPID]
,ecid [ecid]
,DB_NAME(sp.dbid) [Database]
,nt_username [User]
,er.status [Status]
,wait_type [Wait]
,SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
ELSE er.statement_end_offset END -
er.statement_start_offset)/2)
[Individual Query]
,qt.text [Parent Query]
,program_name [Program]
,Hostname
,nt_domain
,start_time
FROM sys.dm_exec_requests [er]
INNER JOIN
sys.sysprocesses [sp]
ON er.session_id = sp.spid
CROSS APPLY
sys.dm_exec_sql_text(er.sql_handle) [qt]
WHERE session_Id > 50 -- Ignore system spids.
AND session_Id NOT IN (@thisSPID) -- Ignore this current statement.
--when a specific DB is supplied filter for it, ohterwise get all DBs
AND CASE WHEN @specificDB IS NULL THEN N'' ELSE DB_NAME(sp.dbid) END
= CASE WHEN @specificDB IS NULL THEN N'' ELSE @specificDB END
ORDER BY session_Id, ecid
END

Post #593250
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse