| Author |
Topic |
|
Norman Castle
Starting Member
9 Posts |
 Posted - 2011-04-29 : 21:18:16
|
| I'm having problems with Shrinkfile. I wish to use the option to migrate the data to other files, and then delete the file.It takes several hours to operate a single shrink operation. And then it usually gives an error message. It only works sometimes.Any ideas what could be wrong?I am, by the way, using SQL Server management Studio. How do I get the file ID number to run it as a SQL statement? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Norman Castle
Starting Member
9 Posts |
Posted - 2011-04-30 : 00:32:15
|
| How do I shrink in small increments?I can't see how to do it except for unused space. I mean, if the file is 950Mb, and only 650 is used, then I can reduce the size by 200 easily. But I can't get it any smaller than 650.If I check the option to move the data to other files, it tries to move all of it. I don't see how to move only some of it. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Norman Castle
Starting Member
9 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-30 : 11:03:02
|
| You have to use the EMPTYFILE option of the DBCC SHRINKFILE command. You also need at least one other file in the same filegroup large enough to accept all the data from the file you're trying to delete. |
|
|
|
Norman Castle
Starting Member
9 Posts |
Posted - 2011-04-30 : 12:40:44
|
quote:
You have to use the EMPTYFILE option of the DBCC SHRINKFILE command.
Can this be done by increments? What's the syntax for it?quote:
You also need at least one other file in the same filegroup large enough to accept all the data from the file you're trying to delete.
Enough space in a single file? Or is it okay if I have several files, with enough space when combined?And if I don't have enough space, but the files are set to autogrow, will that do? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-30 : 21:43:59
|
| EMPTYFILE is an all-or-nothing prospect, it moves everything out of the file you're shrinking.You don't need to have enough space in a single file, just enough in the other files of the filegroup. You can also create a new filegroup and re-create the clustered index on the new filegroup, that will move the data. You'd have to repeat that for any nonculstered indexes in that file. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2011-05-02 : 20:46:52
|
| by the way, you realize you are going to cause fragmentation by shrinking your data files right? Read Why you should not shrink your files @ http://www.sqlskills.com/BLOGS/PAUL/category/Shrink.aspx#p7Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
|
Shrink file