Msg 8985, Level 16, State 1, Line 1 Could not locate file 'xxx_log' for database ‘xxx' in sys.database_files. The file either does not exist, or was dropped.
This happened after doing and in-place upgrade to our existing SQL 2008R2 server. Happened only on one of our database which was on Simple recovery. It look strange to me when the SHRINKFILE command failed. When i compared the logical names on sys.master_files and sys.database_files, the both look different, I try to rename them back to the same file which of course didn't worked , so i thought of giving it a new logical name which fixed the issue.
select name from sys.master_files where database_id = db_id('DBA')
select name from sys.database_files
As you can see the logical names are different on these two queries for the same database. Not sure how this happened, could be related to the in-place upgrade operation.
Here is the alter statement I ran which indeed fixed the issue, you can run the above queries to verify this
ALTER DATABASE [DBA] MODIFY FILE (NAME=N'DBA_log', NEWNAME=N'DBA_log_1')