Link to home
Start Free TrialLog in
Avatar of djrubin
djrubinFlag for United States of America

asked on

SQL Server Extended Events: list of deadlocks growing too long - how to cleanup?

Over the last few months, we've had enough deadlocks occur that when viewing event data for the extended events, the list is much too long. Viewing the properties of the Deadlock Extended Event, I see where the file to store the information is located and named. Strange that there are a few files in this location that have deadlocks.xml_0_131xxxxxxxxxxx.xel format.

The question: how can i archive the deadlock information and get a smaller list when viewing the target data?
Avatar of HainKurt
HainKurt
Flag of Canada image

are your events in this view/table

select * from [sys].[server_event_session_events]

Open in new window

Avatar of djrubin

ASKER

Yes, but only four out of thousands of records returned from viewing Management>Extended Events>Deadlock>package0.event_file.
stop the event
right click properties
go to advanced
set max records, max memory etc...
then start the event...
Avatar of djrubin

ASKER

It appears that the Deadlock Session already has a 1G limit with file rollover. None of the files approach that size but the five files present match with the Maximum number of files. User generated image Any way to take the existing files, archive them for future access and recreate new files (if not automatic) so that the display of events is emptied?
did you stop and check the advanced tab...
there are lots of settings there
Avatar of djrubin

ASKER

Did. Nothing of use there... User generated image
1GB is really too much...

if each log is 100 bytes and with tags 200 bytes, it contains 5M records...
Avatar of djrubin

ASKER

Understood. But how to clear out what is already in those files?
not sure but
if you stop it, then delete/move /rename files and restart, what happens

do it on test machine first :)
Avatar of djrubin

ASKER

For this client, no test/dev to try it out on. Can't experiment on prod.
what are the histogram properties here

ID: 4210137614
Avatar of djrubin

ASKER

Please explain what you are asking
what happens if you stop and restart it...
I guess it clears everything... except the date in files
Avatar of Vitor Montalvão
Can't you just reconfigure it to reduce the max file size and the number of files to rollover? This should overwrite the existing ones.
Avatar of djrubin

ASKER

Huseyin, already stopped and restarted with no clearing of data.
Vitor, this is production so changing things on the fly without knowing what will happen is verbotten.
Vitor, this is production so changing things on the fly without knowing what will happen is verbotten.
That's true if you're dealing with data but in this case it's only a file that doesn't have any kind of impact in your database. Not even on performance.
If db deadlocks keep occurring, you need to fix what is causing excessive deadlocks to occur, not just worry about the log size from those deadlocks.
I am assuming, when you stop and start, it clears all events on db/current session...
But the events are on file, which is 5GB, 5M records... and you are trying to see those...

so, you should decrease file size, so you can see the history quickly...
Avatar of djrubin

ASKER

Can't decrease size in case there are a ton of messages. Would like to clear out what I have now, safely, as these messages have all been handled. Without changing any parameters, what is the best, safest, tested way to clear out the current messages.
5G of files can contain 5 Million records! Do you really need those?

I don't believe you need that many log file...

Just stop it, backup the log files to somewhere,
optionally decrease the size to 5-10MB (25K - 50K records)
then start the event session...
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial