djrubin
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_131xxxxxxx xxxx.xel format.
The question: how can i archive the deadlock information and get a smaller list when viewing the target data?
The question: how can i archive the deadlock information and get a smaller list when viewing the target data?
ASKER
Yes, but only four out of thousands of records returned from viewing Management>Extended Events>Deadlock>package0.e vent_file.
stop the event
right click properties
go to advanced
set max records, max memory etc...
then start the event...
right click properties
go to advanced
set max records, max memory etc...
then start the event...
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. 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
there are lots of settings there
1GB is really too much...
if each log is 100 bytes and with tags 200 bytes, it contains 5M records...
if each log is 100 bytes and with tags 200 bytes, it contains 5M records...
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 :)
if you stop it, then delete/move /rename files and restart, what happens
do it on test machine first :)
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
ID: 4210137614
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
I guess it clears everything... except the date in files
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.
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.
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...
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...
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...
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window