Tags: attempting, database, datafile, error, file, files, ireceive, locate, message, microsoft, mysql, oracle, primary, server, shrink, sql, sysfiles, xxx

Error 8985 cannot locate file xxx in sysfiles

On Database » Microsoft SQL Server

2,750 words with 1 Comments; publish: Tue, 03 Jun 2008 23:17:00 GMT; (25078.13, « »)

I am attempting to shrink a datafile (I have 2 primary data files) when I

receive this error message 'Error 8985 cannot locate file xxx in sysfiles'.

I have repeatedly verified the data file name and even did a 'cut n paste'

into a query in qa dbcc shrinkfile with the same results. I have no

problem shrinking the second file and the log file through either method.

I even ran dbcc shrinkfile (3, emptyfile) moving all data to the problem

file. After removing the second datafile I created a new second datafile

in the primary filegroup and am in the process of moving the data to the

new file. Hopefully I will be able to remove the problem file afterward.

Although I suspect this will still not work because I will have to use the

logical file name and not the number.

Any help will be greatly appreciated!

Thanks

Message posted via http://www.sqlmonster.com

All Comments

Leave a comment...

  • 1 Comments
    • You did a cut and paste from where? What column of what

      query? Generally the error is due to referencing the

      physical file name and not the logical file name. It's hard

      to tell as you example is using the file id which is fine as

      well. Also, make sure you are in the correct database when

      getting the logical file name or id and when executing the

      shrinkfile. If you are selecting from sysfiles or executing

      sp_helpdb to get the name, it applies to the current

      database only. If it still fails and you aren't using

      fileid, try using that instead. It could be an issue with

      the logical file name not following the rules for

      identifiers.

      -Sue

      On Wed, 02 Mar 2005 19:49:01 GMT, "andy williams via

      SQLMonster.com" <forum.sqlserver.questionfor.info.SQLMonster.com> wrote:

      >I am attempting to shrink a datafile (I have 2 primary data files) when I

      >receive this error message 'Error 8985 cannot locate file xxx in sysfiles'.

      >I have repeatedly verified the data file name and even did a 'cut n paste'

      >into a query in qa dbcc shrinkfile with the same results. I have no

      >problem shrinking the second file and the log file through either method.

      >I even ran dbcc shrinkfile (3, emptyfile) moving all data to the problem

      >file. After removing the second datafile I created a new second datafile

      >in the primary filegroup and am in the process of moving the data to the

      >new file. Hopefully I will be able to remove the problem file afterward.

      >Although I suspect this will still not work because I will have to use the

      >logical file name and not the number.

      >Any help will be greatly appreciated!

      >Thanks

      #1; Tue, 03 Jun 2008 23:19:00 GMT