SQL Server 2008 R2 Data Collection reports

  • I am new to the Data Collection Utility. It looks as if it would provide valuable Trending information as well as other useful reports. Is there a way to have the information delivered in an EXCEL or PDF format via email?

    Thank you in advance,

    Charlie

  • I am not clear on what you are calling "Data Collection Reports" or "Data Collection Utility" I know a Data Collection Repository as a database.

    If you could clarify what you mean by "Data Collection Utility", we may be able to help you.

    Yes, you can always pump the data out of a database into many formats. Simply use SSIS or BCP for this. Without a means to extract the data in a database would be insane.

    Andrew SQLDBA

  • Hi Andrew,

    I configured the "Data Collection" located under "Management" in SQL Server Management Studio. There are three reports that are set up... Server Activity History, Disk Usage Summary, and Query Statistic History. The Disk Usage Report looks like iti would be great for disk trending. I am able to right click on the report and save to "XLS" or "PDF" formats. I was wondering if there were a way to automate this to have this infomration sent to our Manager of the Engineering department.

    Sorry if I have the termilogy incorrect.

    Charlie

  • That report is merely running queries against the database box. All this data can be easily queried once you write the code. You can then schedule that in a SQL job for execute, or if needed, create a SSIS package that can do even more. Pretty much anything that you want.

    I do not think there is a way to schedule the packaged queries that make up these simple reports. But they are nothing more than a query that runs on SQL Reporting Services in SSMS.

    I would suggest that you keep this data in a DatabaseMaintenance database for historical reporting. And that will keep the transactions low on the database(s) involved. The data will be queries only once, gathered and inserted into the proper tables. Then SSRS can use the DatabaseMaintenance database as one of the data sources. You could use any front-end that you like. Or you can create another SSIS Package that send this out to a select group. I would use a web based front-end so that they can look at the data anytime they want, and over and over without straining the database box.

    Andrew SQLDBA

  • I have tried to mimic or copy the code that's being executed when these reports are generated, with very little luck though. Anyone else had any luck either creating custom reports off this data or copying the existing reports? My goal is to create a set of reports in SSRS that can then be scheduled. Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply