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’);