KnowledgeBase Archive

An Archive of Early Microsoft KnowledgeBase Articles

View on GitHub

Q295040: SMS: How to Enable SQL Cache Logging in SMS Provider

Article: Q295040
Product(s): Microsoft Systems Management Server
Version(s): 2.0
Operating System(s): 
Keyword(s): kbtool kbsms200
Last Modified: 21-AUG-2001

-------------------------------------------------------------------------------
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 <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:

  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.