Question MDW Disk Usage Data Collector Reports

Question

I evaluate the usage of data collection of SQL Server 2008 R2 Enterprise Edition and I do not understand the meaning of the disk usage reports. For example please take a look at my screenshots:

Both screenshots refer to the same database at the same time. I cannot find any sense in the report in the second snapshot but I need the abilitiy to analyse the growth of the data and index space used. Is there a better report available or can this one be corrected?

Regards, Andreas

Answer 1

Hi Andreas,

Space usage for data files is categorized as a percentage allocated to index pages, unallocated space, data pages, and unused space.

These categories are defined as follows:
Index: The amount of disk space used to hold index pages.
Unallocated: The amount of disk space available to the database but not yet allocated to any object.
Data: The amount of disk space used by data pages.
Unused: The amount of disk space allocated to one or more objects, but not yet used.

Space usage for the transaction log file is categorized as used space and unused space.

The space reported as Space Reserved is the amount of used space plus the space allocated to the file but not yet used. The space reported by Space Used is the actual space currently used by the file, excluding allocated space.

This report is good for  you to  know the disk space usage for  all the databases in the instance of SQL Server.

For more information, please refer to http://msdn.microsoft.com/en-us/library/cc280506.aspx.

Thanks,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 2

Hello Maggie and thanks for your answer again,

I am familar with the meaning of the categories. But try to compare the two reports of my first post of the same database:

First report shows a snapshot of one moment. These values are correct: The database data file is 4GB and 18,8% (770MB) is used for data, 5,37% (220MB) is used for index. Theses two values or the sum of it are interesting for me and I would like to get a report of these values over time.

The second report is does not make any sense for me. First of all the diagram uses a stapled mode, the database is 4GB of size, but the diagram shows about 8GB. Even if I add data and log file sizes I would get only 4,5GB. How is it coming?

Regards, Andreas

Answer 3

Hi Andreas,

As I said in my first post, Space usage for data files is categorized as a percentage allocated to index pages, unallocated space, data pages, and unused space.

The second report shows: Unused Size: 967,80M, Unallocated Size: 3.128,20, Index Size: 1,00, Data Size=4.608,00. Unused Size plus Unallocated Size plus Index Size plus Data equal to Data Files Space Usage. I think the Data in the second report means Data Files Space Usage.

Hope this helps,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 4

Unused Size plus Unallocated Size plus Index Size plus Data equal to Data Files Space Usage. I think the Data in the second report means Data Files Space Usage.

That's exactly what I am speaking about. You said:

Unused Size + Unallocated Size + Index Size + Data = Data File Space

Should be:
967,8          + 3128,2               + 1              + 4608 = 8705

But the Data File Space is exactly 4096! Even the sum of log file and data file is less (4608).
The second report is crap, I wonder if any DBA can use it. Is there an easy way to adapt it?

Andreas

Answer 5

Thanks Andreas for reporting this issue.

We are working on fixing these issues in Data collector reports.

Please let us know if you would be willing to validate the fixed reports on your test systems?. If so, please drop a mail to  me at [e-mail]

Thanks

Sethu Srinivasan [MSFT]

SQL Server

http://blogs.msdn.com/sqlagent

Answer 6

I am willing to validate the fixed reports and already sent you an email.

Regards, Andreas

Answer 7

Hi Sethu,

Has this been resolved yet?

I'll be willing to participate in validating the fixed reports.

Thanks,

Philip

Answer 8

Unfortunatly they only invite for testing on SQL Server 2012. MS is not willing to solve such bugs in current versions. Sad.

Answer 9

Hello Andreas,

Could you please submit the request to our product support http://support.microsoft.com/ph/1044 . Please also specify the product version that you would like to get a fix

Thanks

Sethu Srinivasan [MSFT]

SQL Server

http://blogs.msdn.com/sqlagent

Answer 10

Hello Sethu,

I tried to, but I was not able to find an option to request a solution for that without costs.

Andreas

Answer 11

Hello Andreas,

You could take a look at http://support.microsoft.com/gp/lifepolicy .

http://support.microsoft.com/lifecycle

Thanks

Sethu Srinivasan [MSFT]

SQL Server

Answer 12

Hello Sethu,

3 options: Self service, Paid incident support, contract incident support:

Answer 13

Please install SQL 2012 SP1 from http://support.microsoft.com/kb/2674319, let us know if the report works as expected in your scenario

Thanks

Sethu Srinivasan [MSFT]

SQL Server

http://blogs.msdn.com/sqlagent

Answer 14

Hello Sethu,

thank you for your answer. Paying money for a new version of a faulty software is no option for me and I won't try it. So my conclusion to MDW at SQL Server 2008 R2 is: It is not worth to think about using it. Unfortunatly systems where it was ever activated won't be able to remove it completly again. You can only disable it.

Andreas

Answer 15

Hello Andreas,

Please use the above options to open a support ticket with Microsoft. As it looks like an Issue with our report we would like to investigate the same and provide you with further recommendations. If this leads to be an issue with the report you will not be charged for the support ticket ( The complete cost will be refunded back to you.).

Regards

Pradeep