SQL Server Q&A

Symptoms
This release of Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0 Service Pack 4 (SP4) provides updates for the database components of a SQL Server 7.0 or MSDE 1.0 installation. This article introduces the following information about SP4:
Service Pack versions and the removal of Service Pack 4
How to download andto extract Service Pack 4
How to install Service Pack 4
How to redistribute database components and Service Pack 4 client components
Explains the issues of running Service Pack 4 that affect sites
Resolution
IntroductionService pack versionsRemoving SP4Downloading and extracting SP4Disk space requirements for SP4Extracting Database Components SP4Service pack installationBack up SQL Server databasesMake sure the system databases have sufficient free spacePrepare cluster configurationsStop applications before you run the Service Pack 4 Setup programInstall Database Components SP4Restart servicesRecluster a cluster configurationRestart applicationsInstalling on replicated serversInstalling SP4 on a server that provides a remote distribution database for merge publicationsUpgrading a merge Publisher that receives new subscriptions to SP4 from the original release of SQL Server 7.0Installing SP4 on a merge replication SubscriberRunning SP4 replication with earlier versions of SQL ServerUpdating Access 2000 (Jet 4.0) merge replication SubscribersRedistributing Database Components SP4 client componentsDocumentation notesUpgrading client-only computers after you install SP4Upgrading Japanese database servers to Windows 2000 after you install SP4Applying SP4 to a later versionInstalling SP4 on Windows NT 4.0, Terminal Server EditionNew Troubleshooting GuideReplication enhancements Optimizing replication synchronization Performance enhancements for merge processing of the initial snapshot Merge metadata cleanupChanges in Database Components New database option New sqlservr startup option Change in cursor behavior Changes in SQL Server ProfilerSQL Server clustering white paperData Transformation ServicesExporting to Oracle databasesUsing the DTS Test featureChanges to the Execute Package dialog boxData Transformation Services error messagesUse of the Set keyword in Microsoft Visual Basic scriptsChanges in DTS packages SQL Server Agent proxy account improvementsSQL Server 7.0 and Exchange 5.5The French version of SQL Server Books OnlineRepository changesImporting from DB2 and Informix databasesImproved retrieval of versioned objects

Symptoms
The purpose of this article is to help you in the setup or in the troubleshooting of a configuration for a COM+ Application Server or a configuration for an Internet Information Server (IIS) computer that interacts with a clustered server that uses Microsoft Cluster Server (MSCS) that is behind a firewall.
You may have one of the following scenarios: COM+ or IIS computer (client computer)SQL Server clustered that uses MSCSMicrosoft Distributed Transaction Coordinator (MSDTC) as a clustered resource in its own resource group (own name and IP address)Cluster and client computer that are separated by a firewall Certain Internet Protocol (IP) addresses and their corresponding network names must be known by the client computer for MSDTC to work correctly. The client computer can resolve the following names and the following IP addresses by using Domain Name System (DNS), hosts file, or another name resolution method: MSDTC resourceAn instance of SQL Server if the cluster configuration is either active-passive or active-activeCluster Name Certain IP addresses and their corresponding network names must be known by the nodes in the cluster for MSDTC to work correctly. Both nodes in the cluster can resolve the client computer name to an IP address by using DNS, hosts file, or another name resolution method.
Resolution
Additionally, the firewall must be configured to allow bidirectional traffic to occur between the client computer and the cluster. The firewall rules must include the following: The IP network names and the addresses of both physical nodes on the clusterThe SQL Server Instances network names and addressThe client network name and addressesThe child network name and IP resource of the MSDTC Resource Firewall rules must include the range of IP ports that are defined in the registry to allow traffic. See the “References” section.
You may also have to open an additional range of available IP ports as a requirement for the cluster server. See the “References” section for more information.

Symptoms
The default autogrow and autoshrink settings will work for you with no tuning on many SQL Server systems. However, there are environments where you do not have to turn the settings on or where you may have to adjust the autogrow and autoshrink parameters. This article gives you some background information to guide you when you select the settings for your environment.
Resolution
Here are some things to consider if you decide to tune your autogrow and autoshrink parameters.How do I configure the settings?You can configure the autogrow and autoshrink settings by using one of the following:An ALTER DATABASE statement (not available in SQL Server 7.0)SQL Server Management Studio or SQL Enterprise ManagerThe sp_dboption stored procedure (deprecated in SQL Server 2005)Note If you are using SQL Server 2005, use SQL Server Management Studio instead ofSQL Enterprise Manager. For more information about how to set these settings in SQL Server 2005, visit the following Microsoft Developer Network (MSDN) Web sites:
How to: Add Data or Log Files to a Database (SQL Server Management Studio)
http://msdn2.microsoft.com/en-us/library/ms189253.aspx(http://msdn2.microsoft.com/en-us/library/ms189253.aspx)
Database Properties (Files Page)
http://msdn2.microsoft.com/en-us/library/ms180254.aspx(http://msdn2.microsoft.com/en-us/library/ms180254.aspx)You can also configure the autogrow option when you create a database.
You can view the current settings through the database properties in SQL Enterprise Manager (SEM). Or, you can run the following Transact-SQL command:

sp_helpdb [ [ @dbname= ] ‘name’ ]Keep in mind that the autogrow settings are per file. Therefore, you have to set them in at least two places for each database (one for the primary data file and one for the primary log file). If you have multiple data and/or log files, you must set the options on each file. Depending on your environment, you may end with different settings for each database file.What are the performance implications?If you run a transaction that requires more log space than is available, and you have turned on the autogrow option for the transaction log of that database, then the time it takes the transaction to complete will include the time it takes the transaction log to grow by the configured amount. If the growth increment is large or there is some other factor that causes it to take a long time, the query in which you open the transaction might fail because of a timeout error. The same sort of issue can result from an autogrow of the data portion of your database. To change your autogrow configuration, see the “ALTER DATABASE” topic in SQL Server Books Online.If you run a large transaction that requires the log to grow, other transactions that require a write to the transaction log will also have to wait until the grow operation completes.If you combine the autogrow and autoshrink options, you might create unnecessary overhead. Make sure that the thresholds that trigger the grow and shrink operations will not cause frequent up and down size changes. For example, you may run a transaction that causes the transaction log to grow by 100 MB by the time it commits. Some time after that the autoshrink starts and shrinks the transaction log by 100 MB. Then, you run the same transaction and it causes the transaction log to grow by 100 MB again. In that example, you are creating unnecessary overhead and potentially creating fragmentation of the log file, either of which can negatively affect performance.Physical fragmentation from changing the size of the data or log files can have a severe affect on your performance. This is true whether you use the automatic settings or whether you manually grow and shrink the files frequently.If you grow your database by small increments, or if you grow it and then shrink it, you can end up with disk fragmentation. Disk fragmentation can cause performance issues in some circumstances. A scenario of small growth increments can also reduce the performance on your system.Best PracticesFor a managed production system, you must consider autogrow to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow.You can use alerts or monitoring programs to monitor file sizes and grow files proactively. This helps you avoid fragmentation and permits you to shift these maintenance activities to non-peak hours.AutoShrink and autogrow must be carefully evaluated by a trained Database Administrator (DBA); they must not be left unmanaged.Your autogrow increment must be large enough to avoid the performance penalties listed in the previous section. The exact value to use in your configuration setting and the choice between a percentage growth and a specific MB size growth depends on many factors in your environment. A general rule of thumb to you can use for testing is to set your autogrow setting to about one-eight the size of the file.Turn on the <MAXSIZE> setting for each file to prevent any one file from growing to a point where it uses up all available disk space.Keep the size of your transactions as small as possible to prevent unplanned file growth.Why do I have to worry about disk space if size settings are automatically controlled?The autogrow setting cannot grow the database size beyond the limits of the available disk space on the drives for which files are defined. Therefore, if you rely on the autogrow functionality to size your databases, you must still independently check your available hard disk space. The autogrow setting is also limited by the MAXSIZE parameter you select for each file. To reduce the possibility of running out of space, you can monitor the Performance Monitor counter SQL Server: Databases Object ata File(s) Size (KB) and set up an alert for when the database reaches a certain size.Unplanned growth of data or log files can take space that other applications expect to be available and might cause those other applications to experience problems.The growth increment of your transaction log must be large enough to stay ahead of the needs of your transaction units. Even with autogrow turned on, you can receive a message that the transaction log is full, if it cannot grow fast enough to satisfy the needs of your query.SQL Server does not constantly test for databases that have hit the configured threshold for autoshrink. Instead, it looks at the available databases and finds the first one that is configured to autoshrink. It checks that database and shrinks that database if needed. Then, it waits several minutes before checking the next database that is configured for autoshrink. In other words, SQL Server does not check all databases at once and shrink them all at once. It will work through the databases in a round robin fashion to stagger the load out over a period of time. Therefore, depending on how many databases on a particular SQL Server instance you have configured to autoshrink, it might take several hours from the time the database hits the threshold until it actually shrinks.

Symptoms
The Rebuild master utility (rebuildm.exe) provided with Microsoft SQL Server 2000 stops responding in the Server Configuration step if the source directory that contains data files is on a CD-ROM.
For example: <MyCDDriveLetter>:\x86\data
NOTE: You must terminate the action manually.
The following error message may occur:

Rebuild Master failed with error -1:
The error occurred during server configuration. Refer to install\cnfgsvr.out and the log\errorlog files in C:\Program Files\Microsoft SQL Server\MSSQL for diagnostic information.
Resolution
Rebuildm.exe needs the original master, pubs, msdb, and northwind files (both data and log). Rebuildm.exe copies these files into the installation directory of SQL Server 2000. When the copy completes, rebuildm.exe leaves all those files with the Read-only attribute set to on, which then prevents SQL Server from starting.

Symptoms
When you adjust the maximum worker threads configuration by using code similar to the following:

sp_configure ‘max worker threads’ The run value is updated immediately and you do not have to stop and restart the SQL Server service. However, the User Mode Scheduler picks up the newly configured max worker threads only after you stop and then restart the SQL Server service.
Resolution
Stop and restart SQL Server so that the new configuration settings can take effect.

Symptoms
If you try to connect a clustered named instance of SQL Server through a firewall, and you use only the instance name (for example,SQL_Virtual_Name\Instance_Name) in the connection string, the connection fails and you may receive one of the following error messages:
Error message 1

Specified SQL server not found.
Error message 2

SQL Server does not exist or access denied.If you run a network trace on the server, you can see that the server actually receives the query from the client computer on UDP port 1434 for the virtual server IP address; however, the answer is sent with the physical IP address of the cluster node that is currently running SQL Server.
Resolution
When a client computer connects to a computer that is running a clustered instance SQL Server, if the connection string does not specify the destination TCP port, the client library queries the server on port UDP 1434 to collect information about the instance.
When the server returns the information, the network frame contains the IP address of the physical node instead of the IP address of the virtual server. Depending on the firewall configuration, this network packet may be dropped, and the client may not receive any answer.