Search

DBCC SHRINKFILE doesnt work for a filename. by Wim SQL Server

Closed
as Not Reproducible Help for as Not Reproducible

2
0
Sign in
 to vote
Type: Bug
ID: 765698
Opened: 10/2/2012 5:21:41 AM
Access Restriction: Public
0
Workaround(s)
3
User(s) can reproduce this bug
I have a database and I try t shrink the file MatrixNEW_Data with
Task> Shrink >Files: there I select the Data file with filename MatrixNew_data but I get the error:Msg 8985, Level 16, State 1, Line 1
Could not locate file 'MATRIXNEW_Data' for database 'CEVI' in sys.database_files. The file either does not exist, or was dropped.

In sys.database_files I see:

1    NULL    0    ROWS    1    MATRIXNEW_Data    E:\SQL2008\DATA\CEVI.mdf    0    ONLINE    1920000    -1    128000    0    0    0    0    0    NULL    NULL    NULL    NULL    1853073000000087700040    5E747E34-C49D-47A3-9ED6-B70E8A373BBA    2012-09-03 22:48:15.527    NULL    NULL    NULL    NULL    1594460000000019900004
2    NULL    1    LOG    0    MATRIXNEW_Log    E:\SQL2008\DATA\MSSQL10.SQL2008\MSSQL\Data\CEVI_3.ldf    0    ONLINE    550400    -1    12800    0    0    0    0    0    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
3    NULL    0    ROWS    2    MATRIXNEW_Index    F:\SQL2008\DATA\CEVI_2.NDF    0    ONLINE    1280000    -1    128000    0    0    0    0    0    1485040000000214700001    NULL    NULL    NULL    1853073000000087700040    5E747E34-C49D-47A3-9ED6-B70E8A373BBA    2012-09-03 22:48:15.527    NULL    NULL    NULL    NULL    1594459000000042500006

If I change the name in DBCC Shrinkfile to fileid1 the shrink works.
But the name is present in sys.databases_files so the srhink based on the filename should also work.
Details (expand)

Product Language

English

Version

SQL Server 2008 SP3

Category

SQL Engine

Operating System

Windows Server 2008 (all editions)

Operating System Language

US English

Steps to Reproduce

USE [CEVI]
GO
DBCC SHRINKFILE (N'MATRIXNEW_Data' , 0, TRUNCATEONLY) --doesnt work
GO

DBCC SHRINKFILE (1, 0, TRUNCATEONLY) --works
GO

Actual Results

Could not locate file 'MATRIXNEW_Data' for database 'CEVI' in sys.database_files. The file either does not exist, or was dropped.

Expected Results

the srhink should work because the filename exists.

Platform

IA64

Virtualization

 
File Attachments
File Name Submitted By Submitted On File Size  
Capture.JPG Wim SQL Server 10/2/2012 74 KB
Capture2.JPG Wim SQL Server 10/2/2012 30 KB
Sign in to post a comment.
Posted by Microsoft on 10/15/2012 at 10:29 AM
Since this is now working for you, I'll resolve your bug.
Thanks,
Kevin
Posted by Microsoft on 10/8/2012 at 10:23 AM
It's great to know that it now worked for you. Seems the problem has something to do with the state the DB was in, but I don't think I could know how the DB got into this state at this point.
Is there anything else we could help you with on this issue?
Posted by Wim SQL Server on 10/8/2012 at 5:50 AM
I just got something really strange:
DBCC SHRINKFILE (N'MATRIXNEW_Data' , 0, TRUNCATEONLY): doesn't work

DBCC SHRINKFILE (N'MATRIXNEW_Index' , 0, TRUNCATEONLY) : works
DBCC SHRINKFILE (N'MATRIXNEW_Log' , 0, TRUNCATEONLY) : works
DBCC SHRINKFILE (N'MATRIXNEW_Data' , 0, TRUNCATEONLY): now it works!!
Posted by Wim SQL Server on 10/8/2012 at 5:48 AM
It happens on a SQL2008 SP3. The database is restored by a backup from another system by using redgate backup.
The script you provided works.
the database I have the problem with was restored with the redgate toolbelt.
I restored it again, and the db works fine but still the dbcc shrinkfile doesn't work but in sys.database_files I do see the name and that shrinking through management studio doesn't work for this db.
it' s only on this particular db.

What are the internal code form dbcc shrinkfile because the error is not really correct because the name is present in sys.database_files.
Posted by Alexey Aksyonenko on 10/5/2012 at 3:03 PM
I second this. I get the exact same error. This is happening on all my systems. All the systems in question are replication publishers, all databases I am trying to shrink participate in replication (standard Transactional replication, multiple publisher systems, 1 dedicated distributor system, one dedicated subscriber sytem). System properties for the publishers are below. I can shrink the distribution database and the subscriber databases just fine without getting that error.

Product: Microsoft SQL Server Standard Edition
Operating System: Microsoft Windows NT 6.0 (6002)
Platform: NT INTEL X86
Version: 10.0.5768.0
Language: English (United States)
Memory: 10239 (MB) ---- this varies from system to system
Processors: 4 ---- this varies from system to system
Root Directory: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL
Server Collation: SQL_Latin1_General_CP1_CI_AS
Is Clustered: False
Posted by Microsoft on 10/4/2012 at 5:47 PM
Thank you for logging the feedback.

I got a SQLServer 2008 SP3 build and tried the following but couldn't repro the problem you described.

create database testdb
use testdb
select * from sys.database_files
dbcc shrinkfile (N'testdb', 0, TRUNCATEONLY)

So I'd like to collect a bit more information about this issue:
1. Does this only happen for that particular DB or it happens for all your DBs?
2. If possible, can you please try the above scripts and see if that repro's the same problem for you?
3. On this particular DB, does the same problem constantly repro for you or it only happened once?
4. If this constantly repro for you and even for your other/new DB, please share the script that can be used to create such a DB.

Thanks
Sign in to post a workaround.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement