Hey everyone, sliding in a post on the last day of the month of August. I already missed July :'-(
Anyways, today we will be looking at the following error(s) I got when attempting to shrink a database file via DBCC SHRINKFILE:
- Msg 8985, Level 16, State 1, Line #
Could not locate file 'FileName_1' for database 'DatabaseName' in sys.database_files. The file either does not exist, or was dropped.
Msg 5041, Level 16, State 1, Line #
MODIFY FILE failed. File 'FileName_1' does not exist.
These errors have to do with incorrect filenames you might be using for the DBCC SHRINKFILE parameters. And by incorrect, I mean it is possible that you, like me, were not using the logical filename.
One way to double check the logical filename would be to run the following on the database in question to get the info from sys.database_files:
SELECT
file_id AS FileID,
name AS LogicalFileName,
size/128.0 AS FileSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS EmptySpaceMB
FROM
sys.database_files;
The query gives you some additional information such as file_id (which you can use instead of the logical name) as well as file size and 'empty' space to better determine your file reduction planning.
Hope this helps. :-)
DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
