If you don’t have a centralized monitoring tool, you can log Performance Monitor counters to a file or to a SQL Server database. I recommend using a comma delimited file so that you can open it in your favorite program at a later date or even import it into a SQL Server database.
- Start Performance Monitor.
- Expand the Performance Logs and Alerts option, right-click Counter Logs, and select New Log Settings.
- In the New Log Settings dialog box that opens, enter a name for the group of settings and click OK.
- When the dialog box opens with the name you entered in step 3, select the General tab, click Add Counters, and add all the counters you want to capture.
- Set the interval to a reasonable amount of time. The default is to sample the data every 15 seconds, which might be too often. Every 30 seconds to a minute should be fine—much more often than that might give you an inaccurate picture of your server.
- Click the Log Files tab, and select a log file type of Text File (either comma delimited or tab delineated—just make a note of which one you select). If you’re logging a lot of counters to a database, some events might be missed. To change the location and base name for the incremented file, click Configure.
- Select the Schedule tab. To start logging immediately (or at a specified time), select At in the Start log section and enter the time to start. In the Stop log section, you can opt to stop the logging manually (meaning the file will grow indefinitely), after a certain period of time (measured in seconds, minutes, hours, or days), or at a certain time. Keep in mind that if you’re logging to a file, you want to keep the file sizes manageable. I suggest setting the logging to stop after a certain period of time. Select After in the Stop log section, then select the Start a new log file checkbox, as Figure 1 in the main article shows.
- Click Apply, then click OK.
Main article: SQL Server Consolidation