Questions, Answers, and Tips - 21 Mar 2001

Editor's Note: Send your SQL Server questions and comments to SQL Server MVP Brian Moran at [email protected].

When I use SQL Server Profiler to trace the execution of a procedure, including each statement the procedure executes, Profiler generates TraceAutoPause events. These events seem to stop Profiler from tracing until a TraceAutoRestart event occurs. Why is Profiler generating the TraceAutoPause events?

Occasionally, Profiler can't consume the information it's tracing fast enough. Rather than slow down SQL Server or run the risk of capturing only intermittent commands (which would be worse than an explicit warning that Profiler has paused the trace), Profiler stops producing events until it can read all existing events from the Profiler queue. Event production starts again when Profiler catches up.

Here's a way around the pause. You might have noticed the Server Processes SQL Server trace data check box on the General tab of Profiler's Trace Properties dialog box. Selecting this check box causes SQL Server to capture all events and write them to a trace file. SQL Server captures all events when it runs a trace in this mode, but you run the risk of slowing SQL Server performance under heavy trace-event loads.

I recently had a copy of SQL Server 7.0 and a copy of SQL Server 2000 loaded on my computer. My computer's name is PEOPLE, and the SQL Server 7.0 instance name is also PEOPLE. I named the SQL Server 2000 instance PEOPLE2000, but the name appeared as PEOPLE/PEOPLE2000 because of how SQL Server 2000 handles different instances. Now I've upgraded all my databases to SQL Server 2000 and removed the copy of SQL Server 7.0 from my computer. Can I rename the instance of SQL Server 2000 to just PEOPLE?

Currently, you can't turn a named instance of SQL Server 2000 into the default instance on a machine. Your best bet would be to install a new default instance of SQL Server 2000, which you would name PEOPLE, then use SQL Server 2000's new Copy Database Wizard to move the relevant information from the named instance to the default instance. The Copy Database Wizard provides a convenient online way to move or copy databases and their objects from one server to another, with no server downtime. Using this wizard, you can

You could simply use sp_detach_db and sp_attach_db to move the database, but the Copy Database Wizard helps automate several important steps, including moving logins and SQLServerAgent jobs from one server to another. The Copy Database Wizard is simply a friendly GUI wrapper around five new SQL Server 2000 custom Data Transformation Services (DTS) tasks: the Database Move/Copy task, the Logins Copy task, the Jobs Copy task, the Master Stored Procedures Copy task, and the Error Messages Copy task. You can also call these tasks directly from a DTS package.

Can you detach a database from SQL Server 7.0 and attach it to SQL Server 2000?

Yes, but be careful. SQL Server 2000 makes slight changes to a SQL Server 7.0 database file before attaching it to SQL Server 2000. These changes don't cause data loss, but they will keep you from subsequently re-attaching the database to SQL Server 7.0. Just to be safe, make a file-level backup of the detached database files before you attach them to SQL Server 2000, in case you need to re-attach them to SQL Server 7.0.

When I cancel a query, then run system stored procedure sp_who2, I see the system process ID (SPID) with a command state of rollback. Can I find out how many rows SQL Server is going to roll back so that I can estimate how long the rollback operation will take?

Determining how long the rollback will last depends on which release of SQL Server you're running. Until SQL Server 2000, you didn't have a reliable way to determine how long a connection in the rollback state would continue to run. SQL Server 2000 gives you some long-needed functionality in this area by enhancing the KILL command. SQL Server Books Online (BOL) describes the KILL command as follows: "Terminates a user process based on the system process ID (SPID) or unit of work (UOW). If the specified SPID or UOW has a lot of work to undo, the KILL command may take some time to complete, particularly when it involves rolling back a long transaction."

When you cancel a query through Query Analyzer or simply close an application, your action kills the connection, putting the connection into a rollback state if a transaction was active. SQL Server 2000 adds a new WITH STATUSONLY option to the KILL command that lets you monitor the level of progress for a command that is in the rollback state. Suppose you determined that a connection with a SPID of 54 was deep into the processing of a large transaction. Issuing the command

KILL 54

would put the connection into a rollback state. Issuing the command

KILL 54 WITH STATUSONLY

would put the connection into a rollback and would issue a message about how far the rollback activity has progressed. The result would look like this:

spid 54: Transaction rollback in progress. Estimated rollback completion:80% Estimated time left: 10 seconds.

As you can see, this response gives an estimated percentage of work completed as well as an estimate of how much longer the rollback will take to complete. I encourage you to use the time estimate as an approximate guide rather than assume it is exact. Remember, you can use the WITH STATUSONLY option only when the SPID is in the middle of a rollback. Executing KILL WITH STATUSONLY when the SPID isn't in a rollback state generates the following error message:

Status report cannot be obtained. KILL/ROLLBACK operator forProcess ID|UOW  is not in progress.

I've been having a problem using the ALTER TABLE command to drop a column in a table. For example, running the following script:

SELECT * INTO #test FROM pubs..authorsALTER TABLE #test DROP COLUMN au_id

generates the error message

Server: Msg 170, Level 15, State 1, Line 2Line 2: Incorrect syntax near 'au_id'.

However, I know the script's syntax is correct because I can run the same script in another database. What's the problem?

The symptoms point to a misbehaving database that is set to an old database compatibility level. SQL Server provides varying levels of backward compatibility by letting you run individual databases under the rules of an older release. In SQL Server 7.0, Microsoft gave users the ability to drop a column by using the ALTER TABLE statement, so this ability is fairly new. If your database is running under an old compatibility level, you'll receive the error message you describe.

To see which compatibility level is set for your database, run the following command:

EXEC sp_dbcmptlevel 'BadDB'

You'll probably see a value of 60 or 65, which denotes SQL Server 6.0 or SQL Server 6.5, respectively. To set the database to a compatibility level that lets you use the ALTER TABLE statement to drop a column, run

EXEC sp_dbcmptlevel 'BadDB', 70

for SQL Server 7.0 compatibility mode or

EXEC sp_dbcmptlevel 'BadDB', 80

to set the compatibility mode for SQL Server 2000. You can then rerun the script. Remember that compatibility levels are set on a per-database basis.