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 ««123»»

Return Query Text Along With sp_who2 Using Dynamic Management Views Expand / Collapse
Author
Message
Posted Thursday, June 26, 2008 7:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,221, Visits: 2,614
sheetalsh (6/26/2008)
Hi Ken,

It's giving error as follows:

Msg 321, Level 15, State 1, Line 28
"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

Thanks,
SS


Right Click on your database and select properties. Under the options tab the compatibility level has to be set to "SQL Server 2005 (90)" for it to work.


Ken Simmons
http://twitter.com/KenSimmons
Post #524144
Posted Thursday, June 26, 2008 7:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:57 PM
Points: 406, Visits: 210
Great script. It is nice to have everything together when viewing the results and checking for blocking.

Dave Novak



Post #524188
Posted Thursday, June 26, 2008 8:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:08 PM
Points: 1,414, Visits: 4,540
did something similar a few months ago with the connections and sessions DMV's. it was very nice except took a lot of storage. if we did this on a regular basis it would probably run 200GB - 300GB a month for 2-3 servers. i dumped the data into a table on the local server and every 15 minutes had an SSIS job copy it to a reporting server and delete all data in the local table.



https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #524269
Posted Thursday, June 26, 2008 9:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 22, 2010 10:46 AM
Points: 88, Visits: 181
I'm digging this solution. I really like to be able to filter my results, so I took the code from Dan (nice code Dan) that used the trick from Adam Machanic, and turned it into a table valued function:

USE [master]
GO
/****** Object: UserDefinedFunction [dbo].[fn_sp_who4] Script Date: 06/26/2008 11:31:07 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_sp_who4]
(@filterspid int = NULL, @filter tinyint = 1)
RETURNS @processes TABLE
(
spid int,
blocked int,
databasename varchar(256),
hostname varchar(256),
current_statement_parent xml,
current_statement_sub xml,
program_name varchar(256),
loginame varchar(256),
status varchar(60),
cmd varchar(128),
cpu int,
physical_io int,
[memusage] int,
login_time datetime,
last_batch datetime
)
AS
BEGIN

INSERT INTO @processes
SELECT sub.*
FROM
( SELECT sp.spid,
sp.blocked,
sd.name,
RTRIM(sp.hostname) AS hostname,
( SELECT LTRIM(st.text) AS [text()]
FOR XML PATH(''), TYPE) AS parent_text,
( SELECT
LTRIM(CASE
WHEN LEN(COALESCE(st.text, '')) = 0 THEN NULL
ELSE SUBSTRING(st.text, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1)
END) AS [text()]
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
WHERE er.session_id = sp.spid
FOR XML PATH(''), TYPE) AS child_text,
RTRIM(sp.[program_name]) AS [program_name],
RTRIM(sp.loginame) AS loginame,
RTRIM(sp.status) AS status,
sp.cmd,
sp.cpu,
sp.physical_io,
sp.memusage,
sp.login_time,
sp.last_batch
FROM sys.sysprocesses sp (NOLOCK)
LEFT JOIN sys.sysdatabases sd WITH (NOLOCK) ON sp.dbid = sd.dbid
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS st
) sub
INNER JOIN sys.sysprocesses sp2 ON sub.spid = sp2.spid
ORDER BY sub.spid

-- if specific spid required
IF @filterspid IS NOT NULL
DELETE @processes
WHERE spid <> @filterspid

-- remove system processes
IF @filter = 1 OR @filter = 2
DELETE @processes
WHERE spid < 51 OR spid = @@SPID

-- remove inactive processes
IF @filter = 2
DELETE @processes
WHERE status = 'sleeping'
AND cmd IN ('AWAITING COMMAND')
AND blocked = 0
RETURN
END

Then you can run
SELECT ... from master.dbo.fn_sp_who4(@filterspid = , @filter = )
WHERE ...

or

SELECT * from master.dbo.fn_sp_who4(default,default) for the function parameter defaults

You could also technically remove the parameters entirely since it's a table valued function after all and just use the where clause in the SELECT.

It's rough, but I like it :D

--John Vanda
SQL Junior DBA
Post #524319
Posted Thursday, June 26, 2008 9:47 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
Thanks John and Ken for these scripts. Both are good and are worth keeping it handy.

SQL DBA.
Post #524326
Posted Thursday, July 10, 2008 11:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 5, 2009 7:53 AM
Points: 2, Visits: 6
Very nice script Ken.

Just wondering if you can comment on how process intensive it may be. Would you say it's safe to use in production systems as an alternative to using SQL Profiler
Post #531813
Posted Thursday, July 10, 2008 5:16 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,221, Visits: 2,614
It is not process intensive. Many people use sp_who2 to get a quick view of what is going on on the server. This is by no means a replacement for profiler, but it does give you a quick snapshot of what is happening on the server.

Ken Simmons
http://twitter.com/KenSimmons
Post #532050
Posted Friday, July 11, 2008 4:42 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 1:07 PM
Points: 579, Visits: 1,807
Hi,

I got this error too as the database compatibility level was set to 80 and I think for this table valued function to work the compatibilty level has to be 90 which you can set using this command,

Exec sp_dbcmptlevel @dbname = 'yourdb', @new_cmptlevel = 90

But when I execute the query after setting the compatibility level to 90, its giving another error, 'The user does not have permission to perform this action.' I am the dbo for the database.

Can anyone please let me know if I have to be sysadmin for this or if I need any particular role at the server level. Please dont mind this question if it is very obvious as I am very new to SQL server database. Any suggestion would be appreciated.


---------------------------------------------------------------------------------
Post #532271
Posted Wednesday, October 15, 2008 2:49 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, July 31, 2014 1:27 AM
Points: 115, Visits: 143
Hi,

it runs fine for me but the SQLStatement associated with the blocking session is NULL. The SQLStatement text for the session being blocked is ok.

When i run DBCC inputbuffer(spid) against either session i get text returned.

am i missing something here ?

Any help welcome:)

Eamon
Post #586033
Posted Wednesday, December 31, 2008 12:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:41 AM
Points: 17, Visits: 402
This query does not return the last sql statement issues like inputbuffer does. It only gives the sql for statements currently running. Also, for stored procedures, it does not return the exec statement. It returns the create procedure statement.
Post #628237
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse