November 19, 2012 at 10:04 am
Hi All
I'm trying to figure out if I'm going about this the right way
We have a database file that is mysteriously growing an filling out the space on a drive on one of our servers. I am trying to use extende events to get to the bottom of it but
am having problems with trying to identify the underlying cause of the increase.
I have been going through jonathan kehayias's blog and have found it extremely useful:
I am using extended events and have created this session:
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='DataFileChange2')
DROP EVENT SESSION [DataFileChange2] ON SERVER;
CREATE EVENT SESSION [DataFileChange2]
ON SERVER
ADD EVENT sqlserver.databases_data_file_size_changed(
ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.is_system, sqlserver.nt_username, sqlserver.plan_handle, sqlserver.request_id, sqlserver.session_id, sqlserver.session_nt_username, sqlserver.sql_text, sqlserver.transaction_id, sqlserver.tsql_stack, sqlserver.username))
ADD TARGET package0.asynchronous_file_target(
SET filename='d:\extendedevents\DbFile.xel', max_file_size=5, max_rollover_files=5, metadatafile='d:\extendedevents\DbFile.xem')
WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 5 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF)
ALTER EVENT SESSION [DataFileChange2] ON SERVER STATE = START
This lets me know when this event fires
HOWEVER what I would dearly like to do is to be able to capture the The Sql being run on the box when this event fires. The sqlserver.sql_text in the ACTION section there only gives me the sql if I were to use a DBCC Shrinkfile. What is the correct way to do this? Add another event?
WHat I am doing now is just to have the above run in conjunction with a server side trace and the when it fires I can see the trace?
Is it possible to do without a trace?
Thanks
Chris
November 19, 2012 at 10:37 am
How about using the blackbox trace that is already running ?
This might help you.
http://technet.microsoft.com/en-us/library/cc293615.aspx
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
November 19, 2012 at 10:41 am
Most likely, what you need to see is what DML commands are being run at the time the database grows. To do that, you can either run a trace, or add batch start/complete events to the Extended Events capture. Most useful would be to add them to your Extended Events capture. It works just like a server-side trace, but with the added features available in Extended Events.
That way, you'll be able to see what DML commands are running when the file grows. That'll be what you need, almost certainly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply