Azure SQL托管实例 - 查询存储是read_only [英] Azure SQL Managed Instance - query store is read_only

问题描述

Hello expert，

我们最近将生产数据库移至Azure SQL Managed实例。它进展顺利，没有问题。

唯一的问题是查询存储是Read_Only。当我运行查询时

它显示数据库是Read_Write

选择名称，来自sys.databases的is_read_only

但是，运行下面的查询，返回READ_ONLY

SELECT DATABASEPROPERTYEX（'Interpro'，'可更新性'）

这很奇怪，因为数据库当前正在使用并且是一个生产数据库。

I还试图备份和恢复数据库，但它仍显示为Read_only，查询存储不显示任何统计信息。

任何想法都会有很大的帮助吗？

谢谢，

Aloysius

解决方案

< blockquote>

您好，¥b $ b

查询存储在其大小达到最大限制时将其操作模式更改为read_only。请运行以下查询
以验证这是方案：

 

 SELECT actual_state_desc，desired_state_desc，
 current_storage_size_mb，   
 max_storage_size_mb，readonly_reason  
来自sys.database_query_store_options; 

如果是这种情况，请设置
新限制。

 ALTER DATABASE [QueryStoreDB]  
 SET QUERY_STORE（MAX_STORAGE_SIZE_MB = 1024）; 

希望这会有所帮助。

问候，

Alberto Morillo

SQLCoffee.com

Hello experts,

We have recently moved our production database to Azure SQL Managed instance. It went well, and no issues.

The only issue is the query store is Read_Only. When I run the query

It shows that the database is Read_Write

select name, is_read_only from sys.databases

However, running the query below, returns READ_ONLY

SELECT DATABASEPROPERTYEX ('Interpro', 'updateability')

This is strange, as the database is currently being used and is a production database.

I have also tried to backup and restore the database, however it still shows as Read_only and the query store does not show any stats.

Any thoughts would be of great help?

Thanks,

Aloysius

解决方案

Hello,

Query store changes its operation mode to read_only when its size reaches the maximum limit. Please run the following query to verify this is the scenario:

 

SELECT actual_state_desc, desired_state_desc, 
current_storage_size_mb,  
max_storage_size_mb, readonly_reason  
FROM sys.database_query_store_options;

If that is the case, please set a new limit.

ALTER DATABASE [QueryStoreDB] 
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

这篇关于Azure SQL托管实例 - 查询存储是read_only的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！