SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Return Query Text Along With sp_who2 Using Dynamic Management Views


Return Query Text Along With sp_who2 Using Dynamic Management Views

Author
Message
KenSimmons
KenSimmons
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3802 Visits: 2614
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
DAVNovak
DAVNovak
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1118 Visits: 210
Great script. It is nice to have everything together when viewing the results and checking for blocking.

Dave Novak



alen teplitsky
alen teplitsky
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12394 Visits: 4674
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.
john.vanda
john.vanda
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 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 BigGrin

--John Vanda
SQL Junior DBA
SanjayAttray
SanjayAttray
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7239 Visits: 1619
Thanks John and Ken for these scripts. Both are good and are worth keeping it handy.

SQL DBA.
Alz
Alz
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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 Crazy
KenSimmons
KenSimmons
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3802 Visits: 2614
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
Nabha
Nabha
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3209 Visits: 1815
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.

---------------------------------------------------------------------------------
EamonSQL
EamonSQL
SSC Eights!
SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)

Group: General Forum Members
Points: 838 Visits: 181
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 welcomeSmile

Eamon
Shon Thompson
Shon Thompson
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 537
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search