Prev: SQL Server 2000+2008 on Win2008x64 question
Next: SQL SERVER 2005 Full backup job takes 3 times longer than before
From: Jeff Carrington on 1 Apr 2010 10:42
I am using SQL Server 2008 Std SP1 on a Windows 2008 std 64-bit server. I am
trying to understand and use Extended Events. I have created a session that
is to monitor tempdb usage when ever there is a physical page write. I want
to capture the query that is causing this action. Below is my session
defintion:

CREATE EVENT SESSION Monitor_tempdb_usage ON SERVER
ADD EVENT sqlserver.physical_page_write
( ACTION (sqlserver.nt_username, sqlserver.sql_text,
sqlserver.client_hostname, sqlos.task_time, sqlserver.username,
sqlserver.session_id)
WHERE sqlserver.database_id = 2 --filter database id = 2 i.e tempdb
)
ADD TARGET package0.asynchronous_file_target(
SET filename='c:\Monitor_tempdb_usage.etl',
metadatafile='c:\Monitor_tempdb_usage.mta')

I start the session and then run a script that writes a lot of data to a
global temp table. I can see the script working when I query the
sys.dm_db_session_space_usage DMV. However when I query the event file, I
get the following for the value of the sql_text:

Unable to retrieve SQL text

Does anyone have any idea what I am doing wrong?

Thanx much in advance for any and all help with this.

Jeff
--
Jeff Carrington
DBA
comScore Inc.
 | 
Pages: 1
Prev: SQL Server 2000+2008 on Win2008x64 question
Next: SQL SERVER 2005 Full backup job takes 3 times longer than before