When connecting to a SQL Server instance in SQL Server Management Studio, there are built in reports for databases you can use for free. These RDL files or SQL Server Reporting Service reports use DMVs and DMFs to retrieve meta-data about databases. You do not need to know anything about DMVs or DMFs to use them. These reports are great free tools given to us by Microsoft.
The first one I use a lot is the Disk Usage report. If you right-click a database, select Reports from the sub-menu, then Standard Reports, you will see Disk Usage.
Figure 1: Reports menu
The report gives you a lot of information. First, you get a graph of the Data Files Space Usage with percentages. The Transaction Log is on the right. Above the graphs is a table with more details about the data and transaction log files. Below the graphs are expandable lists for used space as well as growth of files.
Figure 2: Disk Usage report
The next report used frequently is the Disk Usage by Table. You can access it through the same method above, but by selecting Disk Usage by Table instead.
Figure 3: Disk Usage by Table menu
This report gives you an alphabetically sorted list of tables in your database. It has columns for # of records, Reserved Space in MBs, Data in KBs, Index space in KBs and Unused space in KBs.
Figure 4: Disk Usage by Table
The alternative to the last report is Disk Usage by Top Tables.
Figure 5: Disk Usage by Top Tables menu
This reports is the same as the last (list of tables in database) but sorted by the Size for Data column.
Figure 6: Disk Usage by Top Tables
The forth report I like is Index Physical Statistics.
Figure 7: Index Physical Statistics menu
This report helps you locate indexes that are fragmented. There is a lot of info in this report with expandable list for Reorganize and Rebuild suggestions.
Figure 8: Index Physical Statistics
The last report for now will be the Backup and Restore Events.
Figure 9: backup and Restore report
This report will have expandable areas for Average Time and Backup Operations to show how long on average backups and restores were taking as well as a list of Full, Incremental and TLOG backups including the location.
Figure 10: Backup and Restore report
As you can see, these are only 5 of the 10+ reports available from SQL Server Management Studio by default for databases. If you know how to run a profile trace, you can capture the query used to produce the reports and start you own logging of database information.
Thomas