INF: Importing Performance Monitor Logs into SQL Server (275685)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q275685

SUMMARY

This article describes how to import Performance Monitor logs that have been saved as a comma-separated text file into a SQL Server table.

MORE INFORMATION

To import Performance Monitor logs into SQL Server, the logs must be set up properly in Performance Monitor. To do this on Microsoft Windows 2000, follow these steps:
  1. Open Performance Monitor and expand Performance Logs and Alerts.
  2. Right-click Counter logs.
  3. Select New Log Settings.
  4. Enter the log name, and then click OK.
  5. Select the desired counters.
  6. Click the Log Files tab.
  7. For Log File Type, choose .csv (comma separated values).
  8. Click Apply, and then click OK.
This sets up the logs so that they can be imported into SQL Server. After the desired monitoring period is complete, follow these steps to import the logs into SQL Server:
  1. In Enterprise Manager, right-click Databases.
  2. Select All Tasks, and then select Import Data.
  3. On the DTS Wizard splash screen, click Next.
  4. In the Data Source window, choose Text File.
  5. Browse to the Performance Monitor log, and click Next.
  6. Make sure that First row has column names is selected, and then click Next.
  7. The column delimiter should be Comma, and then click Next.
  8. Select your desired database or create a new database, and then click Next.
  9. The defaults for the source table are acceptable. If you like, modify the destination table name, and then click Next.
  10. Select a schedule, click Next, and then click Finish.
When this package runs, the database is populated with the results from the log.

The only thing that remains to do is to convert the columns in the database to their appropriate types (datetime, float, integer, and so on). To do this, follow these steps:
  1. In Enterprise Manager, expand Databases and select the database with the Performance Monitor data.
  2. Right-click the table with the data. It should have the same name as the file from which the data was imported. Select Design Table.
  3. All the data types will be varchar; adjust them to fit the data type and then save the design.
  4. You may also want to change the column name to a simpler name. For example, you could use "Lock Blocks" rather than "\\Server123\SQLServer:Memory Manager\Lock Blocks" to make queries easier to work with.
This database can now be queried, modified, and displayed in the same manner as any SQL Server database.
Modification Type:MajorLast Reviewed:10/29/2003
Keywords:kbinfo KB275685
©2003 Microsoft Corporation. All rights reserved.