Top Ten SQL Server 7.0 Support Issues and Resolutions
The information in this document was adapted from the TechNet Briefing
Perform an unattended installation
DTS Package permissions
Replication
Database
Troubleshooting the SQL Server services
Stats update
Cursor usage
Deferred updates
Stored procedure recompilation
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:
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 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
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:
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
.gif "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:
8. Deferred Updates
Knowledge Base Article:
238254 - INF: UPDATE Statements May be Replicated as DELETE/INSERT Pairs
More Resources:
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:
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: