Data Collector in SQL Server 2008 – II
Posted by decipherinfosys on June 6, 2008
In part I, we had touched upon Data Collector in SQL Server 2008. In this post, we will pick up the discussion from where we left it. Once you have made the choices, you will get the final screen like the one shown below:
And after pressing Finish, the progress will be shown on the next screen –> you need to ensure that you have the MS SQL Server Agent running else, the steps will fail since it configures the collection jobs as part of this process.
And once it is implemented successfully, now, let’s go to SSMS and see whether our SQL Agent jobs got created correctly and analyze those jobs. As you can see from the image below, we can see that we now have 6 SQL Server Agent jobs created for us and you can see under Data Collection, three System Data Collection Sets: Disk Usage, Query Statistics and Server Activity:
And if you right click on Disk Usage and click on properties, you will see the collection items as well as the SQL that is used for the data collection. You can change the schedule for the run as well as the history that you want to keep.
And it created our database for us: VISHNU_DC which has a bunch of objects that are used for the data storage for the snapshots and for reporting purposes.
It creates three schemas for us: CORE, SNAPSHOTS and CUSTOM_SNAPSHOTS. In the next post, we will look at running some these jobs and collect information and look into the reports that can be used to look into this rich information that is now at our disposal.