Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
SMS: How to Enable SQL Cache Logging in the Systems Management Server Provider
Article ID: 295040 - View products that this article applies to.
This article was previously published under Q295040
This article summarizes the steps that you can take to increase the level of logging when you troubleshoot the Systems Management Server (SMS) Provider. This can be particularly useful when you suspect performance degradation.
To fine-tune logging levels, the SMS SQL Cache Logging Level can be adjusted. The resulting information is compared to the output of running the sp_who2 stored procedure in the SQL Server Query Analyzer.
Be aware that increasing the verbosity of logging generated by the SMS Provider may further contribute to any potential performance issues you are attempting to troubleshoot, because additional information will be produced in log files.
To adjust the SMS SQL Cache Logging Level, follow these steps:
275742Additional information is placed in the "SMSPROV.LOG" log file, typically found at X:\SMS\Logs\ (where X:\ is the drive on which you have SMS installed). Also, another log file, named "SMSPROV_SQLCACHE_<24 hour time format>.LOG" is created in the same directory location.
(http://support.microsoft.com/kb/275742/EN-US/ )SMS: How to Increase the Size of the SMS Provider Log File
SMSPROV.LOG will contain enumerations of each of the SMS objects where security is applied. Each class is enumerated, but not its instances or properties. You may also note additional connection information in the log file.
The file, SMSPROV_SQLCACHE_<24 hour time format>.LOG is produced as a tab-delimited file to identify which cached credentials the SMS Provider is using to communicate with the SMS database. Key items to note are the "Type," the "Thread ID," and the "spid" columns.
Compare this to the SQL Query analyzer (version 7.0 and higher), focused on the SMS database. Type sp_who2, and press <F5> to run the stored procedure. The output will appear in the bottom frame of the query analyzer. Compare the results with the SMSPROV_SQLCACHE_<24 hour time format>.LOG file to determine if there are any differences in number of "spid"'s, or in "TimeLastUsed." You may also find other useful information by matching "spid"'s with the object accessed.
NOTE: Only open connections to the SMS database through the SMS Provider will be displayed, indicating cached credentials. All of the spid's listed from "sp_who2" should match those displayed by the SMS Provider SQL cache log file generated.
If the number of cached connections is higher in either source, the SMS Provider or the SQL Server, then this could indicate problems with the number of allowed SMS Provider connections. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
267237For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/267237/EN-US/ )SMS: Upgrading to SP2 Resets Provider Connections to Default
(http://support.microsoft.com/kb/260372/EN-US/ )SMS: Calculating SQL User Connections for SMS 2.0