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 for
Process ID|UOW <xxx> 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..authors
ALTER TABLE #test DROP COLUMN au_id
generates the error message
Server: Msg 170, Level 15, State 1, Line 2
Line 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.