这篇文章主要给大家介绍了关于 SQL Server 在 AlwaysOn 中使用内存表的一些 "踩坑" 记录, 文中通过示例代码介绍的非常详细, 对大家的学习或者工作具有一定的参考学习价值, 需要的朋友们下面随着小编来一起学习学习下吧
前言
最近因为线上 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
好了, 以上就是这篇文章的全部内容了, 希望本文的内容对大家的学习或者工作具有一定的参考学习价值, 如果有疑问大家可以留言交流, 谢谢大家对 PHPERZ 的支持
来源: http://www.phperz.com/article/18/0316/352233.html