DBCC shrinkfile gives an error

Question

I am trying to compress my log file using DBCC SHRINKFILE(db_2.ldf) , which is the log file name

This gives me an error every time:

8985, level 16, state 1, line 1 Could not find file "FIELD" for db database in sys.database_files. The file either does not exist or has been deleted.

Can you suggest what I can do to fix this.

Answer 1

The file name should be the logical file name, not the physical file name. Look in the database properties on the Files tab for the logical name of the file you are trying to compress and use that name.

Answer 2

Do you run it in the context of the database with the log you are trying to compress? Before running DBCC commands, make sure you have the correct USE statement

Answer 3

If you had the same problem, the solution was to rename the logical file according to the database name. The following is an example of querying logical file names, and then rename the files:

 -- retrieve the logical files for the current db SELECT [name] AS logical_file FROM sys.database_files df -- rename the logical file (to match the database name) ALTER DATABASE YourDB MODIFY FILE (NAME = 'LogicalFile1', NEWNAME='NewLogicalFile1') GO ALTER DATABASE YourDB MODIFY FILE (NAME = 'LogicalFile2', NEWNAME='NewLogicalFile2') GO 

The reason for the two changes is that there are usually two files associated with each database, data file, and log file.

Answer 4

The command below works. However, I do not see this reduce the size. I see the same size after and before it starts. Could you tell me what I might have missed.

 fileid groupid size maxsize growth status perf name filename 2 0 1048 268435456 10 1048642 0 PrimaryLogFileName 

thanks

Answer 5

From SQL Management Studio Right-click the database name, Tasks, Compression, Database Ctrl + Shift + N (or Script Action in a new query window)

Creates the following:

USE [DATABASE_NAME] GO GO DBCC SHRINKDATABASE (N'DataBaseName) GO