SQL Server gives error 8985 - "Could not locate file XXXXXXX in sysfiles"
Recently we came across this scenario:
- A transaction log was growing fast in size.
- To free up space we decided to:
o Backup the transaction log
o Shrink the log file to reclaim lost space from the hard disk
The backup log command worked as expected, however the shrinking of the transaction log gave this error:
SQL error 8985 - Could not locate file XXXXXXX in sysfiles
We ran these three queries to find out what kind of inconsistency there was:
- DBCC FILEHEADER (dbname)
This query returned an incorrect filename.
- Select * from dbname.dbo.sysfiles
This query reported the correct filename.
- Select * from master..sysaltfiles
This query reported the correct filename.
We guessed that someone in the past had made some kind of disaster recovery scenario and the logical filename was now incorrect. It seems that this error will occur if the physical names of the files have been changed, while the logical names remain unchanged.
To fix this inconsistency, we used the Alter database command:
ALTER DATABASE dbname
MODIFY FILE (NAME = old_file_name, NEWNAME = new_file_name)
After this command, the shrinking worked as expected.
Disclaimer
This is a personal weblog. The opinions expressed here represent my own and not those of my employer. The steps are provided “as is” without a warranty of any kind.
