Select the product you need help with

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

SUMMARY

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.

MORE INFORMATION

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:

Start Registry Editor (Regedt32.exe).
Locate the SQL Cache Logging Level value under the following key in the registry:
[ HKEY_LOCAL_MACHINE \ Software \ Microsoft \ SMS \ Providers ]
On the Edit menu, click DWORD, type 1 as the Data, and then click OK.
Quit Registry Editor.
Restart the SMS Provider. This can be done by stopping and restarting the Windows Management Instrumentation service.

Additionally, before you restart the SMS Provider, you may want to increase the size of the SMS Provider log file. For additional information about how to increase the size of the SMS Provider log file, click the article number below to view the article in the Microsoft Knowledge Base:

275742

(http://support.microsoft.com/kb/275742/EN-US/ )

SMS: How to Increase the Size of the SMS Provider Log File

Additional 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.

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:

267237

(http://support.microsoft.com/kb/267237/EN-US/ )

SMS: Upgrading to SP2 Resets Provider Connections to Default

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

260372

(http://support.microsoft.com/kb/260372/EN-US/ )

SMS: Calculating SQL User Connections for SMS 2.0