5 Database Reports In SQL Server Management Tools

Built-in Database Reports in SQL Server Management Studio

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.

Using the Disk Usage Report in SQL Server Management Studio

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