SQL Server 在AlwaysOn上使用内存表"踩坑"

2021-10-06 03:56:00

介绍

因为线上alwayson环境的一个数据库上使用内存表。经过大概一个星期监控程序发现了一个非常严重问题这个数据库的日志文件不会截断，已用空间一直在增加（存在定时的每个小时的日志备份），同时内存表数据库文件也无法删除，下面就介绍一下后面我的处理过程。

数据库：SQL Server2014 Enterprise Edition (64-bit)

删除文件

使用一个单独非alwayson环境的数据库测试。

一、创建内存表

---创建内存表文件组
ALTER DATABASE [test] ADD FILEGROUP [test_ag] CONTAINS MEMORY_OPTIMIZED_DATA   
GO 
----创建内存表数据库文件
ALTER DATABASE [test] 
ADD FILE  
(  
   NAME = 'test_memory',  
   FILENAME ='D:\database\memory'  
)  
TO FILEGROUP [test_ag];  
GO

二、删除内存表数据库文件

USE [test]
GO
ALTER DATABASE [test]  REMOVE FILE [test_memory]
GO

备注：此时还未创建表，创建完后数据库文件执行删除就无法删除，接下来试试在线文档的删除方法方法

三、官方相关的删除方法

即使已使用“DBCC SHRINKFILE”操作清空 FILESTREAM 容器，但出于各种系统维护原因，数据库可能仍然需要保留对已删除文件的引用。 sp_filestream_force_garbage_collection (TRANSACT-SQL)将运行 FILESTREAM 垃圾回收器删除这些文件时，则可以安全进行这些操作。除非 FILESTREAM 垃圾回收器已从 FILESTREAM 容器中删除所有文件，否则 ALTER DATABASEREMOVE FILE 操作将无法删除 FILESTREAM 容器并返回错误。建议使用以下过程删除 FILESTREAM 容器。

1.运行DBCC SHRINKFILE (TRANSACT-SQL)带有 EMPTYFILE 选项以将此容器的活动内容移动到其他容器。

USE test;  
GO  
-- Create a data file and assume it contains data.  
ALTER DATABASE test   
ADD FILE (  
    NAME = Test1data,  
    FILENAME = 'D:\database\t1data.ndf',  
    SIZE = 5MB  
    );  
GO  
-- Empty the data file.  
DBCC SHRINKFILE (test_memory, EMPTYFILE);  
GO

2.确保已在 FULL 或 BULK_LOGGED 恢复模型中执行日志备份。

3.确保复制日志读取器作业已运行（如果相关）。

通过log_reuse_wait_desc的状态可以看到当前数据库已经无需日志备份，当然我已经执行过日志备份。

4.运行sp_filestream_force_garbage_collection (TRANSACT-SQL)强制垃圾回收器删除不再需要此容器中的任何文件。

USE [test]
GO  
EXEC sp_filestream_force_garbage_collection @dbname = N'test' @filename = N' test_memory ';

5.执行带有 REMOVE FILE 选项的 ALTER DATABASE，以删除此容器。

USE [test]
GO
ALTER DATABASE [test]  REMOVE FILE [test_memory]
GO

还是无法删除！！！

四、问题分析

一开始是在alwayson的环境中删除，提示由于副本的原因无法删除。后面单独在一个非alwayson的环境下的数据库测试同样是无法删除，起初以为是创建了内存表的原因后面测试仅仅创建文件组和文件然后来删除文件同样是无法删除，个人猜测有可能是buffer的缘故；在buffer中一直存在内存表相关的文件存在，通过执行DBCC DROPCLEANBUFFERS命令也无法清空buffer中的内存表对象。使尽浑身解数还是无法将它删除掉，最后只能投降了！！！线上环境等不下去；只能使用最不愿使用的生成表结构导出数据的办法来重建新的数据库。

生成脚本重建数据库

创建一个新的数据库同时保证当前数据库可用（重命名当前的数据库，新创建的数据库使用之前的名称这样可以保证应用程序那边不需要改变），这样如果出现什么问题也可以及时的切换回来。

步骤如下（在允许停机维护的情况下进行）：

1.禁用所有相关作业

2禁用应用程序登入用户

同时保证相关进程事务都已完成。

ALTER LOGIN [test] DISABLE
GO

USE [master]
GO
ALTER DATABASE [test] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;--将数据库设置成单用户并回滚当前连接

USE [test];---保持连接操作,防止其它用户此时进行连接
GO

3.执行checkpoint刷新所有脏页

CHECKPOINT

---返回当前buffer中每个数据库所占的buffer大小和buffer中脏页的大小
WITH    CTE1
          AS ( SELECT   COUNT(*) * 8 / 1024 AS dirty_cached_size_MB ,
                        COUNT(*) AS dirty_pages,
                        CASE database_id
                          WHEN 32767 THEN 'ResourceDb'
                          ELSE DB_NAME(database_id)
                        END AS database_name
               FROM     sys.dm_os_buffer_descriptors
               WHERE    is_modified = 1
               GROUP BY DB_NAME(database_id),database_id
             ),
        CET2
          AS ( SELECT   COUNT(*) * 8 / 1024 AS cached_size_MB ,
                        COUNT(*) AS pages,
                        CASE database_id
                          WHEN 32767 THEN 'ResourceDb'
                          ELSE DB_NAME(database_id)
                        END AS database_name
               FROM     sys.dm_os_buffer_descriptors
               GROUP BY DB_NAME(database_id),database_id
             )
    SELECT 
    CET2.database_name,
    CET2.cached_size_MB,
    --CET2.pages,
    CTE1.dirty_cached_size_MB
    --CTE1.dirty_pages 
    FROM CTE1 INNER JOIN CET2 ON CTE1.database_name = CET2.database_name

---将数据库选项改成多用户访问
ALTER DATABASE [test] 
SET MULTI_USER;

4.生成数据库脚本

5.重命名旧的数据库

注意：如果数据库是在alwayson中，需要先从可用性数据库中删除，否则无法重命名数据库。

/*
1.断开数据库所有连接同时禁止新的连接进来
2.比如禁止登入用户、将实例设为单用户模式等。
*/
----1.设置数据库脱机
USE [master] 
ALTER DATABASE [test] SET  OFFLINE WITH ROLLBACK IMMEDIATE;

----2.手动修改数据库物理文件名，例如将test.mdf改成test_old.mdf

----3.语句修改
USE [master] 
ALTER DATABASE [test] 
MODIFY FILE (NAME = test, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test_old.mdf');
GO
ALTER DATABASE [test] 
MODIFY FILE (NAME = test_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test_old_log.ldf');
GO

---4.设置数据库在线
USE [master] 
ALTER DATABASE [test] SET  ONLINE


----5.修改数据库逻辑文件名

USE [test]
GO
ALTER DATABASE [test] MODIFY FILE (NAME=N'test', NEWNAME=N'test_old')
GO
USE [test]
GO
ALTER DATABASE [test] MODIFY FILE (NAME=N'test_log', NEWNAME=N'test_old_log')
GO

----6.重命名数据库
USE [master] 
EXEC sp_renamedb N'test', N'test_old';  

----7.查询
SELECT *
FROM sys.master_files
WHERE database_id = DB_ID('test_old');

6.创建新的数据库同时导入脚本到新的数据库

如果同时导出表结构和数据在ssms工具中执行可能会因为脚本过大无法执行，可以使用sqlcmd工具执行脚本导入，具体方法可以百度一下。当然还有其他方法就是只导出表结构然后通过“导出数据\导入数据”的方法同步数据。

注意：如果使用“导出数据\导入数据”的方法同步数据，注意勾选“启用标示插入”

7.其它

1.如果存在alwayson记得将新的数据库加入到可用性数据库组中。

2.将新的数据库加入到备份作业中。

3.对比新旧两个数据库的表数量是否相同。

4.配置登入用户新的数据库权限。

总结

内存表是2014新引入的功能所以对于新功能的第一个版本使用要比较慎重，特别是在线上环境。虽然在上线之前做过测试，但是显然备份这块的测试往往比较容易被忽略因为没有线上的这种环境。好在是这次影响的是一个新上的项目数据量和并发都很小且允许节假日停机维护；如果是非常大的系统对于需要导入导出数据肯定是非常头疼的事情关键还得看允许停机的时长。因为自己在生产环境踩了坑，写这篇文章希望后面的人可以避免踩坑。

备注：内存表在2014版本的alwayson中无法同步到辅助副本，这就导致了它的作用大打折扣，2016版本可以同步到辅助副本，建议有条件的直接上2016。

本文转自pursuer.chen(陈敏华)博客园博客，原文链接：http://www.cnblogs.com/chenmh/p/6958385.html，如需转载请自行联系原作者

码农公寓

介绍