Server FAQ

Troubleshooting SQL Server

Troubleshooting

Server FAQ

What is the difference between DBCC INDEXDEFRAG and DBCC REINDEX?

Answer:

Unlike DBCC DBREINDEX or any general index build, DBCC INDEXDEFRAG is an online operation, so it does not hold long-term locks that can block running queries or updates. Depending on the amount of fragmentation, DBCC INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX because a relatively unfragmented index can be defragmented much faster than a new index can be built. Another advantage is that with DBCC INDEXDEFRAG, the index is always available, unlike DBREINDEX. A large amount of fragmentation can cause DBCC INDEXDEFRAG to run considerably longer than DBCC DBREINDEX, which may or may not outweigh the benefit of the command's online capabilities. DBCC INDEXDEFRAG will not help if two indexes are interleaved on the disk because INDEXDEFRAG shuffles the pages in place. To improve the clustering of pages, rebuild the index.

When I create a table, I get the following 2714 error message:

Total rowsize for table exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.

However, the table creation succeeds and data can be inserted without problems. What did this error message mean?

Answer:

This error message indicates that you have variable length columns in your table (such as nvarchar or varbinary) and that the total maximum length of all the columns adds up to more than 8,060 bytes. You can still insert rows into the table if the total length of the data in each row does not exceed 8,060 bytes. However, if the data does exceed 8,060 bytes, the insertion fails with the following error message:

Server: Msg 511, Level 16, State 1, Line 5
Cannot create a row of size <rowlength> which is greater than the allowable maximum of 8060.
The statement has been terminated.

A user-defined function returns a table that is schemabound to two tables in my database. According to the documentation, the referenced tables cannot be altered until the schemabound option is removed. However, I am able to add or delete columns from these referenced tables. Shouldn't this give me an error?

Answer:

You will not receive an error if you alter columns that are not referenced by the function. You cannot drop or alter the columns of a table that are involved in the schemabinding, but you can alter other columns that are not involved in the schemabinding. For example, suppose a schemabound table, table1, is defined with 5 columns: c1, c2, c3, c4, and c5. If the function only references c1 and c3, only columns c1 and c3 cannot be altered. Columns c2, c4, and c5 can be altered as needed.

How can I set the database to single user mode and restrict the access to dbo use only?

Answer:

In SQL Server 2000, a database cannot be in single-user mode with dbo use only. Instead, the following alternative options are available by using the ALTER DATABASE command:
  • ALTER DATABASE database SET SINGLE_USER.

    This command restricts access to the database to only one user at a time.

  • ALTER DATABASE database SET RESTRICTED_USER.

    This command restricts access to the database to only members of the db_owner, dbcreator, or sysadmin roles.

  • ALTER DATABASE database SET MULTI_USER.

    This command returns access to the database to its normal operating state.

Can I run multiple instances of SQL Server 2000 at the same time on one computer?

Answer:

Yes. For information, see Multiple Instances of SQL Server.

Are DB-Library applications supported in SQL Server 2000?

Answer:

Yes. However, DB-Library has not been enhanced for SQL Server 2000. DB-Library includes the same features that the Microsoft® SQL Server™ 7.0 DB-Library contains. This means that a DB-Library application can only connect to a default instance of SQL Server 2000; it cannot connect to a named instance. It will not recognize any of the new features available in SQL Server 2000.

Do I need to use the multi-protocol network library to enable encryption?

Answer:

No. SQL Server 2000 can use the Secure Sockets Layer (SSL) to encrypt all data transmitted between an application computer and a SQL Server instance on a database computer. The SSL encryption is performed within the Super Socket Net-Library (Dbnetlib.dll and Ssnetlib.dll) and applies to all inter-computer protocols supported by SQL Server 2000. For more information, see Net-Library Encryption.

Why does my SQL statement work correctly outside of a user-defined function, but incorrectly inside it?

Answer:

You may have included a statement in the BEGIN-END block that has side effects, which is not allowed in user-defined functions. Function side effects are any permanent changes to the state of a resource that has a scope outside the function. Changes can be made only to local objects such as local cursors or variables. Examples of actions that cannot be performed in a function include modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user.

How can I qualify a named instance in a linked server query?

Answer:

You must use square brackets around the multi-instance linked server name in the four part query. For example:

SELECT * FROM [myServer\sql80].northwind.dbo.customers