| SQL_Guru 2006-01-30, 8:23 pm |
| We are just starting to deploy SQL 2005 and I have run into an issue with
Activity Monitor.
The Activity Monitor in SQL Server Management Studio shows no information on
long batches if run on SQL 2005. This appears to be a bug in the Activity
Monitor since it uses nvarchar(512) to gather the dbcc inputbuffer instead of
nvarchar(4000). The output from dbcc inputbuffer is noted correctly in the
SQL 2005 BOL from December. I've included the command used by Activity
Monitor to illustrate the problem.
Command used by Activity Monitor:
create table #tmpDBCCinputbuffer ([Event Type] nvarchar(512), [Parameters]
int, [Event Info] nvarchar(512))
insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER(154)')
select [Event Info] from #tmpDBCCinputbuffer
If I run this in query analyzer against a running query that is over 512
bytes I get the following message and don’t get a result set:
Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
To easily generate a long query that will still be running in order to check
this, use the following as an example:
/*
This is a long query…
*/
Waitfor delay ’00:02:00’
Fill up the comment section with text until it is over 512 characters. Run
it, note the spid it is using, and check what the batch is with Activity
Monitor. It won’t return anything. Then try it with dbcc inputbuffer (spid)
and you’ll see the correct results.
If anyone can verify this as a bug it would be great or if there is a way to
change the size it uses (nvarchar(4000)), that would be better. This is
especially an issue if dynamic queries are used instead of stored procedures,
as it is very easy to make a query over 512 characters including comments.
Thanks.
|