MS SQL Server Diagnostics

Microsoft® SQL Server
Version 7.0
For Microsoft Windows NT ® and Windows ® 95/98 Operating Systems
Microsoft Corporation 

Contents

Introduction

Finding Information on the Internet

Documentation Conventions

Overview of Printed Documentation

Part 1 Optimizing Database Performance

Chapter 1 Database Design

Logical Database Design

Database Design Considerations: Data Types

Physical Database Design

RAIDDeveloping a Drive Performance StrategyRAID Levels and SQL ServerComparing Different Implementations of RAID LevelsAbout Hardware-based SolutionsAbout Windows NT-based Disk Striping and Striping with ParityAbout Windows NT-based Disk Mirroring and DuplexingPartitioningData Placement Using FilegroupsPlacing Tables on FilegroupsPlacing Indexes on FilegroupsIndex Tuning RecommendationsOptimizing Transaction Log PerformanceOptimizing tempdb PerformanceFile Systems

Chapter 2 Query Tuning

Analyzing a Query

Graphically Displaying the Execution Plan Using SQL Server Query AnalyzerLogical and Physical OperatorsAssertAggregateBookmark LookupClustered Index DeleteClustered Index InsertClustered Index UpdateClustered Index ScanClustered Index SeekCollapseCompute ScalarConcatenationConstant ScanCross JoinDeleteDeleted ScanDistinctDistinct SortDistribute StreamsEager SpoolFilterFlow DistinctFull Outer JoinGather StreamsHash MatchHash Match RootHash Match TeamIndex DeleteIndex InsertIndex ScanIndex SeekIndex SpoolIndex UpdateInner JoinInsertInserted ScanLazy SpoolLeft Anti Semi JoinLeft Outer JoinLeft Semi JoinLog Row ScanMerge IntervalMerge JoinNested LoopsParallelismParameter Table ScanRemote DeleteRemote InsertRemote QueryRemote ScanRemote UpdateRepartition StreamsRight Anti Semi JoinRight Outer JoinRight Semi JoinRow Count SpoolSequenceSortSplitStream AggregateTable DeleteTable InsertTable ScanTable SpoolTable UpdateTopUnionUpdateCursor Logical and Physical Operators

Query Tuning Recommendations

Advanced Query Tuning Concepts

Understanding Nested Loops JoinsUnderstanding Merge JoinsUnderstanding Hash Joins

Chapter 3 Application Design

Networking and Performance

Named Pipes vs. TCP/IP Sockets

Optimizing Application Performance Using Efficient Data Retrieval

Effects of Transactions and Batches on Application Performance

Effects of Stored Procedures on Application Performance

Understanding and Avoiding Blocking

Optimizing Distributed Queries

Chapter 4 Optimizing Utility and Tool Performance

Optimizing Backup and Restore Performance

Optimizing Bulk Copy Performance

Optimizing DBCC Performance

Chapter 5 Optimizing Server Performance

Optimizing Server Performance Using Memory Configuration Options

Optimizing Server Performance Using I/O Configuration Options

Optimizing Server Performance Using Windows NT Options

Maximizing ThroughputConfiguring Server TaskingConfiguring Virtual Memory

Part 2 Troubleshooting

Chapter 6 Troubleshooting

Frequently Asked Questions

Reporting Errors to Your Primary Support Provider

Reproducing Problems

Isolating Connection Problems

Orphaned SessionsNamed Pipes Client ConnectionsTCP/IP Sockets Client Connections

Chapter 7 Troubleshooting SQL Server Setup

Setup Troubleshooting: Checklist

Testing an Installation of SQL Server 7.0

SQL Server Setup Frequently Asked Questions

Informational Files Created by SQL Server Setup

Contents of the Sqlstp.log file

Scripting

Troubleshooting the SQL Server Upgrade Wizard

Completing the SQL Server Upgrade WizardUpgrade Log Files

Chapter 8 Server and Database Troubleshooting

Resetting the Suspect Status

Troubleshooting Alerts

Troubleshooting Backing Up and Restoring

Troubleshooting Orphaned Users

Troubleshooting Data Transformation Services

Troubleshooting Locking

Troubleshooting MSSQLServer or SQLServerAgent Services User Accounts

Troubleshooting Full-Text Search

Before Uninstalling Site Server 3.0Presence of UNC path in PATH Environment Variable

Troubleshooting Multiserver Jobs

Troubleshooting the Operating System

Appearance of Internet Connection Dialog Box at Startup of Windows 95/98Thread PoolingInsufficient Virtual Memory on the Server

Insufficient Resource Space

Determining When SQL Server Causes a Windows NT Blue ScreenWinlogon Problem Caused by SQL Extensible Performance CountersResource LeakCPU Monopolization

Troubleshooting Recovery

Recovery PerformanceInsufficient Disk Space

Troubleshooting Replication

Troubleshooting Statistics

Chapter 9 SQL Server Tools Troubleshooting

Troubleshooting the Index Tuning Wizard

Troubleshooting SQL Mail with Exchange Server

Troubleshooting SQL Server Profiler

Troubleshooting SQL Server Query Analyzer

Troubleshooting the Web Assistant Wizard

Chapter 10 Programming Troubleshooting

Troubleshooting the SQL Server ODBC Driver

Diagnosing ODBC Connection ErrorsDiagnosing General ODBC Errors

Troubleshooting SQL-DMO

Part 3 Error Messages

Chapter 11 Error Messages

Error Message Formats

Error Message Numbers and DescriptionsError Message Severity LevelsSeverity Levels 0 through 19Severity Levels 20 through 25ADO Error Message FormatOLE DB Error Message FormatODBC Error Message FormatEmbedded SQL for C Error Message FormatDB-Library Error Message FormatMessages Returned by SQL Server 7.0 Utilities

Finding Supplemental Error Message Information

Adding User-Defined Error Messages

Chapter 12 System Error Messages

Errors 1 - 999

Errors 1000 - 1999

Errors 2000 - 2999

Errors 3000 - 3999

Errors 4000 - 4999

Errors 5000 - 5999

Errors 6000 - 6999

Errors 7000 - 7999

Errors 9000 - 9999

Errors 10000 - 10999

Errors 11000 - 11999

Errors 13000 - 13999

Errors 14000 - 14999

Errors 15000 - 15999

Errors 16000 - 16999

Errors 17000 - 17999

Errors 18000 - 18999

Errors 19000 -19999

Errors 20000 - 20999

Errors 21000 -21999

Chapter 13 Resolving System Error Messages

Error 103

Error 107

Error 109

Error 137

Error 156

Error 170

Error 207

Error 208

Error 229

Error 220

Error 245

Error 259

Error 266

Error 268

Error 511

Error 515

Error 544

Error 601

Error 602

Error 605

Error 624

Error 625

Error 644

Error 701

Error 813

Error 822

Error 823

Error 844

Error 845

Error 911

Error 913

Error 924

Error 926

Error 945

Error 1002

Error 1105

Error 1203

Error 1204

Error 1205

Error 1505

Error 1508

Error 1510

Error 1530

Error 1702

Error 1803

Error 1814

Error 1902

Error 1903

Error 1904

Error 1910

Error 1916

Error 2501

Error 2511

Error 2513

Error 2535

Error 2601

Error 2750

Error 2751

Error 2812

Error 3023

Error 3036

Error 3101

Error 3120

Error 3143

Error 3149

Error 3154

Error 3155

Error 3206

Error 3209

Error 3227

Error 3242

Error 3247

Error 3249

Error 3251

Error 3256

Error 3258

Error 3263

Error 3267

Error 3414

Error 3604

Error 3627

Error 4207

Error 4208

Error 4214

Error 4305

Error 4306

Error 4318

Error 5013

Error 5701

Error 5808

Error 6103

Error 7130

Error 7303

Error 7304

Error 7306

Error 7314

Error 7321

Error 7356

Error 7357

Error 7391

Error 7392

Error 7399

Error 7403

Error 7413

Error 8101

Error 8102

Error 8106

Error 8114

Error 8163

Error 8501

Error 8645

Error 8651

Error 8906

Error 8908

Error 8925

Error 8952

Error 8956

Error 8976

Error 9002

Error 17050

Error 17809

Error 17824 (Open Data Services)

Error 18456

Error 18458

Error 18459

Read/Write Error

Server Is Unnamed

Error Log Messages

MAPI Error Messages

Chapter 14 DB-Library Error Messages

DB-Library Error Severities

Error 10008 (DB-Library)

Error 10024 (DB-Library)

Error 10053 (DB-Library)

Error 10054 (DB-Library)

Distributed Queries Error Messages

Embedded SQL for C Error Messages

Chapter 15 SQL Server Enterprise Manager Error Messages

(+) operator ignored.

(+) table reference may not be joined with more than one table.

<0s> in expression is not part of the query.

<0s> is not supported.

<0s> may not be used in this query type.

<0s> support is not available in this server version.

A blank constraint expression is not allowed.

A number of rows were affected.

A pair of matching columns is required to create a relationship.

A primary key can't be created on column '<0s>' because it allows null values.

A primary key or index cannot be created on columns with a datatype of <0s>.

A primary key or index cannot have more than <0d> columns.

A primary key or unique constraint must be defined for table '<0s>' before it can participate in a relationship.

A relationship cannot contain more than '<0d>' columns.

Ambiguous outer join (+) operator.

An index already exists for table '<0s>' with the columns '<1s>'.

An index can't exist on a blank column.

Appropriate SQL cannot be generated.

Are you sure you want to delete the current selection from your database?

Are you sure you want to delete the selected relationship from your database?

Are you sure you want to permanently delete table '<0s>' from your database?

Are you sure you want to permanently delete the selected tables from your database?

Are you sure you want to remove the selected table from the diagram?

Are you sure you want to remove the selected tables from the diagram?

Bad top value.

Both sides of a relationship must have the same number of columns.

Cannot add this expression to the select list.

Cannot assign an alias to this field.

Cannot convert entry to valid date/time value.

Cannot convert to proper type.

Cannot delete rows with unknown keys.

Cannot edit rows with unknown keys.

Cannot edit this cell.

Cannot filter this expression.

Cannot insert into this expression.

Cannot open encrypted <0s> <1s>.

Cannot update this expression.

Cannot use column with IMAGE or TEXT data type in this context.

Cannot use LONG data type in this context.

Column '<1s>' in table '<0s>' participates in index '<2s>'. Columns with a datatype of '<3s>' cannot participate in indexes or primary keys.

Column list is not supported for Make Table queries.

Column with (+) operator does not reference a valid table.

Data source alias is read only.

Data source base name is read only.

Data type error in expression.

Data type mismatch - no conversion possible.

Data type mismatch - use the CONVERT function.

DELETE statement conflicted with COLUMN REFERENCE constraint.

Do you want to suppress further error messages telling you why records can't be pasted?

Editing this default will permanently unbind the named default.

Error after function '<0s>'.

Error after predicate near '<0s>'.

Error before EXISTS clause.

Error before EXISTS clause: '<0s>" not recognized.

Error before FROM clause.

Error following UNION operator.

Error in column list.

Error in column list: '<0s>' not recognized.

Error in destination table specification.

Error in FROM clause near '<0s>'.

Error in GROUP BY clause.

Error in GROUP BY clause near '<0s>'.

Error in HAVING clause.

Error in HAVING clause near '<0s>'

Error in INSERT statement.

Error in INSERT statement: '<0s>' not recognized.

Error in join expression.

Error in join expression: '<0s>' not recognized.

Error in list of function arguments: '<0s>' not recognized.

Error in list of values.

Error in list of values in IN clause.

Error in list of values in IN clause: '<0s>' not recognized

Error in list of values: '<0s>' not recognized.

Error in ON clause near '<0s>'.

Error in optional FROM clause.

Error in ORDER BY clause.

Error in ORDER BY clause near '<0s>'.

Error in SELECT clause: alias '<0s>' not recognized.

Error in SELECT clause: alias not recognized.

Error in SELECT clause: expression near '<0s>'.

Error in set list in UPDATE clause.

Error in table name or view name in DELETE clause.

Error in table name or view name in DELETE clause: '<0s>' not recognized.

Error in table name or view name in INSERT clause.

Error in table name or view name in INSERT clause: '<0s>' not recognized.

Error in table name or view name in UPDATE clause.

Error in text following query statement: '<0s>'.

Error in values list in INSERT INTO clause.

Error in values list in INSERT INTO clause: '<0s>' not recognized.

Error in WHERE clause near '<0s>'.

Error modifying column properties.

Function argument count error.

Identity column '<0s>' in table '<1s>' must have a datatype of int, smallint, tinyint, decimal or numeric with scale of 0.

Ignoring illegal use of ALL.

Ignoring ODBC syntax.

Illegal expression list usage.

Illegal sequence use.

Illegal use of outer join operator.

Incomplete column list.

Incomplete SET clause.

Incomplete values list.

Invalid entries must be resolved before you can exit this pane.

Invalid escape character.

Invalid identifier: '<0s>'.

Invalid name. You must provide a name for this object.

Invalid or missing expression.

Invalid or missing expression near '<0s>'.

Invalid prefix or suffix characters.

Invalid row index: Goto failed.

Invalid text or symbol.

Invalid view name.

Missing escape character in LIKE predicate.

Missing FROM clause.

Missing FROM clause near '<0s>'.

Missing FROM keyword.

Missing FROM keyword near '<0s>'.

Missing INTO keyword.

Missing INTO keyword near '<0s>'.

Missing or incomplete SELECT clause.

Missing pattern in LIKE predicate.

Missing SET keyword.

Missing subquery.

Missing subquery correlation name.

Missing subquery or the operator you entered requires parenthesis.

Must enter either TRUE or FALSE.

Object <0s> does not exist in the database.

ODBC error: <0s>.

One or more selected tables are already on the diagram.

Only one clustered index can be created on table '<0s>'.

Only one ROWGUIDCOL column is allowed per table.

Oracle does not allow full outer joins.

Oracle does not support this join syntax.

Outer join column may not be used with an IN predicate or subquery.

Query Designer cannot open this query file.

Query has executed successfully.

Relationship '<0s>' was modified or deleted since the diagram was loaded.

Row limit value should be between <xxx> and <xxx>.

SQL statement could not be parsed.

SQL Verification.

Syntax error in table reference.

Syntax error in table reference: '<0s>' not recognized.

System errors.

Table '<0s>' is marked for deletion and was not added to the diagram.

Table '<0s>' no longer exists in the database.

The Allow Nulls property can't be set on a column that is part of the primary key.

The Allow Nulls property can't be set on column '<0s>' because it is an identity column.

The columns in table '<0s>' do not match an existing enabled primary key or UNIQUE constraint.

The current version of the ODBC driver is not valid.

The datatype of column '<1s>' in the '<0s>' table can't be changed because it participates in index '<2s>'.

The datatype or size property of '<0s>.<1s>' doesn't match '<2s>.<3s>'.

The Default Value property can't be set on column '<0s>' because it is an identity column.

The existing relationship must have at least one pair of related columns.

The Group By function is not supported in WHERE clauses.

The Identity property can't be set on column '<0s>' because it allows null values.

The Identity property can't be set on column '<0s>' because it has a default value.

The NOT keyword may not be used in a column cell.

The number of selected columns exceeds the number of columns in the target table.

The ORDER BY function is not supported for CREATE TABLE queries.

The outer join operator (+) cannot be used as an OR operand.

The outer join operator (+) cannot be used in QBE.

The primary key or UNIQUE constraint cannot be changed until its existing relationships are deleted.

The primary key or Unique constraint cannot be changed while relationships to the existing primary key or Unique constraint are enforced.

The query cannot be executed because some files are missing or not registered.

The Query Designer does not support the critical ODBC APIs.

The Query Designer does not support the current ODBC API.

The Query Designer supports no more than one data source for this type of query.

The specified OR group number is already in use.

The total size of an index or primary key cannot exceed 256 bytes.

The value you entered is not consistent with the data type of the column.

There are not enough columns to match the subquery select list.

There are not enough columns to match the value list.

There is no unique table in this query.

This ALIAS name is already being used.

This cell contains the text string "<NULL>" which may not be processed correctly.

This IS operator can only be used with NULL or NOT NULL.

This operator cannot be used with columns with data type "uniqueidentifier".

This table is not in the query definition.

Too many characters for field width.

Unable to add constraint.

Unable to create index.

Unable to create relationship.

Unable to locate data source.

Unable to modify table.

Unable to parse expression.

Unable to parse query text.

Unable to parse statement.

Unable to preserve trigger.

Unknown column.

Unknown conversion specification.

Unneccesary use of CONVERT function.

Unsupported SQL.

View already exists in the database.

You are not logged in as the database owner or system administrator. You will not be able to save changes to tables that you do not own.

You may not use a link server object as a destination with this query type.

You may not use ALL with CUBE or ROLLUP.

You may not use an openrowset as a destination with this query type.

You must enter an expression in the Column cell before continuing.

Your entry cannot be converted to a valid datetime value.

Chapter 16 ODBC Error Messages

Appendix A Accessibility for People with Disabilities

Customizing Windows or Windows NT

Accessibility Notes and Utilities to DownloadTo Download the Files

Microsoft Services for People Who Are Deaf or Hard-of-Hearing

Customer ServiceTechnical Assistance

Microsoft Documentation in Alternative Formats

Utilities to Enhance Accessibility

Getting More Accessibility Information

MicrosoftMacintosh Disability SolutionsTrace R&D CenterAssistive Technology Programs and Trained Evaluators

Index

Information in this document is subject to change without notice. The names of companies, products, people, characters, and/or data mentioned herein are fictitious and are in no way intended to represent any real individual, company, product, or event, unless otherwise noted. Complying with all applicable copyright laws is the responsibility of the user. No part of this document may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

© 1988-1998 Microsoft Corporation. All rights reserved.

Microsoft, ActiveX, Back Office, Microsoft Press, MSDN, MS-DOS, Outlook, Visual Basic, Win32, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the U.S.A. and/or other countries.

Intel is a registered trademark of Intel Corporation.

Macintosh is a registered trademark of Apple Computer, Inc.

Other product and company names mentioned herein may be the trademarks of their respective owners.

Document No. X03-75528

Printed in the United States of America