Logging Perfmon Counters to SQL 2005

Question

Logging Perfmon Counters to SQL 2005

Randy Dray

SSC Enthusiast

Points: 128
More actions
October 10, 2007 at 6:35 am

#182521

Hello,
I have set up a database on my Development server Called SQL_Monitor.
I am trying to log the Perfmon Counters to the database.
I followed the steps in http://support.microsoft.com/kb/296222.
I keep getting errors in the event log for
Event Type:Warning
Event Source:SysmonLog
Event Category:None
Event ID:2004
Date:10/9/2007
Time:3:24:27 PM
User:N/A
Computer:SQL-HELP
Description:
The service was unable to open the log file SQL:SQLMon!SQLMon for log SQLMon and will be stopped. Check the log folder for existence, spelling, permissions, and ensure that no other logs or applications are writing to this log file. You can reenter the log file name using the configuration program. This log will not be started. The error returned is: .
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: e1 0b 00 c0 á..À
Any Ideas?

Viewing 8 posts - 1 through 7 (of 7 total)

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

rroman81 SSC Journeyman Points: 76 More actions · Answer 1

I have the same scenario set up. I am getting the same error. In addition, I am getting Event ID 3042 from Source: PDH stating that ODBCBCP.dll is not compatible with SQL Native Client driver.

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 2

[font="Comic Sans MS"]For Randy:

Kindly check the path where you are storing the perform results eg. "C:\perfmon\test.csv" and make sure the path exists and you have the right permission on the folder.

For rroman81:

Refer the below link, i think this may help you.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=476173&SiteID=1[/font]

Regards..Vidhya Sagar
SQL-Articles

mortenlundgreen SSC Rookie Points: 25 More actions · Answer 3

Hi,

I get the event 2004 also. I made a log with type sql, so I therefore don't see any use for storing the log in the file system (e.g. "C:\perflogs"). It is extremely difficult to get this sql database logging to work.

Regards

Morten

JenH SSC-Addicted Points: 461 More actions · Answer 4

Any problems testing the ODBC system DSN that you set up with connection info to your target perfmon database? And did you use SQL Server for this DSN? Using the SQL Native Client type doesn't work. We set up the database, set up the system DSN with windows account that had SA privileges and the appropriate database connection info and perfmon did the rest in terms of creating the tables in the database.

On a related note, can anyone point me in the direction of a source for querying the perfmon database? I can use perfmon to read the database, but I'd like to put some queries into one of our dba monitoring reports and the data organization is a bit trickier than the csv log file format.

Thanks,

Jen

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 5

Jen Hutchinson (1/30/2009)
On a related note, can anyone point me in the direction of a source for querying the perfmon database?

Here's a partial example. Assuming that Processor counters have been collected this returns the minimum, maximum and average values grouped by 30 minute intervals.

SELECT t2.objectname, t2.countername,

DATEADD( minute,

(DATEPART(minute,CAST(SUBSTRING(t1.counterdatetime,1,23) AS DATETIME))/30)*30,

DATEADD(hour,DATEDIFF(hour,0,CAST(SUBSTRING(t1.counterdatetime,1,23) AS DATETIME)),0)) AS Period,

AVG(t1.countervalue) AS [Avg],

MIN(t1.countervalue) AS [Min],

MAX(t1.countervalue) AS [Max]

FROM counterdata t1 INNER JOIN counterdetails t2 ON t2.counterid=t1.counterid

WHERE t2.countername = '% Processor Time' AND t2.instancename = '_Total'

AND t2.objectname = 'Processor'

GROUP BY DATEADD( minute, (DATEPART(minute,CAST(SUBSTRING(t1.counterdatetime,1,23) AS DATETIME))/30)*30,

DATEADD(hour,DATEDIFF(hour,0,CAST(SUBSTRING(t1.counterdatetime,1,23) AS DATETIME)),0)),

t2.objectname, t2.countername

ORDER BY Period

References:

Converting the char(24) date and time from perfmon to a SQL datetime.

http://www.resquel.com/ssb/2008/11/24/RelogSQLDatabaseSchemaNastyDatetimeHandling.aspx

SQL Log File Schema

http://msdn.microsoft.com/en-us/library/aa373198(VS.85).aspx

Phani-345480 SSC Veteran Points: 228 More actions · Answer 6

Great that works! - Givign permission on the C:\PerfLogs folder...

kenny-892481 SSC Journeyman Points: 91 More actions · Answer 7

@SSC Eights! – I've been looking for that link ('SQL Log File Schema') for months! Thanks!