The DBCC SHRINKFILE statement has no effect when you run it after you run the DBCC SHRINKFILE statement together with the EMPTYFILE option in SQL Server 2000 (920768)

The information in this article applies to:

Microsoft SQL Server 2000 Standard Edition
Microsoft SQL Server 2000 Developer Edition
Microsoft SQL Server 2000 Enterprise Edition
Microsoft SQL Server 2000 Personal Edition
Microsoft SQL Server 2000 Workgroup Edition

SYMPTOMS

In Microsoft SQL Server 2000, if you run the DBCC SHRINKFILE statement after you run the DBCC SHRINKFILE statement together with the EMPTYFILE option, the second DBCC SHRINKFILE statement does not have any effect. For example, you run the following statements:

     DBCC SHRINKFILE (myfile, EMPTYFILE)
     DBCC SHRINKFILE (myfile, 100)

The first statement works as expected. However, the second statement has no effect. You only receive the following message: DBCC execution completed. If DBCC printed error messages, contact your system administrator.

CAUSE

This problem occurs because the purpose of running the DBCC SHRINKFILE statement together with the EMPTYFILE option is to let an administrator remove a file. A file must be completely empty before it can be removed. Therefore, when you run such a statement, SQL Server prevents allocations and most other changes to the file. This effect lasts until the SQL Server service is stopped and restarted. After the SQL Server service has been restarted, allocations can be made within the file again, and DBCC SHRINKFILE statements will behave in an expected manner.

WORKAROUND

To work around this problem, stop and then restart the SQL Server service.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Modification Type:	Minor	Last Reviewed:	7/7/2006
Keywords:	kbExpertiseAdvanced kbprb KB920768 kbAudDeveloper kbAudITPRO