March 4, 2008 at 12:23 pm
I am trying to find out the sql statement run by a process. To do this I am using the DBCC INPUTBUFFER (process #) command. However, the eventinfo column of the output, cuts off the sql statement. Does anyone know how I can see the full statement?
March 4, 2008 at 12:31 pm
In eventinfo column for DBCC inputbuffer you can see the first 256 characters only. Why not run a SQL profiler. Textdata column will display the complete code in it.
SQL DBA.
March 4, 2008 at 12:40 pm
Thank you for your information about profiler. I will try that. But I was also curious to know why BOL says eventinfo is nvchar(4000) and it will only show 256 characters.
From BOL: EventInfo nvarchar(4000)
For an EventType of RPC, EventInfo contains only the procedure name. For an EventType of Language, only the first 4000 characters of the event are displayed.
March 4, 2008 at 12:46 pm
I checked both 2000 and 2005 and it shows nvarchar(255) -
For an EventType of RPC, EventInfo contains only the procedure name. For an EventType of Language or No Event, only the first 255 characters of the event are displayed.
SQL DBA.
March 4, 2008 at 12:48 pm
there is a DMV exposing this in sql2005, I'll have to do a little search on it.
this comes from http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2892190&SiteID=1
(I don't have my own scripts available for the moment)
-- CREATE VIEW who_vw
-- AS
SELECT
p.spid AS [SPID]
,DB_NAME(p.dbid) AS [DBName]
,COALESCE(OBJECT_NAME(txt.objectID,txt.DBID),
CASE
WHEN txt.encrypted = 1
THEN 'Encrypted'
WHEN r.session_id IS NULL
THEN txt.text
ELSE LTRIM(SUBSTRING(txt.text, r.statement_start_offset / 2 + 1
,((CASE WHEN r.statement_end_offset = -1
THEN DATALENGTH(txt.text)
ELSE r.statement_end_offset
END) - r.statement_start_offset) / 2))
END) AS [Query]
,p.loginame AS [Login]
,p.hostname AS [Host Name]
,p.status AS [Status]
,p.blocked AS [BlkBy]
,ISNULL(t.trancount,0) AS [TranCount]
,ISNULL(l.lockcount,0) AS [LockCount]
,l.resource_type AS [LockType]
,l.request_mode AS [LockMode]
,l.request_status AS [LockStatus]
,r.wait_type AS [WaitType]
,r.percent_complete AS [PercentComplete]
,r.estimated_completion_time AS [EstCompTime]
,p.cpu AS [CPU]
,p.physical_io AS [IO]
,c.num_reads AS [Reads]
,c.num_writes AS [Writes]
,c.last_read AS [LastRead]
,c.last_write AS [LastWrite]
,p.login_time AS [StartTime]
,p.last_batch AS [LastBatch]
,p.PROGRAM_NAME AS [Program Name]
,p.spid AS [SPID2]
FROM sys.sysprocesses p
INNER JOIN sys.dm_exec_connections c (NOLOCK)
ON c.session_id = p.spid
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS txt
LEFT JOIN sys.dm_exec_requests r
ON c.session_id = r.session_id
LEFT OUTER JOIN
(
SELECT
session_id
,database_id = MAX(database_id)
,trancount = COUNT(*)
FROM
sys.dm_tran_session_transactions t
INNER JOIN sys.dm_tran_database_transactions dt
ON t.transaction_id = dt.transaction_id
GROUP BY session_id
) t
ON t.session_id = p.spid
LEFT OUTER JOIN
(
SELECT
request_session_id
,database_id = MAX(resource_database_id)
,resource_type
,request_status
,request_mode
,lockcount = COUNT(*)
FROM
sys.dm_tran_locks (NOLOCK)
GROUP BY request_session_id, resource_type, request_mode, request_status
) l
ON p.spid = l.request_session_id
WHERE p.spid <> @@SPID
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 4, 2008 at 1:19 pm
ALZDBA (3/4/2008)
there is a DMV exposing this in sql2005, I'll have to do a little search on it.this comes from http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2892190&SiteID=1
(I don't have my own scripts available for the moment)
-- CREATE VIEW who_vw
-- AS
SELECT
p.spid AS [SPID]
,DB_NAME(p.dbid) AS [DBName]
,COALESCE(OBJECT_NAME(txt.objectID,txt.DBID),
CASE
WHEN txt.encrypted = 1
THEN 'Encrypted'
WHEN r.session_id IS NULL
THEN txt.text
ELSE LTRIM(SUBSTRING(txt.text, r.statement_start_offset / 2 + 1
,((CASE WHEN r.statement_end_offset = -1
THEN DATALENGTH(txt.text)
ELSE r.statement_end_offset
END) - r.statement_start_offset) / 2))
END) AS [Query]
,p.loginame AS [Login]
,p.hostname AS [Host Name]
,p.status AS [Status]
,p.blocked AS [BlkBy]
,ISNULL(t.trancount,0) AS [TranCount]
,ISNULL(l.lockcount,0) AS [LockCount]
,l.resource_type AS [LockType]
,l.request_mode AS [LockMode]
,l.request_status AS [LockStatus]
,r.wait_type AS [WaitType]
,r.percent_complete AS [PercentComplete]
,r.estimated_completion_time AS [EstCompTime]
,p.cpu AS [CPU]
,p.physical_io AS [IO]
,c.num_reads AS [Reads]
,c.num_writes AS [Writes]
,c.last_read AS [LastRead]
,c.last_write AS [LastWrite]
,p.login_time AS [StartTime]
,p.last_batch AS [LastBatch]
,p.PROGRAM_NAME AS [Program Name]
,p.spid AS [SPID2]
FROM sys.sysprocesses p
INNER JOIN sys.dm_exec_connections c (NOLOCK)
ON c.session_id = p.spid
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS txt
LEFT JOIN sys.dm_exec_requests r
ON c.session_id = r.session_id
LEFT OUTER JOIN
(
SELECT
session_id
,database_id = MAX(database_id)
,trancount = COUNT(*)
FROM
sys.dm_tran_session_transactions t
INNER JOIN sys.dm_tran_database_transactions dt
ON t.transaction_id = dt.transaction_id
GROUP BY session_id
) t
ON t.session_id = p.spid
LEFT OUTER JOIN
(
SELECT
request_session_id
,database_id = MAX(resource_database_id)
,resource_type
,request_status
,request_mode
,lockcount = COUNT(*)
FROM
sys.dm_tran_locks (NOLOCK)
GROUP BY request_session_id, resource_type, request_mode, request_status
) l
ON p.spid = l.request_session_id
WHERE p.spid <> @@SPID
haha! That's my view!!! I feel special ...
http://www.sqlservercentral.com/Forums/Topic457115-146-1.aspx
March 4, 2008 at 1:27 pm
Thanks for all of your responses. I will try Adam's view and see if it gives me the results I need.
March 4, 2008 at 2:06 pm
I posted a custom rdl which does this - the query is a bit more simple. I don't usually create views or procs for this type of work as I may wish to add filters so generally a query works better.
http://www.grumpyolddba.co.uk/monitoring/Creating%20Custom%20Reports%20for%20the%20DBA.htm
you can extract the query from the rdl file
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 4, 2008 at 2:33 pm
You can filter a view ...
Personally, I prefer procs and views as you can create a wrapper process to keep them in a "admin" or "dba" database on all your servers so that you have a repository of all your scripts available no matter what server you're on.
To each their own.
March 4, 2008 at 3:06 pm
Adam Bean (3/4/2008)
haha! That's my view!!! I feel special ...http://www.sqlservercentral.com/Forums/Topic457115-146-1.aspx
Dang, Adam... why don't you put your name on that bad boy! 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2008 at 3:27 am
Adam Bean (3/4/2008)haha! That's my view!!! ...
This prooves once again the world is round :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 5, 2008 at 6:38 am
I do that in my procs, but never with views ... I just updated it to include the view.
It still needs some work though ...
March 6, 2008 at 6:14 am
You get the sql by double-clicking on the process, which I think solves the original problem - although I'm not sure if there would be truncation.
the alternatives posted seem interesting though.
March 6, 2008 at 8:55 am
Hi
Why not use activity monitor?
Gives the last SQL batch for the process...
March 6, 2008 at 6:01 pm
ian treasure (3/6/2008)
HiWhy not use activity monitor?
Gives the last SQL batch for the process...
You do not get the query text, transaction count, lock count, etc. and you can only sort by columns. The view allows you to do whatever you want to do (sorting, custom filtering, joining back to other dmv's, etc.).
Again, to each their own, everyone has their ways of accomplishing similar tasks.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply