SQL 2000 Shrink data file doesn't work

Question

Post reply

SQL 2000 Shrink data file doesn't work

xxxxxxxxxxx

Ten Centuries

Points: 1234
More actions
December 1, 2010 at 9:48 am

#388347

I would like to thank all in advance for any assistance provided.
I have a sql 2000 database that I had to span the data files over two new data files I created for a total of THREE data files.
I created the datafiles and set them to NOT autogrow.
I emptied the primary into these two new files using
DBCC SHRINKFILE (N'logicaldatafilename' , EMPTYFILE)
The primary would go from a single 75gb file to three 25gb files.
----PROBLEM----
I cannot seem to recover the remaining 50gb from the original data file and I've tried all of the following with no luck.
note: MyDatafilename replaced the real filename..
This didn't work
DBCC SHRINKFILE (N'MyDatafilename' ,TRUNCATEONLY)
This didn't work
DBCC SHRINKFILE (N'MyDatafilename' , 25000)
This didn't work from within the correct database.
DBCC SHRINKFILE (1 ,25000)
This didn't work from within the correct database.
DBCC UPDATEUSAGE(0)
This returns normal
DBCC CHECKDB
Thanks,
Mark
"Happy Holidays!"

Viewing 7 posts - 1 through 6 (of 6 total)

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

bjhogan SSC Eights! Points: 870 More actions · Answer 1

What does this return?

SELECT name ,

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB

FROM sysfiles;

xxxxxxxxxxx Ten Centuries Points: 1234 More actions · Answer 2

It is showing the following, which most of the first one is empty space.

Data 105000

Log 8

Data2 20000

Data3 20000

bjhogan SSC Eights! Points: 870 More actions · Answer 3

I'm pretty sure I ran into a similar problem some months ago and wish, for the life of me, remember what I did to resolve. I want to say that for some reason I had to increase the size of the file slightly(a couple of MB) and then the shrink went through successfully.

Also, don't know if you know this, but you won't be able to drop that file. I'm assuming you know this or you probably would have already tried it.

Another thing to look at, I've never had to use it is the NOTRUNCATE. It moves allocated pages at the end of the file to unallocated at the beginning. TRUNCATEONLY frees all space at the end of the file. So, it could be that you have system information at the end of the file stopping you from shrinking it completely......Just a thought.

I'll probably end up looking through all my documentation today trying to find the resolution....thanks for that......=)

Johan Bijnens SSC Guru Points: 135258 More actions · Answer 4

- you simply cannot empty the first "primary" file as it contains all catalog info !

Your alternatives could have been to add another filegroup, and put two files in that one, then move all user objects to that new filegroup.

SSC has some useful scripts for that.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

xxxxxxxxxxx Ten Centuries Points: 1234 More actions · Answer 5

Thank you both for your help.

I don't really need to remove the original primary data file, but rather reduce the unused space after moving the data into the two other data files.

I've been on this goose-chase of moving image and text tables out of the database and then trying to shrink, but that didn't work.

xxxxxxxxxxx Ten Centuries Points: 1234 More actions · Answer 6

Thank you all again for your assistance. I wanted to post a reply so other DBA's know what fixed this problem for me.

Looks like BJ was right on the money!

Here is a clear recap for future readers.....

Problem:

SQL Server 2000 data file will not shrink. After I empty out the primary data file I am not able to recover the unused drive space from the data file.

Resolution:

I added less than 100M to the data file that I was trying to shrink and was then able to compress and truncate the unused space from the data file.

THANKS!!!

Mark