February 4, 2010 at 11:06 pm
We have a SQL 2000 server over here on which we have a large database of 200 GB having 2 datafiles , both in Primarry file group. Datafiles are IT1 (located oin E: drive) nd IT2(located on F: drive). IT1 size is 110 GB & IT2 is newly created data file with 89GB size.
Now we are trying to move all the data from IT1 to IT2 using
DBCC Shrinkfile('IT1',EMPTYFILE) command.
It's completed successfully just in milli secs. that is not the expected time to finish bcoz IT1 is so large . After completition, both files are still of same size means no data movement from IT1 to IT2. What can be reason that command completed prematurely?
For IT2, we have unrestricted growth by 64MB.. please get back to me ASAP
February 5, 2010 at 3:56 am
i guess , destination drive will have enough space
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 8, 2010 at 7:07 am
Use DBCC SHOWFILESTATS to check if your database file still has some used extents.
Generally there are two reasons if a file cannot be removed from a filegroup after being used DBCC SHRINKFILE with EMPTYFILE:
1) The file that you want to remove and shrink with empty is the primary file. It would fail as expected because primary file contains some unmovable contents and could not be emptied.
2) Because the contents of the target file has to move to other files in the same filegroup, if other files in the same filegroup does not have enough space to take the contents of the target file, the emptyfile would fail.
You may check the two conditions and if your situation did not fit any one of them, I recommend that you try rerunning DBCC SHRINKFILE with emptyfile operation and check if the column UsedPages in the result is 0. Then try removing the file again.
Vivek
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply