Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
prashu
Starting Member
4 Posts |
 Posted - 2008-12-17 : 08:36:54
|
| I'm trying to troubleshoot a SQL issue. I know it's a blocking issue in SQL 2005...so I go do the following:1) I find the spid that is causing the block and do an DBCC Inputbuffer(spid)The issue with this is that it only displays 255 characters. So, I'll only see part of a SQL Query and it truncates. Is there another tool or some kind of parameter I could put on this command so that the results aren't truncated? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-12-17 : 08:39:11
|
| select * from sys.dm_tran_locks where request_session_id = yourspid___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
 |
|
|
prashu
Starting Member
4 Posts |
Posted - 2008-12-17 : 08:57:27
|
[quote]Originally posted by spirit1
select * from sys.dm_tran_locks where request_session_id = yourspid___________________________________________________________________________Thanks spirit1 for your reply ,the issue is i'm not able to get the full query from the event_info from DBCC INPUTBUFFER. it is getting truncated. Is there any other way to get the full SQL query statement . |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
prashu
Starting Member
4 Posts |
Posted - 2008-12-17 : 09:53:17
|
| Thanks for your reply Spirit1, i tried that statement but it is not solving my problem .I created a log file that is intended to capture the sql statement being used to insert/ update/delete a record in a table (see the attached code snippet) but even though I created the statement field to be varchar (4000) the captured statement is being cut off...Is there any other way to capture the whole statement. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-12-17 : 10:01:46
|
quote:
i tried that statement but it is not solving my problem
how did you try it? do you know what each dynamic view is doing? how does it not work for you?___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-17 : 10:02:21
|
| You can increase Characters size in Tools-Options-Query Results. |
|
|
|
prashu
Starting Member
4 Posts |
Posted - 2008-12-17 : 23:55:07
|
| HI spirit1 , using that statement , we'l be getting the statement in that 'SqlStatement Text' , if i use it in insert/ update trigger , i'm getting only the whole Trigger statement not the actual insert/update sql statement that got executed |
|
|
|
|
|
|
DBCC Inputbuffer