Very large database file > 2TB shrinking

Question

Post reply

Very large database file > 2TB shrinking

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

fixfox8 SSC-Addicted Points: 488 More actions · Answer 1

GilaMonster (8/15/2008)
Ok. Which one are you trying to shrink and what size are you trying to shrink it to?

Sop -1 2367438

Sop_log 12800000 1

Sop_1 192000000 1000000

Sop_2 192000000 1000000

Sop_3 192000000 1000000

Sop_4 192000000 512000

First one to 1 terabyte. If it is not possible to something less then two at least. Could you please explain what is the problem.Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Odd. I suspected it was a problem with the file's max_size setting, but apparently not.

Grasping at straws here... what's the command that you're using to shrink the file?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4105 More actions · Answer 3

Hi,

How much free space you have in the file?

I suspect that you are trying to shrink the file to a size where your data is holding larger space.

If that is your case, you need to move some tables into different fielgroups and then shrink the database

You can also think of partitioning some large tables (and place the partitions in different file groups)

SQL Server 2005, came up with this nice feature and it is really a very good method to do. You can move the data chunk by chunk during offpeack hours.

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 4

I assume you have free space on the drive, correct? Also, is there free space listed in the database?

fixfox8 SSC-Addicted Points: 488 More actions · Answer 5

Thanks all. I have solved the problem. Just I am curious, if I have .mdf and some .ndf files in the Primary filegroupe, Is it possible some how to move a part of the data from .mdf to any .ndf/ and then I can shrink the .mdf or re size it. Best Regards.

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 6

any update on the issue or solution? Might help the next person.

SequelDBA Ten Centuries Points: 1093 More actions · Answer 7

I have a similar problem. I have a database that was restored from Prod to Test. The DB has multiple secondary files. We have purged prod data from this copy and now I'm trying to complete a shrink, as well as use only one primary data file. There's still over 200 GB that I can not reclaim. When I run a shrink (DBCC SHRINKFILE (Testdatafile, EMPTYFILE) I receive this error:

Msg 2555, Level 16, State 2, Line 1

Cannot move all contents of file "Testdatafile" to other places to complete the emptyfile operation.

Here are the results of sp_spaceused:

Database Database Size Unallocated

TestDB1 235923.63 MB226780.71 MB

Reserved Data Index SizeUnused

1375528 KB1062424 KB269432 KB43672 KB

Thank you...

KU