SQL Server 2008R2 Filestream 简介以及垃圾回收集

起因

公司的一个.net 项目已经运行了将近5年的时间了，数据库当然是用的是Sql Server,版本是2008R2。其中需要将文件存储到数据库中，因此使用了Sql Server 2008 R2数据库，因为随着数据量的增加和业务流程中相关无用的上传文件需要清理，发现单纯的通过Delete语句从数据库中删除相关记录是不会回收磁盘空间的，当然通过数据库收缩操作也没有任何效果。这里来探索下如何才能达到回收文件空间的目的。

首先先来简单的了解下Sql Server 2008R2带的FileStream特性和简单的基本用法。

1.什么是SQL Server FileStream

FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data.

FILESTREAM integrates the SQL Server Database Engine with an NTFS or ReFS file systems by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.

FILESTREAM uses the NT system cache for caching file data. This helps reduce any effect that FILESTREAM data might have on Database Engine performance. The SQL Server buffer pool is not used; therefore, this memory is available for query processing.
以上是微软官方文档对于Sql server 2008R2的FileStream的解释，简单来说就是将VARbinary（MAX）存储的非结构化数据（文件，JSON，xml等）存储到WIndow NT文件系统下一个文件夹里，可以通过Sql 语句来操作也可以通过Win32 API Steam API来访问。

2.SQL Server 2008 R2 FileStream基本使用

(1).基本配置
首先FileStream特性并不是默认开启的，需要通过Sql Server 管理器开启。
数据库要支持Filestream必须开启对该数据库FileStream的支持。直接上图：

其中比传统数据库文件夹多的特性是：
多了一个FileStrem文件，指向一个目录。
(2).用SQL语句访问FileStream 的数据
Insert NULL:

    INSERT INTO Archive.dbo.Records
    VALUES (newid (), 1, NULL);
    GO
    

Insert 长度为0的数据

    INSERT INTO Archive.dbo.Records
    VALUES (newid (), 2, 
  CAST ('' as varbinary(max)));
GO

创建一个文件

    INSERT INTO Archive.dbo.Records
VALUES (newid (), 3, 
  CAST ('Seismic Data' as varbinary(max)));
GO

更新FileStream数据

    UPDATE Archive.dbo.Records
    SET [Chart] = CAST('Xray 1' as varbinary(max))
    WHERE [SerialNumber] = 2;
    

(3).客户端程序获取FileStream数据
Sql Server 提供了如下基本API和函数来访问数据，这里直接引用官方文档：

PathName returns a path as a token to a BLOB. An application uses this token to obtain a Win32 handle and operate on BLOB data.

When the database that contains FILESTREAM data belongs to an Always On availability group, then the PathName function returns a virtual network name (VNN) instead of a computer name.

GET_FILESTREAM_TRANSACTION_CONTEXT() returns a token that represents the current transaction of a session. An application uses this token to bind FILESTREAM file system streaming operations to the transaction.

The OpenSqlFilestream API obtains a Win32 file handle. The application uses the handle to stream the FILESTREAM data, and can then pass the handle to the following Win32 APIs: ReadFile, WriteFile, TransmitFile, SetFilePointer, SetEndOfFile, or FlushFileBuffers. If the application calls any other API by using the handle, an ERROR_ACCESS_DENIED error is returned. The application should close the handle by using CloseHandle.

客户端获取Filestream文件的步骤：
a：获取路径

    DECLARE @filePath varchar(max)
    SELECT @filePath = Chart.PathName()
    FROM Archive.dbo.Records
    WHERE SerialNumber = 3
    PRINT @filepath
    
    

b：获取事务处理上下文

    DECLARE @txContext varbinary(max)
    BEGIN TRANSACTION
    SELECT @txContext = GET_FILESTREAM_TRANSACTION_CONTEXT()
    PRINT @txContext
    COMMIT
    
    

c：获取文件流（C#）

    using System.IO;
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace FILESTREAM
{
class Program
{
    static void Main(string[] args)
    {
        SqlConnection sqlConnection = new SqlConnection(
            "Integrated Security=true;server=(local)");

        SqlCommand sqlCommand = new SqlCommand();
        sqlCommand.Connection = sqlConnection;

        try
        {
            sqlConnection.Open();

            //The first task is to retrieve the file path
            //of the SQL FILESTREAM BLOB that we want to
            //access in the application.

            sqlCommand.CommandText =
                  "SELECT Chart.PathName()"
                + " FROM Archive.dbo.Records"
                + " WHERE SerialNumber = 3";

            String filePath = null;

            Object pathObj = sqlCommand.ExecuteScalar();
            if (DBNull.Value != pathObj)
                filePath = (string)pathObj;
            else
            {
                throw new System.Exception(
                    "Chart.PathName() failed"
                  + " to read the path name "
                  + " for the Chart column.");
            }

            //The next task is to obtain a transaction
            //context. All FILESTREAM BLOB operations
            //occur within a transaction context to
            //maintain data consistency.

            //All SQL FILESTREAM BLOB access must occur in 
            //a transaction. MARS-enabled connections
            //have specific rules for batch scoped transactions,
            //which the Transact-SQL BEGIN TRANSACTION statement
            //violates. To avoid this issue, client applications 
            //should use appropriate API facilities for transaction management, 
            //management, such as the SqlTransaction class.

            SqlTransaction transaction = sqlConnection.BeginTransaction("mainTranaction");
            sqlCommand.Transaction = transaction;

            sqlCommand.CommandText =
                "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";

            Object obj = sqlCommand.ExecuteScalar();
            byte[] txContext = (byte[])obj;

            //The next step is to obtain a handle that
            //can be passed to the Win32 FILE APIs.

            SqlFileStream sqlFileStream = new SqlFileStream(filePath, txContext, FileAccess.ReadWrite);

            byte[] buffer = new byte[512];

            int numBytes = 0;

            //Write the string, "EKG data." to the FILESTREAM BLOB.
            //In your application this string would be replaced with
            //the binary data that you want to write.

            string someData = "EKG data.";
            Encoding unicode = Encoding.GetEncoding(0);

            sqlFileStream.Write(unicode.GetBytes(someData.ToCharArray()),
                0,
                someData.Length);

            //Read the data from the FILESTREAM
            //BLOB.

            sqlFileStream.Seek(0L, SeekOrigin.Begin);

            numBytes = sqlFileStream.Read(buffer, 0, buffer.Length);

            string readData = unicode.GetString(buffer);

            if (numBytes != 0)
                Console.WriteLine(readData);

            //Because reading and writing are finished, FILESTREAM 
            //must be closed. This closes the c# FileStream class, 
            //but does not necessarily close the the underlying 
            //FILESTREAM handle. 
            sqlFileStream.Close();

            //The final step is to commit or roll back the read and write
            //operations that were performed on the FILESTREAM BLOB.

            sqlCommand.Transaction.Commit();
        }
        catch (System.Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
        finally
        {
            sqlConnection.Close();
        }
        return;
    }
}
}

3.SQL Server 2008 R2 FileStream回收探索

言归正传来探索下到底怎么才能回收filestream的不用的垃圾文件

过程：

通过搜索得知应该和checkpoint有关，有人给出了下面的代码：

delete from t_filestream where id = 1 checkpoint

原理上认为之所以不会回收是SQL server的底层设计需要维持文件的稳定性，只有等待手动了检查点的时候才会去回收文件，尝试之后让并卵。有文章支出需要做一次数据的日志的备份操作，也无效，也有人说要对数据库进行DBCC checkpoint操作，将脏数据写入和清除掉，都无效。

突然发现checkpoint操作是不是和数据库恢复模型有关？于是微软相关论坛有人指出在完整模式和大容量日志模式下是不会回收的。想想也对，这些文件本生就是恢复模式的一部分，怎么可能删除掉。于是得出以下正确步骤：

1.将数据恢复模式设为简单
2.执行类似delete from t_filestream where id = 1 checkpoint
3.等待数据被sql engine 慢慢删除

由于同事之前简单的认为删除了数据就会把垃圾文件回收，因此上述过程已经失效了。通过实验，应该这样做：

1.将数据恢复模式设为简单
2.执行checkpoint
3.执行DBCC checkdb
4.等待数据被更新和删除