SQL Server在AlwaysOn中用内存表的"踩坑" SQL Server在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

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

USE [test]
GO 
EXEC sp_filestream_force_garbage_collection @dbname = N'test' @filename = N' test_memory '; 

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

ALTER LOGIN [test] DISABLE
GO

USE [master]
GO
ALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;--将数据库设置成单用户并回滚当前连接

USE [test];---保持连接操作,防止其它用户此时进行连接
GO

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;

/*
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');