Skip to content

Archive for

How to Schedule a job to collect SQL Server Performance Monitor (PerfMon) counters?

Please follow below steps if you would like to schedule a job to collect SQL Server (or any other OS related) performance counter over the spam of hours/days/etc. Here we will create a manual data collector set and will schedule it to run as per our requirement.

NOTE:- These steps will work on Windows Server 2008 or higher edition only

  • Go to Start –> Run –>  type “Perfmon” –> Enter. Below Screen will open.
  • Expand Data Collector Sets à right click on “User Defined” –> New à Data Collector Set
  • Give name to new data collector set and select “Create Manually”. Click Next. 
  • Select Create data logs and Performance counter. Then click Next.
  • On this screen, select how frequently you want to collect the data. Then Click on Add button to add counters which need to be collected. 
  • This will open below window where you can select the counters to be collected.

In this example we will collect below events/counters to check SQL Server performance:

Selected Buffer cache hit ratio, Free pages, Lazy writes/sec and Page life expectancy events under SQLServer:Buffer Manager counter.

  •  Once the counters are selected Click OK.
  • Here you can view the counters that will be collected. Add/Remove if required. Then click next when ready to go to next step. 
  • Select directory where you want to save the log and click next. 
  • You can set this data collector set to run under different account if required. Here we will just leave it default and just save this new data collector set. Click Finish.
  • Now you will be able to see this new data collector set under “Data Collector Sets”.
    • To start/stop Test_1, just right click on Test_1 and select Start/Stop. You can also schedule this by going to the properties of TEST_1 data collector set.

Windows cannot access the specified device, path or file

You may get below error when you try to run SQL Server 2000 patch exe:-

Windows cannot access the specified device, path or file. You may not have appropriate permission to access the item

You have verified and have full access on all drives and are even Adim on box.

Then where is the issue? The issue is with the file properties.

You will see the file is blocked. This happens when you copy file from some remote location and there is some additional security setup on this server. Just click on UNBLOCK and click Apply. Try to run setup again at it will work.