SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Could not locate file in sys.database_files


Could not locate file in sys.database_files

Author
Message
SQLSACT
SQLSACT
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5118 Visits: 2969
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
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13606 Visits: 4077
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
mahesh.dasoni
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 1025
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)
hamish.watson8
hamish.watson8
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 27
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/


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search