10

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.

The logical name is just normal characters, no special characters or spaces or numbers, no umlaut etc., only upper- and lower-case characters. It's similar to "DotNetDev". Server collation is Latin1_General_CI_AS.

I am still getting the same error when executing the following lines:

DECLARE @filename sysname; 
SELECT @filename = name 
FROM sys.database_files 
WHERE name = 'XY';

PRINT @filename;


DBCC SHRINKFILE (@filename , 0, TRUNCATEONLY);

The print statement works fine, so it seems that I am using the right logical name.

Paul White
  • 94,921
  • 30
  • 437
  • 687
DotNetDeveloper
  • 391
  • 1
  • 4
  • 13

5 Answers5

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

Cougar9000
  • 1,538
  • 1
  • 13
  • 29
4

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?

DotNetDeveloper
  • 391
  • 1
  • 4
  • 13
2

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.

Mukus
  • 153
  • 1
  • 6
2

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.

Joanna Piasecka
  • 21
  • 1
1

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”

The Hamish
  • 11
  • 2