Microsoft KB Archive/920768

= 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 =

Article ID: 920768

Article Last Modified on 11/2/2007

-

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 &quot;Applies to&quot; section.

Keywords: kbexpertiseadvanced kbprb KB920768

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.