Home Dashboard Directory Help
Search

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


Status: 

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)
view
3
User(s) can reproduce this bug

Description

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
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.
File Name Submitted By Submitted On File Size  
Capture2.JPG 10/2/2012 30 KB
Capture.JPG 10/2/2012 74 KB