CODEWITHSUNDEEP
sql-servertransaction-logdbcc
user70636
user70636

Reputation: 1089

DBCC shrinkfile gives error

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

It gives me error every time:

8985, Level 16, State 1, Line 1 Could not locate file 'FIelD' for database db in sys.database_files. The file either does not exist, or was dropped.

Can you please suggest what can I do to fix it.

Upvotes: 5

Views: 12842

Answers (5)

BillM
BillM

Reputation: 21

From SQL Management Studio Right click the database name, Tasks, Shrink, Database Ctrl+Shift+N (or Script Action to New Query Window)

Generates the following:

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

Upvotes: 0

Ken Richards
Ken Richards

Reputation: 3013

Had the same problem over here, the solution was to rename the logical file to match the database name. Below is an example to query the logical file names and then perform a rename of 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 alters is that there are usually two files associated with each database, the data file and the log file.

Upvotes: 1

user70636
user70636

Reputation: 1089

The command below worked. However I don't see it reducing the size. I see the same size after and before running it. Can you please let me know what I might have missed.

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

Thanks

Upvotes: 0

onupdatecascade
onupdatecascade

Reputation: 3366

The file name should be the logical file name and 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 shrink, and use that name.

Upvotes: 13

curtisk
curtisk

Reputation: 20175

Are you running it in the context of the database that has the log you are trying to shrink? Make sure you have the right USE statement before running DBCC commands

Upvotes: 1

Related Questions