Home > Article > Database > SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

WBOYOriginal: 2016-06-07 15:35:071600browse

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组之前写过一篇文章： SQLSERVER将一个文件组的数据移动到另一个文件组每个物理文件（数据文件）对应一个文件组的情况（一对一）如果我把数据移到另一个文件组了，不想要这个已经清空的文件组了

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

之前写过一篇文章：SQLSERVER将一个文件组的数据移动到另一个文件组

每个物理文件（数据文件）对应一个文件组的情况（一对一）

如果我把数据移到另一个文件组了，不想要这个已经清空的文件组了，怎麽做？

脚本跟之前那篇文章差不多

<span> 1</span> <span>USE</span><span> master
</span><span> 2</span> <span>GO</span>
<span> 3</span> 
<span> 4</span> 
<span> 5</span> <span>IF</span> <span>EXISTS</span>(<span>SELECT</span> <span>*</span> <span>FROM</span> sys.<span>[</span><span>databases</span><span>]</span> <span>WHERE</span> <span>[</span><span>database_id</span><span>]</span><span>=</span><span>DB_ID</span>(<span>'</span><span>Test</span><span>'</span><span>))
</span><span> 6</span> <span>DROP</span> <span>DATABASE</span> <span>[</span><span>Test</span><span>]</span>
<span> 7</span> 
<span> 8</span> <span>--</span><span>1.创建数据库</span>
<span> 9</span> <span>CREATE</span> <span>DATABASE</span> <span>[</span><span>Test</span><span>]</span>
<span>10</span> <span>GO</span>
<span>11</span> 
<span>12</span> <span>USE</span> <span>[</span><span>Test</span><span>]</span>
<span>13</span> <span>GO</span>
<span>14</span> 
<span>15</span> 
<span>16</span> <span>--</span><span>2.创建文件组</span>
<span>17</span> <span>ALTER</span> <span>DATABASE</span> <span>[</span><span>Test</span><span>]</span>
<span>18</span> <span>ADD</span> FILEGROUP <span>[</span><span>FG_Test_Id_01</span><span>]</span>
<span>19</span> 
<span>20</span> <span>ALTER</span> <span>DATABASE</span> <span>[</span><span>Test</span><span>]</span>
<span>21</span> <span>ADD</span> FILEGROUP <span>[</span><span>FG_Test_Id_02</span><span>]</span>
<span>22</span> 
<span>23</span> 
<span>24</span> 
<span>25</span> <span>--</span><span>3.创建文件</span>
<span>26</span> <span>ALTER</span> <span>DATABASE</span> <span>[</span><span>Test</span><span>]</span>
<span>27</span> <span>ADD</span> <span>FILE</span>
<span>28</span> (NAME <span>=</span> N<span>'</span><span>FG_TestUnique_Id_01_data</span><span>'</span>,FILENAME <span>=</span> N<span>'</span><span>E:\FG_TestUnique_Id_01_data.ndf</span><span>'</span>,SIZE <span>=</span> 1MB, FILEGROWTH <span>=</span><span> 1MB )
</span><span>29</span> <span>TO</span> FILEGROUP <span>[</span><span>FG_Test_Id_01</span><span>]</span><span>;
</span><span>30</span> 
<span>31</span> <span>ALTER</span> <span>DATABASE</span> <span>[</span><span>Test</span><span>]</span>
<span>32</span> <span>ADD</span> <span>FILE</span>
<span>33</span> (NAME <span>=</span> N<span>'</span><span>FG_TestUnique_Id_02_data</span><span>'</span>,FILENAME <span>=</span> N<span>'</span><span>E:\FG_TestUnique_Id_02_data.ndf</span><span>'</span>,SIZE <span>=</span> 1MB, FILEGROWTH <span>=</span><span> 1MB )
</span><span>34</span> <span>TO</span> FILEGROUP <span>[</span><span>FG_Test_Id_02</span><span>]</span><span>;
</span><span>35</span> 
<span>36</span> 
<span>37</span> <span>--</span><span>4.创建表，这个表的数据存放在[FG_Test_Id_01] 文件组上</span>
<span>38</span> <span>CREATE</span> <span>TABLE</span> aa(id <span>INT</span> ,cname <span>NVARCHAR</span>(<span>4000</span>)) <span>ON</span> <span>[</span><span>FG_Test_Id_01</span><span>]</span> 
<span>39</span> <span>GO</span>
<span>40</span> 
<span>41</span> 
<span>42</span> <span>--</span><span>5.插入数据</span>
<span>43</span> <span>INSERT</span> <span>INTO</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span>
<span>44</span> <span>SELECT</span> <span>1</span>,<span>REPLICATE</span>(<span>'</span><span>s</span><span>'</span>,<span>3000</span><span>)
</span><span>45</span> <span>GO</span> <span>500</span>
<span>46</span> 
<span>47</span> 
<span>48</span> <span>--</span><span>6.查询数据</span>
<span>49</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span>
<span>50</span> 
<span>51</span> 
<span>52</span> <span>--</span><span>7.创建聚集索引在[FG_Test_Id_02]文件组上</span>
<span>53</span> <span>CREATE</span> <span>CLUSTERED</span> <span>INDEX</span> PK_ID <span>ON</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span>(<span>[</span><span>id</span><span>]</span>) <span>WITH</span>(ONLINE<span>=</span><span>ON</span>) <span>ON</span> <span>[</span><span>FG_Test_Id_02</span><span>]</span>
<span>54</span> <span>GO</span>
<span>55</span> 
<span>56</span> 
<span>57</span> <span>--</span><span>8.我们查看一下文件组的逻辑文件名</span>
<span>58</span> <span>EXEC</span> <span>[</span><span>sys</span><span>]</span>.<span>[</span><span>sp_helpdb</span><span>]</span> <span>@dbname</span> <span>=</span> TEST <span>--</span><span> sysname</span>
<span>59</span> 
<span>65</span> 
<span>66</span> <span>--9</span><span>.移除FG_Test_Id_01文件组</span>
<span>67</span> <span>ALTER</span> <span>DATABASE</span><span> TEST
</span><span>68</span> REMOVE <span>FILE</span> FG_TestUnique_Id_01_data

当你移动数据到文件组[FG_Test_Id_02]上时，这时候文件组[FG_Test_Id_01]里面已经没有数据了
使用下面的脚本查看

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

<span> 1</span> <span>--</span><span>数据库文件、大小和已经使用空间</span>
<span> 2</span> <span>USE</span> <span>[</span><span>Test</span><span>]</span>  <span>--</span><span>要查看的当前数据库的使用空间，自动增长大小，数据库文件位置</span>
<span> 3</span> <span>GO</span>
<span> 4</span> <span>set</span> nocount <span>on</span>
<span> 5</span> <span>create</span> <span>table</span><span> #Data(
</span><span> 6</span>       FileID <span>int</span> <span>NOT</span> <span>NULL</span><span>,
</span><span> 7</span>       <span>[</span><span>FileGroupId</span><span>]</span> <span>int</span> <span>NOT</span> <span>NULL</span><span>,
</span><span> 8</span>       TotalExtents <span>int</span> <span>NOT</span> <span>NULL</span><span>,
</span><span> 9</span>       UsedExtents <span>int</span> <span>NOT</span> <span>NULL</span><span>,
</span><span>10</span>       <span>[</span><span>FileName</span><span>]</span> sysname <span>NOT</span> <span>NULL</span><span>,
</span><span>11</span>       <span>[</span><span>FilePath</span><span>]</span> <span>nvarchar</span>(<span>MAX</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>12</span>       <span>[</span><span>FileGroup</span><span>]</span> <span>varchar</span>(<span>MAX</span>) <span>NULL</span><span>)
</span><span>13</span> 
<span>14</span> <span>create</span> <span>table</span><span> #Results(
</span><span>15</span>       db sysname <span>NULL</span><span> ,
</span><span>16</span>       FileType <span>varchar</span>(<span>4</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>17</span>       <span>[</span><span>FileGroup</span><span>]</span> sysname <span>not</span> <span>null</span><span>,
</span><span>18</span>       <span>[</span><span>FileName</span><span>]</span> sysname <span>NOT</span> <span>NULL</span><span>,
</span><span>19</span>       TotalMB numeric(<span>18</span>,<span>2</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>20</span>       UsedMB numeric(<span>18</span>,<span>2</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>21</span>       PctUsed numeric(<span>18</span>,<span>2</span>) <span>NULL</span><span>,
</span><span>22</span>       FilePath <span>nvarchar</span>(<span>MAX</span>) <span>NULL</span><span>,
</span><span>23</span>       FileID <span>int</span> <span>null</span><span>)
</span><span>24</span> 
<span>25</span> <span>create</span> <span>table</span> #<span>Log</span><span>(
</span><span>26</span>       db sysname <span>NOT</span> <span>NULL</span><span>,
</span><span>27</span>       LogSize numeric(<span>18</span>,<span>5</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>28</span>       LogUsed numeric(<span>18</span>,<span>5</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>29</span>       Status <span>int</span> <span>NOT</span> <span>NULL</span><span>,
</span><span>30</span>       <span>[</span><span>FilePath</span><span>]</span> <span>nvarchar</span>(<span>MAX</span>) <span>NULL</span><span>)
</span><span>31</span> 
<span>32</span> <span>INSERT</span> #Data (FileID, <span>[</span><span>FileGroupId</span><span>]</span>, TotalExtents, UsedExtents, <span>[</span><span>FileName</span><span>]</span>, <span>[</span><span>FilePath</span><span>]</span><span>)
</span><span>33</span> <span>EXEC</span> (<span>'</span><span>DBCC showfilestats WITH NO_INFOMSGS</span><span>'</span><span>)
</span><span>34</span> 
<span>35</span> <span>update</span><span> #Data
</span><span>36</span> <span>set</span> #Data.FileGroup <span>=</span><span> sysfilegroups.groupname
</span><span>37</span> <span>from</span><span> #Data, sysfilegroups
</span><span>38</span> <span>where</span> #Data.FileGroupId <span>=</span><span> sysfilegroups.groupid
</span><span>39</span> 
<span>40</span> <span>INSERT</span> <span>INTO</span> #Results (db, <span>[</span><span>FileGroup</span><span>]</span>, FileType, <span>[</span><span>FileName</span><span>]</span><span>, TotalMB, UsedMB, PctUsed, FilePath, FileID)
</span><span>41</span> <span>SELECT</span> <span>DB_NAME</span><span>() db,
</span><span>42</span>             <span>[</span><span>FileGroup</span><span>]</span><span>,
</span><span>43</span>             <span>'</span><span>Data</span><span>'</span><span> FileType,
</span><span>44</span>             <span>[</span><span>FileName</span><span>]</span><span>,
</span><span>45</span>             TotalExtents <span>*</span> <span>64</span>.<span>/</span><span>1024</span><span>. TotalMB,
</span><span>46</span>             UsedExtents <span>*</span><span>64</span>.<span>/</span><span>1024</span><span> UsedMB,
</span><span>47</span>             UsedExtents<span>*</span><span>100</span>. <span>/</span><span>TotalExtents  UsedPct,
</span><span>48</span>             <span>[</span><span>FilePath</span><span>]</span><span>,
</span><span>49</span> <span>            FileID
</span><span>50</span> <span>FROM</span><span> #Data
</span><span>51</span> <span>order</span> <span>BY</span> <span>--</span><span>1,2</span>
<span>52</span> <span>DB_NAME</span>(), <span>[</span><span>FileGroup</span><span>]</span>
<span>53</span> 
<span>54</span> <span>insert</span> #<span>Log</span><span> (db,LogSize,LogUsed,Status)
</span><span>55</span> <span>exec</span>(<span>'</span><span>dbcc sqlperf(logspace) WITH NO_INFOMSGS </span><span>'</span><span>)
</span><span>56</span> 
<span>57</span> <span>insert</span> #Results(db, <span>[</span><span>FileGroup</span><span>]</span>, FileType, <span>[</span><span>FileName</span><span>]</span><span>,  TotalMB,UsedMB, PctUsed, FilePath, FileID)
</span><span>58</span> <span>select</span> <span>DB_NAME</span><span>() db,
</span><span>59</span>             <span>'</span><span>Log</span><span>'</span> <span>[</span><span>FileGroup</span><span>]</span><span>,
</span><span>60</span>             <span>'</span><span>Log</span><span>'</span><span> FileType,
</span><span>61</span>             s.<span>[</span><span>name</span><span>]</span> <span>[</span><span>FileName</span><span>]</span><span>,
</span><span>62</span>             s.Size<span>/</span><span>128</span>. <span>as</span><span> LogSize ,
</span><span>63</span>             <span>FILEPROPERTY</span>(s.name,<span>'</span><span>spaceused</span><span>'</span>)<span>/</span><span>8.00</span> <span>/</span><span>16.00</span> <span>As</span><span> LogUsedSpace,
</span><span>64</span>             ((<span>FILEPROPERTY</span>(s.name,<span>'</span><span>spaceused</span><span>'</span>)<span>/</span><span>8.00</span> <span>/</span><span>16.00</span>)<span>*</span><span>100</span>)<span>/</span>(s.Size<span>/</span><span>128</span><span>.) UsedPct,
</span><span>65</span> <span>            s.FileName FilePath,
</span><span>66</span> <span>            s.FileID FileID
</span><span>67</span>       <span>from</span> #<span>Log</span><span> l , master.dbo.sysaltfiles f , dbo.sysfiles s
</span><span>68</span>       <span>where</span> f.dbid <span>=</span> <span>DB_ID</span><span>()
</span><span>69</span>       <span>and</span> (s.status <span>&</span> <span>0x40</span>) <span></span> <span>0</span>
<span>70</span>       <span>and</span> s.FileID <span>=</span><span> f.FileID
</span><span>71</span>       <span>and</span> l.db <span>=</span> <span>DB_NAME</span><span>()
</span><span>72</span> 
<span>73</span> <span>SELECT</span> r.db <span>AS</span> "<span>Database</span><span>",
</span><span>74</span> r.FileType <span>AS</span> "<span>File</span><span> type",
</span><span>75</span> <span>CASE</span>
<span>76</span>      <span>WHEN</span> r.FileGroup <span>=</span> <span>'</span><span>Log</span><span>'</span> <span>Then</span> <span>'</span><span>N/A</span><span>'</span>
<span>77</span>      <span>ELSE</span><span> r.FileGroup
</span><span>78</span> <span>END</span> "<span>File</span> <span>group</span><span>",
</span><span>79</span> r.FileName <span>AS</span> "Logical <span>file</span><span> name",
</span><span>80</span> r.TotalMB <span>AS</span><span> "Total size (MB)",
</span><span>81</span> r.UsedMB <span>AS</span><span> "Used (MB)",
</span><span>82</span> r.PctUsed <span>AS</span> "Used (<span>%</span><span>)",
</span><span>83</span> r.FilePath <span>AS</span> "<span>File</span><span> name",
</span><span>84</span> r.FileID <span>AS</span> "<span>File</span><span> ID",
</span><span>85</span> <span>CASE</span> <span>WHEN</span> s.maxsize <span>=</span> <span>-</span><span>1</span> <span>THEN</span> <span>null</span>
<span>86</span>     <span>ELSE</span> <span>CONVERT</span>(<span>decimal</span>(<span>18</span>,<span>2</span>), s.maxsize <span>/</span><span>128</span><span>.)
</span><span>87</span> <span>END</span> "<span>Max</span><span>. size (MB)",
</span><span>88</span> <span>CONVERT</span>(<span>decimal</span>(<span>18</span>,<span>2</span>), s.growth <span>/</span><span>128</span><span>.) "Autogrowth increment (MB)"
</span><span>89</span> <span>FROM</span><span> #Results r
</span><span>90</span> <span>INNER</span> <span>JOIN</span><span> dbo.sysfiles s
</span><span>91</span> <span>ON</span> r.FileID <span>=</span><span> s.FileID
</span><span>92</span> <span>ORDER</span> <span>BY</span> <span>1</span>,<span>2</span>,<span>3</span>,<span>4</span>,<span>5</span>
<span>93</span> 
<span>94</span> <span>DROP</span> <span>TABLE</span><span> #Data
</span><span>95</span> <span>DROP</span> <span>TABLE</span><span> #Results
</span><span>96</span> <span>DROP</span> <span>TABLE</span> #<span>Log</span>

View Code

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

使用下面的SQL语句移除文件组[FG_Test_Id_01]就可以了

 
<span>5</span> <span>--9</span><span>.移除FG_Test_Id_01文件组</span>
<span>6</span> <span>ALTER</span> <span>DATABASE</span><span> TEST
</span><span>7</span> REMOVE <span>FILE</span> FG_TestUnique_Id_01_data

此时就只剩下主文件组和[FG_Test_Id_02]文件组了
SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

注意：如果不使用聚集索引来移动文件组[FG_Test_Id_01]上的数据到文件组[FG_Test_Id_02]

<span>1</span> <span>--</span><span>4.创建表，这个表的数据存放在[FG_Test_Id_01] 文件组上</span>
<span>2</span> <span>CREATE</span> <span>TABLE</span> aa(id <span>INT</span> ,cname <span>NVARCHAR</span>(<span>4000</span>)) <span>ON</span> <span>[</span><span>FG_Test_Id_01</span><span>]</span> 
<span>3</span> <span>GO</span>

直接使用下面SQL语句来收缩文件会报错

<span>1</span> <span>-</span><span>收缩一下FG_Test_Id_01文件组文件</span>
<span>2</span> <span>DBCC</span> SHRINKFILE(FG_TestUnique_Id_01_data,EMPTYFILE)

报错内容

<span>1</span> <span>DBCC</span> SHRINKFILE: 无法移动堆页 <span>3</span>:<span>515</span><span>。
</span><span>2</span> 消息 <span>2555</span>，级别 <span>16</span>，状态 <span>1</span>，第 <span>1</span><span> 行
</span><span>3</span> <span>无法将文件 "FG_TestUnique_Id_01_data" 的所有内容移到其他位置，以完成清空文件操作。
</span><span>4</span> <span>语句已终止。
</span><span>5</span> <span>DBCC</span> 执行完毕。如果 <span>DBCC</span><span> 输出了错误信息，请与系统管理员联系。
</span><span>6</span> 消息 <span>1105</span>，级别 <span>17</span>，状态 <span>2</span>，第 <span>1</span><span> 行
</span><span>7</span> 无法为数据库 <span>'</span><span>Test</span><span>'</span> 中的对象 <span>'</span><span>dbo.aa</span><span>'</span> 分配空间，因为 <span>'</span><span>FG_Test_Id_01</span><span>'</span> 文件组已满。请删除不需要的文件、删除文件组中的对象、将其他文件添加到文件组或为文件组中的现有文件启用自动增长，以便增加可用磁盘空间。

因为文件组[FG_Test_Id_01]里还有数据，不能清空

两个物理文件（数据文件）对应一个文件组的情况（一对多）

上面的情况是每个物理文件（数据文件）对应一个文件组的情况

下面这种情况是两个物理文件（数据文件）对于一个文件组的情况

一对一的情况使用聚集索引里移动数据，而一对一的情况使用DBCC SHRINKFILE

创建数据库

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

test1和test2这两个数据文件归属于主文件组primary，而数据文件test1最大大小为6MB初始大小为5MB

test2数据文件最大大小没有限制

使用下面脚本添加数据到主文件组

<span> 1</span> <span>--</span><span>1.创建表，这个表的数据存放在主文件组上</span>
<span> 2</span> <span>CREATE</span> <span>TABLE</span> aa(id <span>INT</span> ,cname <span>NVARCHAR</span>(<span>4000</span><span>)) 
</span><span> 3</span> <span>GO</span>
<span> 4</span> 
<span> 5</span> 
<span> 6</span> <span>--</span><span>2.插入数据</span>
<span> 7</span> <span>INSERT</span> <span>INTO</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span>
<span> 8</span> <span>SELECT</span> <span>1</span>,<span>REPLICATE</span>(<span>'</span><span>s</span><span>'</span>,<span>3000</span><span>)
</span><span> 9</span> <span>GO</span> <span>600</span>
<span>10</span> 
<span>11</span> 
<span>12</span> <span>--</span><span>3.查询数据</span>
<span>13</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span>
<span>14</span> 
<span>15</span> 
<span>16</span> 
<span>17</span> 
<span>18</span> <span>--</span><span>4.我们查看一下文件组的逻辑文件名</span>
<span>19</span> <span>EXEC</span> <span>[</span><span>sys</span><span>]</span>.<span>[</span><span>sp_helpdb</span><span>]</span> <span>@dbname</span> <span>=</span><span> TEST1
</span><span>20</span>  <span>--</span><span> sysname</span>
<span>21</span> <span>SELECT</span>  <span>DB_NAME</span>(database_id) <span>AS</span><span> DatabaseName ,
</span><span>22</span>         Name <span>AS</span><span> Logical_Name ,
</span><span>23</span> <span>        Physical_Name ,
</span><span>24</span>         ( size <span>*</span> <span>8</span> ) <span>/</span> <span>1024</span><span> SizeMB
</span><span>25</span> <span>FROM</span><span>    sys.master_files
</span><span>26</span> <span>WHERE</span>   <span>DB_NAME</span>(database_id) <span>=</span> <span>'</span><span>Test1</span><span>'</span>

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

因为第一个数据文件的最大大小限制，所以有一部分数据插入到了test2.ndf

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

现在修改test1数据文件的最大大小限制为20MB

相关SQL

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

<span>1</span> <span>ALTER</span> <span>DATABASE</span> <span>[</span><span>Test1</span><span>]</span> MODIFY <span>FILE</span>(name<span>=</span><span>'</span><span>Test1</span><span>'</span>,SIZE<span>=</span>5MB, filegrowth<span>=</span>1MB, MAXSIZE<span>=</span>20MB)

View Code

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

执行下面的SQL语句

<span>1</span> <span>--</span><span>5.收缩文件</span>
<span>2</span> <span>DBCC</span><span> SHRINKFILE(test2,EMPTYFILE)
</span><span>3</span> 
<span>4</span> 
<span>5</span> <span>--</span><span>6.移除test2数据文件test2.ndf</span>
<span>6</span> <span>ALTER</span> <span>DATABASE</span><span> TEST1
</span><span>7</span> REMOVE <span>FILE</span> test2

在执行第五条语句的时候，执行下面脚本

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

<span> 1</span> <span>--</span><span>数据库文件、大小和已经使用空间</span>
<span> 2</span> <span>USE</span> <span>[</span><span>Test1</span><span>]</span>  <span>--</span><span>要查看的当前数据库的使用空间，自动增长大小，数据库文件位置</span>
<span> 3</span> <span>GO</span>
<span> 4</span> <span>set</span> nocount <span>on</span>
<span> 5</span> <span>create</span> <span>table</span><span> #Data(
</span><span> 6</span>       FileID <span>int</span> <span>NOT</span> <span>NULL</span><span>,
</span><span> 7</span>       <span>[</span><span>FileGroupId</span><span>]</span> <span>int</span> <span>NOT</span> <span>NULL</span><span>,
</span><span> 8</span>       TotalExtents <span>int</span> <span>NOT</span> <span>NULL</span><span>,
</span><span> 9</span>       UsedExtents <span>int</span> <span>NOT</span> <span>NULL</span><span>,
</span><span>10</span>       <span>[</span><span>FileName</span><span>]</span> sysname <span>NOT</span> <span>NULL</span><span>,
</span><span>11</span>       <span>[</span><span>FilePath</span><span>]</span> <span>nvarchar</span>(<span>MAX</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>12</span>       <span>[</span><span>FileGroup</span><span>]</span> <span>varchar</span>(<span>MAX</span>) <span>NULL</span><span>)
</span><span>13</span> 
<span>14</span> <span>create</span> <span>table</span><span> #Results(
</span><span>15</span>       db sysname <span>NULL</span><span> ,
</span><span>16</span>       FileType <span>varchar</span>(<span>4</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>17</span>       <span>[</span><span>FileGroup</span><span>]</span> sysname <span>not</span> <span>null</span><span>,
</span><span>18</span>       <span>[</span><span>FileName</span><span>]</span> sysname <span>NOT</span> <span>NULL</span><span>,
</span><span>19</span>       TotalMB numeric(<span>18</span>,<span>2</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>20</span>       UsedMB numeric(<span>18</span>,<span>2</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>21</span>       PctUsed numeric(<span>18</span>,<span>2</span>) <span>NULL</span><span>,
</span><span>22</span>       FilePath <span>nvarchar</span>(<span>MAX</span>) <span>NULL</span><span>,
</span><span>23</span>       FileID <span>int</span> <span>null</span><span>)
</span><span>24</span> 
<span>25</span> <span>create</span> <span>table</span> #<span>Log</span><span>(
</span><span>26</span>       db sysname <span>NOT</span> <span>NULL</span><span>,
</span><span>27</span>       LogSize numeric(<span>18</span>,<span>5</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>28</span>       LogUsed numeric(<span>18</span>,<span>5</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>29</span>       Status <span>int</span> <span>NOT</span> <span>NULL</span><span>,
</span><span>30</span>       <span>[</span><span>FilePath</span><span>]</span> <span>nvarchar</span>(<span>MAX</span>) <span>NULL</span><span>)
</span><span>31</span> 
<span>32</span> <span>INSERT</span> #Data (FileID, <span>[</span><span>FileGroupId</span><span>]</span>, TotalExtents, UsedExtents, <span>[</span><span>FileName</span><span>]</span>, <span>[</span><span>FilePath</span><span>]</span><span>)
</span><span>33</span> <span>EXEC</span> (<span>'</span><span>DBCC showfilestats WITH NO_INFOMSGS</span><span>'</span><span>)
</span><span>34</span> 
<span>35</span> <span>update</span><span> #Data
</span><span>36</span> <span>set</span> #Data.FileGroup <span>=</span><span> sysfilegroups.groupname
</span><span>37</span> <span>from</span><span> #Data, sysfilegroups
</span><span>38</span> <span>where</span> #Data.FileGroupId <span>=</span><span> sysfilegroups.groupid
</span><span>39</span> 
<span>40</span> <span>INSERT</span> <span>INTO</span> #Results (db, <span>[</span><span>FileGroup</span><span>]</span>, FileType, <span>[</span><span>FileName</span><span>]</span><span>, TotalMB, UsedMB, PctUsed, FilePath, FileID)
</span><span>41</span> <span>SELECT</span> <span>DB_NAME</span><span>() db,
</span><span>42</span>             <span>[</span><span>FileGroup</span><span>]</span><span>,
</span><span>43</span>             <span>'</span><span>Data</span><span>'</span><span> FileType,
</span><span>44</span>             <span>[</span><span>FileName</span><span>]</span><span>,
</span><span>45</span>             TotalExtents <span>*</span> <span>64</span>.<span>/</span><span>1024</span><span>. TotalMB,
</span><span>46</span>             UsedExtents <span>*</span><span>64</span>.<span>/</span><span>1024</span><span> UsedMB,
</span><span>47</span>             UsedExtents<span>*</span><span>100</span>. <span>/</span><span>TotalExtents  UsedPct,
</span><span>48</span>             <span>[</span><span>FilePath</span><span>]</span><span>,
</span><span>49</span> <span>            FileID
</span><span>50</span> <span>FROM</span><span> #Data
</span><span>51</span> <span>order</span> <span>BY</span> <span>--</span><span>1,2</span>
<span>52</span> <span>DB_NAME</span>(), <span>[</span><span>FileGroup</span><span>]</span>
<span>53</span> 
<span>54</span> <span>insert</span> #<span>Log</span><span> (db,LogSize,LogUsed,Status)
</span><span>55</span> <span>exec</span>(<span>'</span><span>dbcc sqlperf(logspace) WITH NO_INFOMSGS </span><span>'</span><span>)
</span><span>56</span> 
<span>57</span> <span>insert</span> #Results(db, <span>[</span><span>FileGroup</span><span>]</span>, FileType, <span>[</span><span>FileName</span><span>]</span><span>,  TotalMB,UsedMB, PctUsed, FilePath, FileID)
</span><span>58</span> <span>select</span> <span>DB_NAME</span><span>() db,
</span><span>59</span>             <span>'</span><span>Log</span><span>'</span> <span>[</span><span>FileGroup</span><span>]</span><span>,
</span><span>60</span>             <span>'</span><span>Log</span><span>'</span><span> FileType,
</span><span>61</span>             s.<span>[</span><span>name</span><span>]</span> <span>[</span><span>FileName</span><span>]</span><span>,
</span><span>62</span>             s.Size<span>/</span><span>128</span>. <span>as</span><span> LogSize ,
</span><span>63</span>             <span>FILEPROPERTY</span>(s.name,<span>'</span><span>spaceused</span><span>'</span>)<span>/</span><span>8.00</span> <span>/</span><span>16.00</span> <span>As</span><span> LogUsedSpace,
</span><span>64</span>             ((<span>FILEPROPERTY</span>(s.name,<span>'</span><span>spaceused</span><span>'</span>)<span>/</span><span>8.00</span> <span>/</span><span>16.00</span>)<span>*</span><span>100</span>)<span>/</span>(s.Size<span>/</span><span>128</span><span>.) UsedPct,
</span><span>65</span> <span>            s.FileName FilePath,
</span><span>66</span> <span>            s.FileID FileID
</span><span>67</span>       <span>from</span> #<span>Log</span><span> l , master.dbo.sysaltfiles f , dbo.sysfiles s
</span><span>68</span>       <span>where</span> f.dbid <span>=</span> <span>DB_ID</span><span>()
</span><span>69</span>       <span>and</span> (s.status <span>&</span> <span>0x40</span>) <span></span> <span>0</span>
<span>70</span>       <span>and</span> s.FileID <span>=</span><span> f.FileID
</span><span>71</span>       <span>and</span> l.db <span>=</span> <span>DB_NAME</span><span>()
</span><span>72</span> 
<span>73</span> <span>SELECT</span> r.db <span>AS</span> "<span>Database</span><span>",
</span><span>74</span> r.FileType <span>AS</span> "<span>File</span><span> type",
</span><span>75</span> <span>CASE</span>
<span>76</span>      <span>WHEN</span> r.FileGroup <span>=</span> <span>'</span><span>Log</span><span>'</span> <span>Then</span> <span>'</span><span>N/A</span><span>'</span>
<span>77</span>      <span>ELSE</span><span> r.FileGroup
</span><span>78</span> <span>END</span> "<span>File</span> <span>group</span><span>",
</span><span>79</span> r.FileName <span>AS</span> "Logical <span>file</span><span> name",
</span><span>80</span> r.TotalMB <span>AS</span><span> "Total size (MB)",
</span><span>81</span> r.UsedMB <span>AS</span><span> "Used (MB)",
</span><span>82</span> r.PctUsed <span>AS</span> "Used (<span>%</span><span>)",
</span><span>83</span> r.FilePath <span>AS</span> "<span>File</span><span> name",
</span><span>84</span> r.FileID <span>AS</span> "<span>File</span><span> ID",
</span><span>85</span> <span>CASE</span> <span>WHEN</span> s.maxsize <span>=</span> <span>-</span><span>1</span> <span>THEN</span> <span>null</span>
<span>86</span>     <span>ELSE</span> <span>CONVERT</span>(<span>decimal</span>(<span>18</span>,<span>2</span>), s.maxsize <span>/</span><span>128</span><span>.)
</span><span>87</span> <span>END</span> "<span>Max</span><span>. size (MB)",
</span><span>88</span> <span>CONVERT</span>(<span>decimal</span>(<span>18</span>,<span>2</span>), s.growth <span>/</span><span>128</span><span>.) "Autogrowth increment (MB)"
</span><span>89</span> <span>FROM</span><span> #Results r
</span><span>90</span> <span>INNER</span> <span>JOIN</span><span> dbo.sysfiles s
</span><span>91</span> <span>ON</span> r.FileID <span>=</span><span> s.FileID
</span><span>92</span> <span>ORDER</span> <span>BY</span> <span>1</span>,<span>2</span>,<span>3</span>,<span>4</span>,<span>5</span>
<span>93</span> 
<span>94</span> <span>DROP</span> <span>TABLE</span><span> #Data
</span><span>95</span> <span>DROP</span> <span>TABLE</span><span> #Results
</span><span>96</span> <span>DROP</span> <span>TABLE</span> #<span>Log</span>

View Code

你会发现
SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

数据都移动到了test1.mdf里去了

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

执行第六条SQL语句，删除test2.ndf文件

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

数据没有丢失

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

这里关键在于EMPTYFILE参数：DBCC SHRINKFILE(test2,EMPTYFILE)

总结

这里要根据是一对多还是一对一来选择移动数据的方法

如果是一对多：使用DBCC SHRINKFILE(要移动数据的数据文件,EMPTYFILE)

如果是一对一：创建聚集索引

参考文章： [SQL]透過 DBCC SHRINKFILE([要清空的File], EMPTYFILE) 來將資料移到另一個資料檔之中

大家可以做一下实验

对于同一个文件组里的多个数据文件（不一定是主文件组），

比如有有个文件组叫[FG_Test_01]，里面有两个数据文件test3.ndf和test4.ndf

test3.ndf和test4.ndf都有数据

如果我运行DBCC SHRINKFILE(test4,EMPTYFILE)，test4.ndf里的数据是否会移动到test3.ndf还是会移动到test1.mdf？？？

这个实验留给大家o(∩_∩)o

2014-1-14补充：

这个实验的测试脚本和结果

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

<span> 1</span> <span>USE</span><span> master
</span><span> 2</span> <span>GO</span>
<span> 3</span> 
<span> 4</span> <span>--</span><span>DROP DATABASE [Test]</span>
<span> 5</span> 
<span> 6</span> 
<span> 7</span> <span>IF</span> <span>EXISTS</span>(<span>SELECT</span> <span>*</span> <span>FROM</span> sys.<span>[</span><span>databases</span><span>]</span> <span>WHERE</span> <span>[</span><span>database_id</span><span>]</span><span>=</span><span>DB_ID</span>(<span>'</span><span>Test</span><span>'</span><span>))
</span><span> 8</span> <span>DROP</span> <span>DATABASE</span> <span>[</span><span>Test</span><span>]</span>
<span> 9</span> 
<span>10</span> <span>--</span><span>1.创建数据库</span>
<span>11</span> <span>CREATE</span> <span>DATABASE</span> <span>[</span><span>Test</span><span>]</span>
<span>12</span> <span>GO</span>
<span>13</span> 
<span>14</span> <span>USE</span> <span>[</span><span>Test</span><span>]</span>
<span>15</span> <span>GO</span>
<span>16</span> 
<span>17</span> 
<span>18</span> <span>--</span><span>2.创建文件组</span>
<span>19</span> <span>ALTER</span> <span>DATABASE</span> <span>[</span><span>Test</span><span>]</span>
<span>20</span> <span>ADD</span> FILEGROUP <span>[</span><span>FG_Test_Id_01</span><span>]</span>
<span>21</span> 
<span>22</span> 
<span>23</span> 
<span>24</span> 
<span>25</span> 
<span>26</span> <span>--</span><span>3.创建文件</span>
<span>27</span> <span>ALTER</span> <span>DATABASE</span> <span>[</span><span>Test</span><span>]</span>
<span>28</span> <span>ADD</span> <span>FILE</span>
<span>29</span> (NAME <span>=</span> N<span>'</span><span>FG_TestUnique_Id_01_data</span><span>'</span>,FILENAME <span>=</span> N<span>'</span><span>E:\FG_TestUnique_Id_01_data.ndf</span><span>'</span>,SIZE <span>=</span> 1MB, FILEGROWTH <span>=</span><span> 1MB )
</span><span>30</span> <span>TO</span> FILEGROUP <span>[</span><span>FG_Test_Id_01</span><span>]</span><span>;
</span><span>31</span> 
<span>32</span> <span>ALTER</span> <span>DATABASE</span> <span>[</span><span>Test</span><span>]</span>
<span>33</span> <span>ADD</span> <span>FILE</span>
<span>34</span> (NAME <span>=</span> N<span>'</span><span>FG_TestUnique_Id_02_data</span><span>'</span>,FILENAME <span>=</span> N<span>'</span><span>E:\FG_TestUnique_Id_02_data.ndf</span><span>'</span>,SIZE <span>=</span> 1MB, FILEGROWTH <span>=</span><span> 1MB )
</span><span>35</span> <span>TO</span> FILEGROUP <span>[</span><span>FG_Test_Id_01</span><span>]</span><span>;
</span><span>36</span> 
<span>37</span> 
<span>38</span> 
<span>39</span> 
<span>40</span> 
<span>41</span> <span>--</span><span>4.创建表，这个表的数据存放在[FG_Test_Id_02] 文件组上</span>
<span>42</span> <span>CREATE</span> <span>TABLE</span> aa(id <span>INT</span> ,cname <span>NVARCHAR</span>(<span>4000</span>)) <span>ON</span> <span>[</span><span>FG_Test_Id_01</span><span>]</span> 
<span>43</span> <span>GO</span>
<span>44</span> 
<span>45</span> 
<span>46</span> <span>--</span><span>5.插入数据</span>
<span>47</span> <span>INSERT</span> <span>INTO</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span>
<span>48</span> <span>SELECT</span> <span>1</span>,<span>REPLICATE</span>(<span>'</span><span>s</span><span>'</span>,<span>3000</span><span>)
</span><span>49</span> <span>GO</span> <span>1000</span>
<span>50</span> 
<span>51</span> 
<span>52</span> <span>--</span><span>6.查询数据</span>
<span>53</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span>
<span>54</span> 
<span>55</span> 
<span>56</span> 
<span>57</span> 
<span>58</span> <span>--</span><span>7.我们查看一下文件组的逻辑文件名</span>
<span>59</span> <span>EXEC</span> <span>[</span><span>sys</span><span>]</span>.<span>[</span><span>sp_helpdb</span><span>]</span> <span>@dbname</span> <span>=</span> TEST <span>--</span><span> sysname</span>
<span>60</span> 
<span>61</span> 
<span>62</span> 
<span>63</span> <span>--</span><span>8.收缩文件</span>
<span>64</span> <span>DBCC</span><span> SHRINKFILE(FG_TestUnique_Id_02_data,EMPTYFILE)
</span><span>65</span> 
<span>66</span> 
<span>67</span> <span>--</span><span>9.移除FG_TestUnique_Id_03_data数据文件FG_TestUnique_Id_03_data.ndf</span>
<span>68</span> <span>ALTER</span> <span>DATABASE</span><span> TEST
</span><span>69</span> REMOVE <span>FILE</span><span> FG_TestUnique_Id_02_data
</span><span>70</span> 
<span>71</span> 
<span>72</span> 
<span>73</span> <span>--</span><span>10.查询数据</span>
<span>74</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span>
<span>75</span> <span>SELECT</span> <span>COUNT</span>(<span>*</span>) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span>

View Code

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

数据没有丢失

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

答案：

FG_TestUnique_Id_02_data.ndf里的数据会移动到FG_TestUnique_Id_01_data.ndf，不会移动到Test.mdf

因为DBCC SHRINKFILE只能在同一文件组内移动数据，而mdf只能属于主文件组primary

如有不对的地方，欢迎大家拍砖o(∩_∩)o

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：Could not load file or assembly 'Microsoft.SqlServerNext article：mysql (1) : WINDOWS下安装mysql

See more

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

Related articles