Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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

3 Answers 3

up vote 2 down vote accepted

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

share|improve this answer
    
This was already suggested by @thomasStringer. The OP is getting error. Please see the comments. –  Kin Jun 6 '13 at 13:53
    
Agreed. Let OP comeback and see as there might be collation problem as well. Cheers ! –  Kin 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

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?

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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