SQL Server在AlwaysOn中使用内存表的“踩坑”记录
作者:pursuer.chen 发布时间:2024-01-26 03:49:10
前言
最近因为线上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。
好了,以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对脚本之家的支持。
来源:http://www.cnblogs.com/chenmh/p/6958385.html
猜你喜欢
- 本文实例为大家分享了wxPython整点报时的具体代码,供大家参考,具体内容如下# C盘要有个wav文件,内含报时音频import wx &
- 在ASP中,如何创建DSN? 见下:<HTML><HEAD><META&n
- 目标函数编码方式本程序采用的是二进制编码精确到小数点后五位,经过计算可知对于 其编码长度为18,对于 其编码长度为15,因此每个基于的长
- 引子编程世界里只存在两种基本元素,一个是数据,一个是代码。编程世界就是在数据和代码千丝万缕的纠缠中呈现出无限的生机和活力。数据天生就是文静的
- django版本:1.4.21。一、准备工作1、新建项目和app[root@yl-web-test srv]# django-admin.p
- 导入Git项目 新建项目File–>New–>Project from Version Control–>Git从Git
- TO_DATE格式(以时间:2007-11-02 13:45:25为例)1. 日期和字符转换函数用法(to_date,to_char)sel
- 本文实例讲述了Python实现的对一个数进行因式分解操作。分享给大家供大家参考,具体如下:在数学中,我们可能会对一个数进行因式分解,如何用P
- 目录为什么要画局部放大图?程序逻辑程序实例总结这项功能的目的是为了方便使用opencv做图像标注工具。为什么要画局部放大图?在做图像数据标注
- 使用爬虫时遇到etree红线问题1.首先解释一下该问题的原因是Pycharm的解释器与所要求的解释器不匹配:解决方法很简单:将软件Pycha
- 如下所示:#待处理列表A= [1,2,3,4,5]#移动次数a = 3右移比较简单for i in range(a): A.insert(0
- 一、作业回顾1、格式化输出与%百分号以下结果中,可以正常输出“50%及格”语句是(B)A、print
- 无论是在小得可怜的免费数据库空间或是大型电子商务网站,合理的设计表结构、充分利用空间是十分必要的。这就要求我们对数据库系统的常用数据类型有充
- 网页设计中,内容组织恐怕是最至关重要、最影响设计品质的方面了。如何将信息组织到好的布局中,是一个网站的基础,并且应该在考虑外观之前就决定好。
- 前言Django附带的认证对于大多数常见情况来说已经足够了,但是如何在 Django 中使用自定义的数据表进行用户认证,有一种较为笨蛋的办法
- 1、过年的时候在手机上下载了2048玩了几天,心血来潮决定用py写一个,刚开始的时候想用QT实现,发现依赖有点大。正好看到graphics.
- 元组的结构在这一小节当中主要介绍在 python 当中元组的数据结构:typedef struct { PyObj
- 假设有表tb_sku,其表结构如下:表中大约有200w条记录,执行如下的sql 语句大约 4.36s 返回数据select count(*)
- 新年新气象,今天就用代码来制作一个 动态鞭炮 ,效果如下所示。动态鞭炮的基本原理是:将一个录制好的鞭炮视频以字符画的形式复现,基本步骤是帧采
- 在python2.x中 ,异常是这样的处理的,异常基类后面加一个逗号“ ,” 然后跟着异常类型import tracebackt