ID: Q166244
The information in this article applies to:
Systems Management Server requires several SQL Server configuration options to be set correctly to perform at maximum efficiency. This article summarizes the recommended configuration options for a SQL Server running a Systems Management Server database.
Named Pipes network support is required for Systems Management Server to use to communicate with the Systems Management Server database. You can change SQL Server network support by running SQL Server setup, selecting the Change Network Support option, and then selecting Named Pipes as an installed network.
Tempdb database options enabled:
Select Into/ Bulk Copy
Truncate Log on CheckPoint
Columns Null by Default
No CheckPoint on Recovery
Single User
DBO Use Only
Read Only
Truncate Log on CheckPoint
Select Into/ Bulk Copy
Columns Null by Default
No CheckPoint on Recovery
Single User
DBO Use Only
Read Only
In SQL Server 4.2a, 6.0, and 6.5, you can change the database options by using the SP_DBOPTION stored procedure.
SQL Server Memory: Set the SQL Server memory appropriately. This is the amount of RAM dedicated to SQL Server. This setting depends on the amount of physical RAM in the computer and the usage and performance requirements of SQL Server. Memory is designated in 2-KB blocks. For example, for a dedicated SQL Server with 128 megabytes (MB) of RAM, you may want to set the memory to 64 MB of RAM (32,768 2-KB blocks) to SQL Server. On a SQL Server and Systems Management Server site server with 128 MB of RAM, you may only want to dedicate 40 MB of RAM (20,480 2-KB blocks) to SQL Server.
SQL Server open Objects: Set open objects to 5,000-7,000, depending on the size of your site and the child sites below it. The SQL Server default for open objects is 500, which is not adequate for a small SQL Server running Systems Management Server. Symptoms of open objects being set too low on a SQL Server include poor Systems Management Server or SQL Server performance, a backlog of deltamifs or .mif files in the Systems Management Server directory structure, or delays in inventory, package distribution, and job status MIF processing.
SQL Server User Connections: Set user connections appropriately. Each user connection takes 40 KB of RAM, so this value is determined by the amount of memory dedicated SQL Server and the number of concurrent connections required. Each Systems Management Server site server reporting to a SQL Server requires at least 10 connections. Each running instance of the Systems Management Server Administrator program and the SQL Enterprise Manager requires at least one more connection.
Tempdb in RAM: Microsoft does not recommend placing tempdb in RAM on a SQL Server running Systems Management Server.
In SQL Server 6.0 and 6.5, you can change the SQL Server configuration options through the SQL Enterprise Manager user interface by clicking SQL Server Configure on the Server menu. From there, click the Configuration tab.
In SQL Server 4.2a, 6.0, and 6.5, you can change the configuration options by using the SP_CONFIGURE stored procedure.
If you make any changes to these parameters, stop and restart the MSSQLServer service.
Please refer to your SQL Server documentation or online Help for more information on these settings.
A regularly scheduled database dump, along with backup of the Systems Management Server registry and directory structure, is a mandatory part of a good backup and recovery procedure.
For more information, see the following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q166967
TITLE : INF: Proper SQL Server Configuration Settings
Keywords : kbinterop kbnetwork smsconfig smsdatabase
Version : 1.0 1.1 1.2
Platform : WINDOWS
Issue type : kbhowtoLast Reviewed: April 14, 1998