前言
最近因?yàn)榫€上sql/283939.html">alwayson環(huán)境的一個(gè)數(shù)據(jù)庫上使用內(nèi)存表。經(jīng)過大概一個(gè)星期監(jiān)控程序發(fā)現(xiàn)了一個(gè)非常嚴(yán)重問題這個(gè)數(shù)據(jù)庫的日志文件不會(huì)截?cái)?,已用空間一直在增加(存在定時(shí)的每個(gè)小時(shí)的日志備份),同時(shí)內(nèi)存表數(shù)據(jù)庫文件也無法刪除,下面就介紹一下后面我的處理過程,話不多說了,來一起看看詳細(xì)的介紹吧。
數(shù)據(jù)庫:SQL Server2014 Enterprise Edition (64-bit)
刪除文件
使用一個(gè)單獨(dú)非alwayson環(huán)境的數(shù)據(jù)庫測(cè)試。
一、創(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]GOALTER DATABASE [test] REMOVE FILE [test_memory]GO

備注:此時(shí)還未創(chuàng)建表,創(chuàng)建完后數(shù)據(jù)庫文件執(zhí)行刪除就無法刪除,接下來試試在線文檔的刪除方法方法
三、官方相關(guān)的刪除方法
即使已使用“DBCC SHRINKFILE”操作清空 FILESTREAM 容器,但出于各種系統(tǒng)維護(hù)原因,數(shù)據(jù)庫可能仍然需要保留對(duì)已刪除文件的引用。 sp_filestream_force_garbage_collection (TRANSACT-SQL)將運(yùn)行 FILESTREAM 垃圾回收器刪除這些文件時(shí),則可以安全進(jìn)行這些操作。 除非 FILESTREAM 垃圾回收器已從 FILESTREAM 容器中刪除所有文件,否則 ALTER DATABASEREMOVE FILE 操作將無法刪除 FILESTREAM 容器并返回錯(cuò)誤。 建議使用以下過程刪除 FILESTREAM 容器。
1.運(yùn)行DBCC SHRINKFILE (TRANSACT-SQL)帶有 EMPTYFILE 選項(xiàng)以將此容器的活動(dòng)內(nèi)容移動(dòng)到其他容器
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 選項(xiàng)的 ALTER DATABASE,以刪除此容器。
USE [test]GOALTER DATABASE [test] REMOVE FILE [test_memory]GO

還是無法刪除?。?!
四、問題分析
一開始是在alwayson的環(huán)境中刪除,提示由于副本的原因無法刪除。后面單獨(dú)在一個(gè)非alwayson的環(huán)境下的數(shù)據(jù)庫測(cè)試同樣是無法刪除,起初以為是創(chuàng)建了內(nèi)存表的原因后面測(cè)試僅僅創(chuàng)建文件組和文件然后來刪除文件同樣是無法刪除,個(gè)人猜測(cè)有可能是buffer的緣故;在buffer中一直存在內(nèi)存表相關(guān)的文件存在,通過執(zhí)行DBCC DROPCLEANBUFFERS命令也無法清空buffer中的內(nèi)存表對(duì)象。使盡渾身解數(shù)還是無法將它刪除掉,最后只能投降了?。。【€上環(huán)境等不下去;只能使用最不愿使用的生成表結(jié)構(gòu)導(dǎo)出數(shù)據(jù)的辦法來重建新的數(shù)據(jù)庫。
生成腳本重建數(shù)據(jù)庫
創(chuàng)建一個(gè)新的數(shù)據(jù)庫同時(shí)保證當(dāng)前數(shù)據(jù)庫可用(重命名當(dāng)前的數(shù)據(jù)庫,新創(chuàng)建的數(shù)據(jù)庫使用之前的名稱這樣可以保證應(yīng)用程序那邊不需要改變),這樣如果出現(xiàn)什么問題也可以及時(shí)的切換回來。
步驟如下(在允許停機(jī)維護(hù)的情況下進(jìn)行):
1.禁用所有相關(guān)作業(yè)
2禁用應(yīng)用程序登入用戶
同時(shí)保證相關(guān)進(jìn)程事務(wù)都已完成。
ALTER LOGIN [test] DISABLEGOUSE [master]GOALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;--將數(shù)據(jù)庫設(shè)置成單用戶并回滾當(dāng)前連接USE [test];---保持連接操作,防止其它用戶此時(shí)進(jìn)行連接GO
3.執(zhí)行checkpoint刷新所有臟頁
CHECKPOINT---返回當(dāng)前buffer中每個(gè)數(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ù)庫選項(xiàng)改成多用戶訪問ALTER DATABASE [test] SET MULTI_USER;
4.生成數(shù)據(jù)庫腳本




5.重命名舊的數(shù)據(jù)庫
注意:如果數(shù)據(jù)庫是在alwayson中,需要先從可用性數(shù)據(jù)庫中刪除,否則無法重命名數(shù)據(jù)庫。
/*1.斷開數(shù)據(jù)庫所有連接同時(shí)禁止新的連接進(jìn)來2.比如禁止登入用戶、將實(shí)例設(shè)為單用戶模式等。*/----1.設(shè)置數(shù)據(jù)庫脫機(jī)USE [master] ALTER DATABASE [test] SET OFFLINE WITH ROLLBACK IMMEDIATE;----2.手動(dòng)修改數(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');GOALTER 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]GOALTER DATABASE [test] MODIFY FILE (NAME=N'test', NEWNAME=N'test_old')GOUSE [test]GOALTER 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_filesWHERE database_id = DB_ID('test_old');6.創(chuàng)建新的數(shù)據(jù)庫同時(shí)導(dǎo)入腳本到新的數(shù)據(jù)庫
如果同時(shí)導(dǎo)出表結(jié)構(gòu)和數(shù)據(jù)在ssms工具中執(zhí)行可能會(huì)因?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.對(duì)比新舊兩個(gè)數(shù)據(jù)庫的表數(shù)量是否相同。
4.配置登入用戶新的數(shù)據(jù)庫權(quán)限。
總結(jié)
內(nèi)存表是2014新引入的功能所以對(duì)于新功能的第一個(gè)版本使用要比較慎重,特別是在線上環(huán)境。雖然在上線之前做過測(cè)試,但是顯然備份這塊的測(cè)試往往比較容易被忽略因?yàn)闆]有線上的這種環(huán)境。好在是這次影響的是一個(gè)新上的項(xiàng)目數(shù)據(jù)量和并發(fā)都很小且允許節(jié)假日停機(jī)維護(hù);如果是非常大的系統(tǒng)對(duì)于需要導(dǎo)入導(dǎo)出數(shù)據(jù)肯定是非常頭疼的事情關(guān)鍵還得看允許停機(jī)的時(shí)長。因?yàn)樽约涸谏a(chǎn)環(huán)境踩了坑,寫這篇文章希望后面的人可以避免踩坑。
備注:內(nèi)存表在2014版本的alwayson中無法同步到輔助副本,這就導(dǎo)致了它的作用大打折扣,2016版本可以同步到輔助副本,建議有條件的直接上2016。
好了,以上就是這篇文章的全部?jī)?nèi)容了,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,如果有疑問大家可以留言交流,謝謝大家對(duì)VeVb武林網(wǎng)的支持。
新聞熱點(diǎn)
疑難解答
圖片精選