Microsoft KB Archive/308888

= FIX: sp_MSdependencies May Run for a Long Time to Script Tables and Views =

Article ID: 308888

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q308888



BUG #: 351186 (SHILOH_BUGS)



SYMPTOMS
With a large database that has 6,000 tables and 15,000 views, the sysdepends system table may have close to 500,000 rows. An attempt to script 10 tables by using only DROP and CREATE table statements took several minutes on an 8-processor computer. The most time was spent on sp_MSdependencies stored procedure, which does not seem necessary because the dependency objects were not requested. According to this speed, to script all the tables and views may take over 10 days, which is a serious performance concern.



CAUSE
The scripting process uses the same SQL Distributed Management Objects (DMO) scripting process that Data Transformation Services (DTS) uses. By design, the scripting process must issue a warning message for each dependency object not transferred or scripted, which requires a search for all the dependency objects regardless of whether they were requested. A large database may have multiple dependency levels. More levels escalate the cost dramatically for searching and generating that warning message.



RESOLUTION
For additional information about the latest service pack for Microsoft SQL Server 2000, click the article number below to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

This problem is fixed in SQL Server 2000 Service Pack 2 (SP2) by the addition of a new registry key that you can use to turn off the warning message.

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

To use the new registry key, use these steps:  Start the Registry Editor (Regedt32.exe). Locate, and then click to select the following key in the registry:

HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL Server/80/Tools/DMO

  On the Edit menu, click Add Value, and then add the following registry value:   Value name: IgnoreTransferScriptWarnings Data type: REG_DWORD Radix:     Decimal Value data: 1  Quit the Registry Editor.

The meaning of the value in Value data is shown in the table that follows.

If the IgnoreTransferScriptWarnings key does not exist, the default behavior is same as before, and the warning messages still display, which means there is no performance improvement. To obtain the fix, you must add the IgnoreTransferScriptWarnings key with a nonzero value after you upgrade to SQL Server 2000 Service Pack 2 (SP2). The IgnoreTransferScriptWarnings key does not work in the SQL Server 2000 or SQL Server 2000 Service Pack 1.

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

Keywords: kbbug kbfix KB308888

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.