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 '<my database>' in sys.database_files. The file either does not exist, or was dropped.
DBCC SHRINKFILE (XY, 0, TRUNCATEONLY)
– Thomas Stringer Jun 6 '13 at 12:34DECLARE @filename SYSNAME; SELECT @filename = name FROM sys.database_files ...);
and then use@filename
in the argument toDBCC
? – Aaron Bertrand Jun 6 '13 at 15:53