5

I know there must be a simple answer to this question, but I cannot find it anywhere. Our SQL 2012 FCI have SQLDIAG .XEL files in their MSSQL\LOG folder. In some instances, these files are often exactly 100MB yet when opened in SSMS appear to be empty (says displaying 0 events).

I need to know how to manage their maximum size and file retention if this is possible.

We have some root drive mount points that were from SQL 2008 FCI (upgraded to SQL 2012) and these mount points were only 1GB. As you can guess, these mount points are filling up and I need to limit these Failover Cluster Diagnostic logs if possible or I'll have to come up with an alternative solution.

Improve this question

2 Answers 2

Reset to default
6

You can change this behavior with the ALTER SERVER CONFIGURATION statement. The flexibility to modify all of the necessary parameters (path, max files, max file size) should give you enough control to get it to where you want. See the bottom of the referenced link above for samples/examples. Here is one copied from the source:

ALTER SERVER CONFIGURATION 
SET DIAGNOSTICS LOG MAX_SIZE = 10 MB;

HOWEVER ... these diagnostic logs are great tools for troubleshooting. Whereas you can limit the amount of retained data, I would much rather get more storage to store the default amount of data (1000 MB, which is almost free in today's day) for the time when you will need as much diagnostic data as you can get.

Improve this answer
4
  • Yes, we need to increase the root mount point but it will be a while before we can do that. Thanks for the solution.
    – Brad
    Commented Aug 18, 2014 at 13:21
  • Thomas, do you know why/how the file can be 100MB but appear empty when opened in SSMS?
    – Brad
    Commented Aug 18, 2014 at 15:32
  • How are you opening it? Maybe you aren't getting the file(s) you think you are. Use sys.fn_xe_file_target_read_file() to be explicit: msdn.microsoft.com/en-us/library/cc280743.aspx
    – Thomas Stringer
    Commented Aug 18, 2014 at 15:39
  • I was opening it in SSMS just as an XEL. However using that function works and shows data. Thanks again.
    – Brad
    Commented Aug 18, 2014 at 16:30
0

The reason you are not able to open files may be UAC. Open SSMS in Administrator mode and try to open a file from SSMS.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.