Could not locate file in sys.database_files

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  October 9, 2012 at 3:46 am

  #282812

  Hi All

  I had a weird situation this morning

  On one of my test systems, I tried running the following command to shrink my log file

  DBCC shrinkfile(filename,1024)

  I received this error

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

  I checked and the file does exist.

  I ran a full Consistency Check (DBCC CHECKDB) and that seems to have fixed the problem.

  Has anyone else come across this before? Should I be worried?

  Thanks

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  October 10, 2012 at 2:26 am

  #1547517

  My guess is, you would be using wrong file name

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• mahesh.dasoni

  SSCommitted

  Points: 1653

  More actions

  October 10, 2012 at 3:18 am

  #1547535

  Use the following command:

  SELECT file_id,name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB

  FROM sys.database_files;

  after this command u will find the file id which has free space then run DBCC shrinkfile(file id)

• HamishWatson

  SSC Enthusiast

  Points: 172

  More actions

  March 24, 2017 at 12:59 am

  #1934692

  I had this issue too, tried everything above but still got the error.

  Database was called clientdatabase and the log file clientdatabase_log.

  I managed to resolve it by renaming the logical name of the log file:
  USE [clientdatabase];
  ALTER DATABASE clientdatabase MODIFY FILE (NAME = clientdatabase_log, NEWNAME = clientdatabase_log_1);
  GO

  Re-running the original script for shrinking the transaction log (with new logical name) now worked.

  I blogged about it here:

  https://hybriddbablog.com/answer-to-could-not-locate-file-xxx_log-for-database-xxx-in-sys-database_files/