SQL Server在AlwaysOn中使用內(nèi)存表的“踩坑”記錄

前言

最近因為線上alwayson環(huán)境的一個數(shù)據(jù)庫上使用內(nèi)存表。經(jīng)過大概一個星期監(jiān)控程序發(fā)現(xiàn)了一個非常嚴重問題這個數(shù)據(jù)庫的日志文件不會截斷，已用空間一直在增加（存在定時的每個小時的日志備份），同時內(nèi)存表數(shù)據(jù)庫文件也無法刪除，下面就介紹一下后面我的處理過程，話不多說了，來一起看看詳細的介紹吧。

數(shù)據(jù)庫：SQL Server2014 Enterprise Edition (64-bit)

刪除文件

使用一個單獨非alwayson環(huán)境的數(shù)據(jù)庫測試。

一、創(chuàng)建內(nèi)存表

---創(chuàng)建內(nèi)存表文件組
ALTER DATABASE [test] ADD FILEGROUP [test_ag] CONTAINS MEMORY_OPTIMIZED_DATA 
GO 
----創(chuàng)建內(nèi)存表數(shù)據(jù)庫文件
ALTER DATABASE [test] 
ADD FILE 
( 
 NAME = 'test_memory', 
 FILENAME ='D:\database\memory' 
) 
TO FILEGROUP [test_ag]; 
GO

二、刪除內(nèi)存表數(shù)據(jù)庫文件

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

備注：此時還未創(chuàng)建表，創(chuàng)建完后數(shù)據(jù)庫文件執(zhí)行刪除就無法刪除，接下來試試在線文檔的刪除方法方法

三、官方相關(guān)的刪除方法

即使已使用“DBCC SHRINKFILE”操作清空 FILESTREAM 容器，但出于各種系統(tǒng)維護原因，數(shù)據(jù)庫可能仍然需要保留對已刪除文件的引用。 sp_filestream_force_garbage_collection (TRANSACT-SQL)將運行 FILESTREAM 垃圾回收器刪除這些文件時，則可以安全進行這些操作。 除非 FILESTREAM 垃圾回收器已從 FILESTREAM 容器中刪除所有文件，否則 ALTER DATABASEREMOVE FILE 操作將無法刪除 FILESTREAM 容器并返回錯誤。 建議使用以下過程刪除 FILESTREAM 容器。

1.運行DBCC SHRINKFILE (TRANSACT-SQL)帶有 EMPTYFILE 選項以將此容器的活動內(nèi)容移動到其他容器

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 恢復(fù)模型中執(zhí)行日志備份。

3.確保復(fù)制日志讀取器作業(yè)已運行（如果相關(guān)）。

通過log_reuse_wait_desc的狀態(tài)可以看到當(dāng)前數(shù)據(jù)庫已經(jīng)無需日志備份，當(dāng)然我已經(jīng)執(zhí)行過日志備份。

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.執(zhí)行帶有 REMOVE FILE 選項的 ALTER DATABASE，以刪除此容器。

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

還是無法刪除?。?！

四、問題分析

一開始是在alwayson的環(huán)境中刪除，提示由于副本的原因無法刪除。后面單獨在一個非alwayson的環(huán)境下的數(shù)據(jù)庫測試同樣是無法刪除，起初以為是創(chuàng)建了內(nèi)存表的原因后面測試僅僅創(chuàng)建文件組和文件然后來刪除文件同樣是無法刪除，個人猜測有可能是buffer的緣故；在buffer中一直存在內(nèi)存表相關(guān)的文件存在，通過執(zhí)行DBCC DROPCLEANBUFFERS命令也無法清空buffer中的內(nèi)存表對象。使盡渾身解數(shù)還是無法將它刪除掉，最后只能投降了?。。【€上環(huán)境等不下去；只能使用最不愿使用的生成表結(jié)構(gòu)導(dǎo)出數(shù)據(jù)的辦法來重建新的數(shù)據(jù)庫。

生成腳本重建數(shù)據(jù)庫

創(chuàng)建一個新的數(shù)據(jù)庫同時保證當(dāng)前數(shù)據(jù)庫可用（重命名當(dāng)前的數(shù)據(jù)庫，新創(chuàng)建的數(shù)據(jù)庫使用之前的名稱這樣可以保證應(yīng)用程序那邊不需要改變），這樣如果出現(xiàn)什么問題也可以及時的切換回來。

步驟如下（在允許停機維護的情況下進行）：

1.禁用所有相關(guān)作業(yè)

2禁用應(yīng)用程序登入用戶

同時保證相關(guān)進程事務(wù)都已完成。

ALTER LOGIN [test] DISABLE
GO

USE [master]
GO
ALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;--將數(shù)據(jù)庫設(shè)置成單用戶并回滾當(dāng)前連接

USE [test];---保持連接操作,防止其它用戶此時進行連接
GO

3.執(zhí)行checkpoint刷新所有臟頁

CHECKPOINT

---返回當(dāng)前buffer中每個數(shù)據(jù)庫所占的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

---將數(shù)據(jù)庫選項改成多用戶訪問
ALTER DATABASE [test] 
SET MULTI_USER;

4.生成數(shù)據(jù)庫腳本

5.重命名舊的數(shù)據(jù)庫

注意：如果數(shù)據(jù)庫是在alwayson中，需要先從可用性數(shù)據(jù)庫中刪除，否則無法重命名數(shù)據(jù)庫。

/*
1.斷開數(shù)據(jù)庫所有連接同時禁止新的連接進來
2.比如禁止登入用戶、將實例設(shè)為單用戶模式等。
*/
----1.設(shè)置數(shù)據(jù)庫脫機
USE [master] 
ALTER DATABASE [test] SET OFFLINE WITH ROLLBACK IMMEDIATE;

----2.手動修改數(shù)據(jù)庫物理文件名，例如將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.設(shè)置數(shù)據(jù)庫在線
USE [master] 
ALTER DATABASE [test] SET ONLINE


----5.修改數(shù)據(jù)庫邏輯文件名

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.重命名數(shù)據(jù)庫
USE [master] 
EXEC sp_renamedb N'test', N'test_old'; 

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

6.創(chuàng)建新的數(shù)據(jù)庫同時導(dǎo)入腳本到新的數(shù)據(jù)庫

如果同時導(dǎo)出表結(jié)構(gòu)和數(shù)據(jù)在ssms工具中執(zhí)行可能會因為腳本過大無法執(zhí)行，可以使用sqlcmd工具執(zhí)行腳本導(dǎo)入，具體方法可以百度一下。當(dāng)然還有其他方法就是只導(dǎo)出表結(jié)構(gòu)然后通過“導(dǎo)出數(shù)據(jù)\導(dǎo)入數(shù)據(jù)”的方法同步數(shù)據(jù)。

注意：如果使用“導(dǎo)出數(shù)據(jù)\導(dǎo)入數(shù)據(jù)”的方法同步數(shù)據(jù)，注意勾選“啟用標(biāo)示插入”

7.其它

      1.如果存在alwayson記得將新的數(shù)據(jù)庫加入到可用性數(shù)據(jù)庫組中。

      2.將新的數(shù)據(jù)庫加入到備份作業(yè)中。

      3.對比新舊兩個數(shù)據(jù)庫的表數(shù)量是否相同。

      4.配置登入用戶新的數(shù)據(jù)庫權(quán)限。

總結(jié)

內(nèi)存表是2014新引入的功能所以對于新功能的第一個版本使用要比較慎重，特別是在線上環(huán)境。雖然在上線之前做過測試，但是顯然備份這塊的測試往往比較容易被忽略因為沒有線上的這種環(huán)境。好在是這次影響的是一個新上的項目數(shù)據(jù)量和并發(fā)都很小且允許節(jié)假日停機維護；如果是非常大的系統(tǒng)對于需要導(dǎo)入導(dǎo)出數(shù)據(jù)肯定是非常頭疼的事情關(guān)鍵還得看允許停機的時長。因為自己在生產(chǎn)環(huán)境踩了坑，寫這篇文章希望后面的人可以避免踩坑。

備注：內(nèi)存表在2014版本的alwayson中無法同步到輔助副本，這就導(dǎo)致了它的作用大打折扣，2016版本可以同步到輔助副本，建議有條件的直接上2016。

好了，以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。