前言

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

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

刪除文件

使用一個單獨(dú)非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)維護(hù)原因，數(shù)據(jù)庫可能仍然需要保留對已刪除文件的引用。 sp_filestream_force_garbage_collection (TRANSACT-SQL)將運(yùn)行 FILESTREAM 垃圾回收器刪除這些文件時，則可以安全進(jìn)行這些操作。 除非 FILESTREAM 垃圾回收器已從 FILESTREAM 容器中刪除所有文件，否則 ALTER DATABASEREMOVE FILE 操作將無法刪除 FILESTREAM 容器并返回錯誤。 建議使用以下過程刪除 FILESTREAM 容器。

1.運(yùn)行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è)已運(yùn)行（如果相關(guān)）。

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

4.運(yùn)行sp_filestream_force_garbage_collection (TRANSACT-SQL)強(qiáng)制垃圾回收器刪除不再需要此容器中的任何文件

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)境中刪除，提示由于副本的原因無法刪除。后面單獨(dú)在一個非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)什么問題也可以及時的切換回來。

步驟如下（在允許停機(jī)維護(hù)的情況下進(jìn)行）：

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

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

同時保證相關(guān)進(jì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];---保持連接操作,防止其它用戶此時進(jìn)行連接
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ù)庫所有連接同時禁止新的連接進(jìn)來
2.比如禁止登入用戶、將實(shí)例設(shè)為單用戶模式等。
*/
----1.設(shè)置數(shù)據(jù)庫脫機(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í)行可能會因?yàn)槟_本過大無法執(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)境。雖然在上線之前做過測試，但是顯然備份這塊的測試往往比較容易被忽略因?yàn)闆]有線上的這種環(huán)境。好在是這次影響的是一個新上的項目數(shù)據(jù)量和并發(fā)都很小且允許節(jié)假日停機(jī)維護(hù)；如果是非常大的系統(tǒng)對于需要導(dǎo)入導(dǎo)出數(shù)據(jù)肯定是非常頭疼的事情關(guān)鍵還得看允許停機(jī)的時長。因?yàn)樽约涸谏a(chǎn)環(huán)境踩了坑，寫這篇文章希望后面的人可以避免踩坑。

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

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