Export (0) Print
Expand All

Top Ten SQL Server 7.0 Support Issues and Resolutions

The information in this document was adapted from the TechNet Briefing

"How to successfully work through the 10 most frequently reported Microsoft SQL Server 7.0 issues and solutions"

  1. Perform an unattended installation

  2. DTS Package permissions

  3. Replication

  4. Database

  5. Troubleshooting the SQL Server services

  6. Stats update

  7. Cursor usage

  8. Deferred updates

  9. Stored procedure recompilation

  10. Renaming the server

1. Perform an unattended installation

An unattended installation of SQL Server 7.0 or MSDE 1.0 can be automated by using an .iss file that records the responses and selections that would be provided by a user during an interactive installation.

Recommended Options:
The following is an excerpt from Installing MS SQL Server 7.0

To invoke an unattended installation, you must first generate an InstallShield .iss file. Start SQL Server Setup with the k=Rc switch and proceed through the dialog boxes to install SQL Server as normal. Doing this causes Setup to record your dialog box choices in a file named Setup.iss, which is located in your Windows directory — this will not install SQL Server on the local computer. After this process is completed, the file can be moved or copied to another location for use on other servers. For subsequent automated installations, start Setup and specify a previously generated .iss file as input by using the -f1 setup command-line option. The syntax for this command is:

Setupsql.exe -f1 <full path to iss file> -SMS -s

If the -SMS switch is not specified the underlying InstallShield setup process, Sqlstp.exe, starts a process to perform the Setup and will immediately return control back to the user. The -s switch causes Setup to run in a silent mode.

Setup Log Information:

SQLSTP.LOG

  • \Windows or \Winnt directory

  • Tells what setup has done

CNFGSRV.OUT

  • \MSSQL7\INSTALL directory

  • Reports on configuration attempt by cnfgsvr.exe

Tip: How do I install just the client and the profiler? - Choose Client Connectivity and Profiler during Setup

Knowledge Base Articles:

233312 - Customizing MSDE/SQL Server Unattended Installation Files

231735 - Remote Unattended Install

233337 - How to determine when unattended install is complete

More Resources:

Installing MS SQL Server 7.0

SQL Server Setup

2. DTS Package permissions

Frequently, a package that can be run from the DTS Designer, SQL Server Enterprise Manager, or the command line using dtsrun fails to execute when scheduled as a Microsoft SQL Server job. This generally indicates a permissions or network access problem.

Recommended Options:
Job Ownership is key. If a SQL Server job is owned by sa or a member of the sysadmin fixed server role, the package executes under the security context of the account used by the SQLServerAgent service. If not, the job executes under the local SQLAgentCmdExec account.

The local system and SQLAgentCmdExec accounts have no privileges to access files on network resources by default. If the package is accessing files on the network, make sure that the SQLServerAgent service is running under a Microsoft Windows NT domain account and has access to the share.

Because a scheduled job does not run interactively, any mapped drives that were used to access data files do not exist when the job is executed. To avoid this, a package should always use the full UNC path for connections that access files on a shared directory on the network.

Knowledge Base Articles:

242391 - INF: DTS Package Development, Deployment and Performance

197385 - FIX: DTSRun: Package Reports False Success with User Password

3. Replication

Recommended Options:
FTP can be used to download data from publishers to subscribers. Certain settings must be maintained:

  • Publication must be set to allow subscribers to download via FTP

  • Subscribers must choose to download via FTP and enter their credentials

  • Root of FTP site must point to \sqlinstalldirectory\repldata\ftp

  • Permissions must be set so that services can access data

Knowledge Base Article:

195757 - INF: Frequently Asked Questions - Replication

More Resources:

Replication

Replication for MS SQL Server Version 7.0

4. Database

Torn Pages:

  • The torn page option does not add much CPU cost at all, but it can increase contention on "hot" pages. With torn page detection off, a page can be accessed while it is being written to disk. This is not true if torn page detection is on.

  • Torn page detection is checked whenever a page is read from disk. In practice, this is usually during recovery, because any page on which the write did not complete during normal operations is very likely to be read by recovery (except for nonlogged operations, such as index creation and bulk copy).

  • When a torn page is detected, a severe I/O error is raised. This error closes the connection. The database is only marked suspect if the torn page is detected during recovery.

  • Restoring the database from a backup and rolling the transaction log forward should correct the problem with no data loss.

  • Lab tests have shown that torn pages are likely to happen when disk writes are occurring and power is lost. If you do not have a battery backup or uninterruptible power supply (UPS), you should consider enabling the torn page option.

Database/Log Shrinkage:

  • The log will never grow if the database was never backed up

  • The log may not shrink at the exact time of request

  • Need to mark space as available

  • Back up log

  • Insert a few records in order to move off of the current Virtual Log File

Knowledge Base Articles:

195760 - INF: Frequently Asked Questions - Server

230785 - INF: SQL Server 7.0 Logging and Data Storage Algorithms Extend Data Reliability

More Resources:

Chapter 8 - Monitoring Server Performance and Activity

5. Troubleshooting the SQL Server services

MSSQLSERVER and SQLAGENT:Verify the account has log on as a service right.

MSDTC:Only enter the password in Control Panel >> Services

MSSEARCH:Never enter the password in Control Panel >> Services

It uses account/password information from MSSQLSERVER

Tip: make sure you set the agent to autostart it does not restart automatically when mssqlserver starts

Knowledge Base Articles:

196654 - INF: How to Control the SQL Server Services on Windows 95/98

248391 - PRB: Error 1314 Raised By xp_cmdshell When Executed as Non-SA User

255235 - SQL Server Backup to Remote Drive Does Not Work, Generates 'Error 18204 Access is Denied' Message

More Resources:

Starting, Pausing, and Stopping SQL Server

Troubleshooting SQL Server Setup

6. Stats update

SQL Server 7.0 now updates statistics automatically, but how does this work? 195565 explains this in full detail Basic algorithm - When 500 rows + 20% of the table has changed on a table with 1,000 or more rows - UPDATE STATS

Other Issues

  • If Cardinality is less than 6 no more than 500 update every 500 modifications

  • Profiler can trap Auto-Update-Stats Events

  • Trace Flag 8721 can be enabled in order to show stats update in error log. For this message, "Mods" is the total number of modifications to the table. "Bound" is the modification threshold, "Duration" is the amount of time that the UPDATE STATISTICS statement required to complete, and "UpdCount" is the count of updated statistics.

Knowledge Base Article:

195565 - INF: How SQL Server 7.0 Autostats Work

More Resources:

sp_autostats (T-SQL)

Chapter 8 - Monitoring Server Performance and Activity

7. Cursor Usage

What is a Cursor?:

  • Points to a specific row

  • Retrieves and modifies rows based on current position

  • Can be sensitive to changes in underlying data

Transact SQL Cursor Syntax:

Statement

Description

Declare Cursor

Defines cursor structure and allocates resources

Open

Populates a declared cursor with a result set

Fetch

Navigates within a cursor result set

Close

Releases the current result set

Deallocate

Removes cursor definition and deallocates resources

How Transact-SQL cursors work

Cc966538.f09xx02(en-us,TechNet.10).gif

Implicit Cursor Conversion:

  • Cursor references a view with a top clause - Becomes static

  • Query contains distinct, group by, etc. - Becomes static

  • Query references a table with a trigger - Becomes static

  • Query generates an internal worktable (order by) - Becomes keyset

  • Query references remote tables in linked servers - Becomes keyset

Rules for Choosing a Cursor Type:
Some simple rules to follow in choosing a cursor type are:

  • Use default settings for singleton selects (returns one row), or other small result sets. It is more efficient to cache a small result set on the client and scroll through the cache instead of asking the server to implement a cursor.

  • Use the default settings when fetching an entire result set to the client, such as when producing a report. Default result sets are the fastest way to transmit data to the client.

  • Default result sets cannot be used if the application is using positioned updates, or multiple active statements. If cursors are being used only to support multiple active statements, choose fast forward-only cursors.

  • Default result sets must be used for any Transact-SQL statement or batch of Transact-SQL statements that will generate multiple result sets.

  • Dynamic cursors open faster than static or keyset-driven cursors. Internal temporary work tables must be built when static and keyset-driven cursors are opened, but they are not required for dynamic cursors.

  • In joins, keyset-driven and static cursors can be faster than dynamic cursors. Keyset-driven or static cursors must be used if you want to do absolute fetches.

  • Static and keyset-driven cursors increase the usage of tempdb. Static server cursors build the entire cursor in tempdb; keyset-driven cursors build the keyset in tempdb.

  • If a cursor must remain open through a rollback operation, use a synchronous static cursor and set CURSOR_CLOSE_ON_COMMIT to OFF.

Each call to an API fetch function or method causes a roundtrip to the server when using server cursors. Applications should minimize these roundtrips by using block cursors with a reasonably large number of rows returned on each fetch.

Tip:For T-SQL Cursors FWD_ONLY is an option that is applied to a STATIC, DYNAMIC, OR KEYSET cursor type.

Tip:There is a performance optimization called Fast_Forward Cursors.

Knowledge Base Article:

197800 - FIX: Cursor Overhead Higher on SQL Server 7.0 for Small Result Sets

More Resources:

SQL Stored Procedures

Transact-SQL Topics - Part 7

8. Deferred Updates

Knowledge Base Article:

238254 - INF: UPDATE Statements May be Replicated as DELETE/INSERT Pairs

More Resources:

SQL Operators Event Category

New Transact-SQL Statements

9. Stored procedure recompilation

When Is A Stored Procedure Compiled?

  • When a copy of the plan is not available in memory

  • When an index on a referenced table is dropped

  • When updated statistics are available

  • When a table that was referenced in the procedure has been changed with ALTER TABLE

  • Sp_recompile procname

  • With recompile

Tip:How would you best view recompile events and associate them with stored procedures? Use SQL Server Profiler and trap SP:Recompile events as well as objectid and text columns

More Resources:

SQL Stored Procedures

Chapter 8 - Monitoring Server Performance and Activity

10. Renaming the Server

How do I rename the server?

  • Rename the server as you would normally do for Windows NT or Windows 2000

  • Restart as prompted

  • Rerun SQL Server setup

  • Choose reinstall

  • Setup completes quickly

  • Execute sp_dropserver and sp_addserver

More Resources:

Installing MS SQL Server 7.0

Troubleshooting SQL Server Setup

SQL Server Setup

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft