SQL Server 在AlwaysOn上使用記憶體表"踩坑"

pursuer.chen發表於2017-06-09
SQLServer記憶體

介紹

因為線上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

本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明連結。

《歡迎交流討論》

相關文章