February 18, 2008 at 3:57 pm
Hey all,
I've been working on a new sp_who type procedure, and have a question.
If you join sys.dm_exec_sql_text with sys.dm_exec_connections's most_recent_sql_handle, you can retrieve the text of the SQL batch that is identified by the specified sql_handle (http://msdn2.microsoft.com/en-us/library/ms177648.aspx). Unfortunately, to us DBA's, this result is not worth a much. The result set from this is the actual definition of a procedure, vs. simply showing something like "dbname.dbo.procname".
So the alternative to this is to setup a temp table and DBCC INPUTBUFFER each of the spid's to get the more clear and concise information that you're seeking for a quick system overview.
Well, as great as this procedure is turning out, I want to turn it into a view. Therefore, I'm trying to find an alternative do sys.dm_exec_sql_text that achieves the same results as a DBCC INPUTBUFFER.
Please see the attached.
Any help is greatly appreciated.
**** updated versions are attached to this post ****
Thanks
February 19, 2008 at 2:58 pm
http://www.grumpyolddba.co.uk/monitoring/Creating%20Custom%20Reports%20for%20the%20DBA.htm
is an article about custom reports but if you extract the sql from the reports this might give you what you want - or at least a hint. I'm not totally sure exactly what you're trying to do - you don't really need dbcc inputbuffer with sql 2005. mail me via my web site if you wish to ask more.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 22, 2008 at 12:52 pm
Well, if anyone is interested, MS came back and said that there is in fact no true alternative to DBCC INPUTBUFFER (There is no replacement or alternative for DBCC INPUTBUFFER in SQL Server 2005. We have a bug that is internally tracking this feature request so we can expose similar interface via execution DMVs)
I also updated the first post in this threads attachments with my newest versions.
May 3, 2011 at 7:22 am
I'm not finding the your new code, but am very interested in seeing it. Could you please tell me where it can be found.
Thanks
There is an exception to every rule, except this one...
May 3, 2011 at 7:24 am
<grrrr> 3 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 3, 2011 at 7:25 am
shot in the dark since the thread is a couple years old, could you post the link to the connect ticket so that we may vote on it?
Also an updated URL for the script if possible.
May 3, 2011 at 7:28 am
I found the SQLServerCentral thread from a MSDN forum:
There is an exception to every rule, except this one...
May 3, 2011 at 7:33 am
Tx.
May 3, 2011 at 7:33 am
Many updates have happened to these objects ... while our documentation is still rather poor, visit the site in my signature if you want to review these objects and many more.
Hopefully sooner rather than later I'll be writing an article for SSC about the admin database.
May 3, 2011 at 7:35 am
Adam Bean (5/3/2011)
Many updates have happened to these objects ... while our documentation is still rather poor, visit the site in my signature if you want to review these objects and many more.Hopefully sooner rather than later I'll be writing an article for SSC about the admin database.
The picture on our signature does not load for me. It must be on your HD or something.
May 3, 2011 at 7:36 am
Ya documentation would be nice... :w00t:.
May 3, 2011 at 7:37 am
GilaMonster (5/3/2011)
<grrrr> 3 year old thread.
Why does it bother you? People read this from google today so it's still usefull!
May 3, 2011 at 7:40 am
Ninja's_RGR'us (5/3/2011)
GilaMonster (5/3/2011)
<grrrr> 3 year old thread.Why does it bother you? People read this from google today so it's still usefull!
Because I didn't notice and wrote a couple paragraphs reply to the OP before I saw the date.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 3, 2011 at 7:46 am
GilaMonster (5/3/2011)
Ninja's_RGR'us (5/3/2011)
GilaMonster (5/3/2011)
<grrrr> 3 year old thread.Why does it bother you? People read this from google today so it's still usefull!
Because I didn't notice and wrote a couple paragraphs reply to the OP before I saw the date.
I'd like to hear what you have to say about this. I'm sure I'd learn something.
Also when I search google, I never watch when the thread was posted, but wheter or not I can use the info. So I wouldn't stop myself at the thread date to post good solid info.
May 4, 2011 at 10:13 am
Ninja's_RGR'us (5/3/2011)
GilaMonster (5/3/2011)
Ninja's_RGR'us (5/3/2011)
GilaMonster (5/3/2011)
<grrrr> 3 year old thread.Why does it bother you? People read this from google today so it's still usefull!
Because I didn't notice and wrote a couple paragraphs reply to the OP before I saw the date.
I'd like to hear what you have to say about this. I'm sure I'd learn something.
Also when I search google, I never watch when the thread was posted, but wheter or not I can use the info. So I wouldn't stop myself at the thread date to post good solid info.
This post has a simple one-word reply that is ALL that is required: sp_whoisactive 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply