DBCC SHRINKFILE works with file_id but not with logical name

I am trying to shrink a database file but am running into errors.

Using the file_id from sys.database_files works but using the logical file name yields an error.

The logical file name is the same in both statements, so that can't be the problem. Also, the database that is being connected to, is the same. The following works as expected:

declare @fileId as int = (select file_id from sys.database_files where name = 'XY')
DBCC SHRINKFILE (@fileId, 0, TRUNCATEONLY)

However, the following...

DBCC SHRINKFILE ('XY' , 0, TRUNCATEONLY)

...will result in error 8985:

Msg 8985, Level 16, State 1, Line 1

Could not locate file 'XY' for database '' in sys.database_files. The file either does not exist, or was dropped.

The logical name is just normal characters, no special characters or spaces or numbers, no umlaut etc., only upper- and lower-case characters. It's similar to "DotNetDev". Server collation is Latin1_General_CI_AS.

I am still getting the same error when executing the following lines:

DECLARE @filename sysname; 
SELECT @filename = name 
FROM sys.database_files 
WHERE name = 'XY';

PRINT @filename;

DBCC SHRINKFILE (@filename , 0, TRUNCATEONLY);

The print statement works fine, so it seems that I am using the right logical name.

Revised my answer, testing did not show as a valid reason for it not to work.

Couple of things to check

• Are there leading or trailing spaces in the file name?

• Did you recently upgrade to SP3 by any chance?

Try switching the recovery model from full, to simple, back to full again. There is an apparent bug when updating to SP3. Changing the recovery model will reset the log sequence number and for whatever reason that clears the issue, at least temporarily.

social.msdn conversation about this

StackExchange Database Administrators Q#43932, answer score: 6