9

I am trying to shrink a database file but am running into errors.

Using the file_id from sys.database_files works, but using the logical file name yields an error.

The logical file name is the same in both statements, so that can't be the problem. Also, the database that is being connected to, is the same. The following works as expected:

declare @fileId as int = (select file_id from sys.database_files where name = 'XY')
DBCC SHRINKFILE (@fileId, 0, TRUNCATEONLY)

However the following...

DBCC SHRINKFILE ('XY' , 0, TRUNCATEONLY)

...will result in error 8985:

Msg 8985, Level 16, State 1, Line 1
Could not locate file 'XY' for database '<my database>' in sys.database_files. The file either does not exist, or was dropped.

Improve this question
10
  • 1
    Have you tried it without the single quotes? DBCC SHRINKFILE (XY, 0, TRUNCATEONLY) – Thomas Stringer Jun 6 '13 at 12:34
  • @ThomasStringer Yes, it gives me the same error. – DotNetDeveloper Jun 6 '13 at 12:35
  • 1
    What is the actual logical filename? Does it have any odd characters, or spaces? Can you post that? – Thomas Stringer Jun 6 '13 at 12:36
  • @ThomasStringer It's just normal characters, no special characters or spaces or numbers, and no umlaute etc.. Only upper and lower case characters. It's similar to "DotNetDev". – DotNetDeveloper Jun 6 '13 at 12:58
  • 1
    What happens when you say DECLARE @filename SYSNAME; SELECT @filename = name FROM sys.database_files ...); and then use @filename in the argument to DBCC? – Aaron Bertrand Jun 6 '13 at 15:53
6

Revised my answer, testing did not show as a valid reason for it not to work.

Couple of things to check

  1. Are there leading or trailing spaces in the file name?
  2. Did you recently upgrade to SP3 by any chance?

Try switching the recovery model from full, to simple, back to full again. There is an apparent bug when updating to SP3. Changing the recovery model will reset the log sequence number and for whatever reason that clears the issue, at least temporarily.

social.msdn conversation about this

Improve this answer
5
  • This was already suggested by @thomasStringer. The OP is getting error. Please see the comments. – Kin Shah Jun 6 '13 at 13:53
  • Agreed. Let OP comeback and see as there might be collation problem as well. Cheers ! – Kin Shah Jun 6 '13 at 14:10
  • @Cougar9000 No leading or trailing spaces. And selecting the logical name from sys.database_files works fine as per my OP. And yes, we did upgrade to SP3 a couple of weeks ago. But where is the connection between this problem and SP3? – DotNetDeveloper Jun 7 '13 at 7:32
  • @Cougar9000 Upvoted, because you are pointing me in the right direction. At the moment I am not really sure why setting the recovery model to full and back to simple again has solved my problem though, if somebody can shed some light on this, that would be great. Or is that simply a bug? – DotNetDeveloper Jun 7 '13 at 7:43
  • @DotNetDeveloper from what I gather it is a bug that they have not identified yet. Changing the recovery model will reset the log sequence number. For whatever reason that clears the issue. Not sure if it will be temp or perm fix – Cougar9000 Jun 7 '13 at 13:52
3

After looking at the answer and the link provided by @Cougar9000: Yes we recently upgraded to SP3! I then switched the recovery model from simple to full and back to simple. And then the statement

DBCC SHRINKFILE ('XY' , 0, TRUNCATEONLY)

works.

Does anybody know what's going on here? Is that a bug?

Improve this answer
1

Try running

    

select * from sys.database_files

by using your database and then master db. You will see the log and database files of your db only when you're using your db.

So - 

      

declare @fileId as int = (select file_id from sys.database_files where name = 'XY')

      DBCC SHRINKFILE (@fileId, 0, TRUNCATEONLY)

and

    

DBCC SHRINKFILE ('XY' , 0, TRUNCATEONLY)

both work as long as you're using the same database.

Improve this answer
0

You need to check if the logical file name is the same in both places sys.master_files and sys.database_files. In this case it will be different. Then just alter database giving the file new logical name which you like to have. Both tables will be updated and then you can shrink in GUI or using logical file name. I had the same error in SQL Server 2012, this fixed the issue.

Improve this answer
0

I tried all of the above and still had the issue. 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);

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

I blogged about it here:

Answer to “Could not locate file ‘xxx_Log’ for database ‘xxx’ in sys.database_files”

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.