DOCUMENT:Q295040 21-AUG-2001 [sms] TITLE :SMS: How to Enable SQL Cache Logging in SMS Provider PRODUCT :Microsoft Systems Management Server PROD/VER::2.0 OPER/SYS: KEYWORDS:kbtool kbsms200 ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Systems Management Server version 2.0 ------------------------------------------------------------------------------- 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: 1. Start Registry Editor (Regedt32.exe). 2. Locate the SQL Cache Logging Level value under the following key in the registry: [ HKEY_LOCAL_MACHINE \ Software \ Microsoft \ SMS \ Providers ] 3. On the Edit menu, click DWORD, type 1 as the Data, and then click OK. 4. Quit Registry Editor. 5. 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: Q275742 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" (without the quotation marks), and press 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: Q267237 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: Q260372 SMS: Calculating SQL User Connections for SMS 2.0 Additional query words: prodsms smsprov sqlcache ====================================================================== Keywords : kbtool kbsms200 Technology : kbSMSSearch kbSMS200 Version : :2.0 Issue type : kbinfo ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2001.