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将数据移到另一个文件组之后清空文件组并删除文件组