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.

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.

  • 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 Aug 18 '14 at 13:21
  • Thomas, do you know why/how the file can be 100MB but appear empty when opened in SSMS? – Brad Aug 18 '14 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 Aug 18 '14 at 15:39
  • I was opening it in SSMS just as an XEL. However using that function works and shows data. Thanks again. – Brad Aug 18 '14 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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