Documentation Grafana Cloud Observe and act Monitor infrastructure Database Observability Troubleshoot SQL Server

Troubleshoot SQL Server

Identify and resolve issues from each step of the SQL Server set up guide. Use these checks to verify your database configuration and confirm that Grafana Alloy is wired correctly. These steps do not repeat setup. If a setting is incorrect, refer back to the corresponding step in the set up guide to apply the change.

What you’ll achieve

In this article, you:

• Validate SQL Server monitoring login privileges and database access.
• Confirm Query Store is enabled and populated on every monitored database.
• Troubleshoot Alloy configuration for logs and metrics forwarding.

Before you begin

Make sure you can connect to your SQL Server instance with the db-o11y login and have sufficient privileges to run validation queries.

Verify monitoring login privileges and Query Store access

Connect with the db-o11y credentials, not the administrator, against a database you intend to monitor, then confirm the effective privileges:

SQL Copy

-- Query Store is readable on the connected database. Expect one row with actual_state_desc = READ_WRITE.
SELECT DB_NAME() AS database_name, actual_state_desc FROM sys.database_query_store_options;

-- Databases that schema details can reach. Expect every database you intend to monitor.
SELECT name FROM sys.databases WHERE HAS_DBACCESS(name) = 1;

-- Effective permission to read schema metadata on the current database. Expect 1.
SELECT HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'VIEW DEFINITION') AS can_view_definition;

-- Query Store permission on the current database. Expect at least one value to be 1.
SELECT
  HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'VIEW DATABASE STATE') AS can_view_database_state,
  HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'VIEW DATABASE PERFORMANCE STATE') AS can_view_database_performance_state;

-- Server DMV permission. Expect at least one value to be 1.
SELECT
  HAS_PERMS_BY_NAME(NULL, NULL, 'VIEW SERVER STATE') AS can_view_server_state,
  HAS_PERMS_BY_NAME(NULL, NULL, 'VIEW SERVER PERFORMANCE STATE') AS can_view_server_performance_state;

If the first query returns no rows or an error, can_view_definition returns 0, or both database-state values return 0, the grants aren’t effective on this database. Apply the database-level grants in each affected database, as described in the create-user step for your platform.

If both server-state values return 0, Alloy can’t collect server metrics or query samples from other sessions.

On Azure SQL Database, db-o11y is a contained database user, so server-level permissions don’t apply. Connect to the user database you monitor and verify CONNECT, VIEW DEFINITION, and VIEW DATABASE STATE as described in Set up Azure SQL Database.

Confirm Query Store is enabled

Query Store is a per-database setting and is off by default on databases created before SQL Server 2022. Confirm that actual_state_desc is READ_WRITE on each database you monitor:

SQL Copy

USE [<DB_NAME>];
SELECT actual_state_desc FROM sys.database_query_store_options;

If actual_state_desc returns OFF or READ_ONLY, enable Query Store in READ_WRITE mode:

SQL Copy

ALTER DATABASE [<DB_NAME>] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

On Azure SQL Database, run ALTER DATABASE CURRENT SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE); while connected to the target user database.

Confirm Query Store is populated

Confirm that Query Store has captured runtime statistics on at least one monitored database:

SQL Copy

USE [<DB_NAME>];
SELECT TOP 1 1 FROM sys.query_store_runtime_stats;

• If Query Store is enabled (READ_WRITE) but returns no rows, confirm that the monitored database is receiving query traffic and wait for the current Query Store runtime statistics interval to flush.
• Confirm that exclude_databases or include_databases in your database_observability.sql_server block doesn’t exclude the database receiving traffic.

Confirm statement metrics are collected

Verify that Alloy is collecting statement execution metrics from Query Store for the connected database:

• Confirm that the database= parameter in data_source_name points to a user database where Query Store is READ_WRITE, not a system database such as master. Query Store metrics are collected only from the database named in the connection string.
• Confirm that query_metrics isn’t listed in disable_collectors on the database_observability.sql_server component.
• Confirm that discovery.relabel and loki.relabel apply matching instance labels and don’t overwrite server_id. For relabeling checks, refer to Validate relabeling rules consistency.

Troubleshoot Alloy configuration

Validate component wiring, labels, scrape targets, and write endpoints. For guidance on exporters, components, relabeling, scrape jobs, and remote writes, refer to Troubleshoot Alloy components.

Next steps

For setup instructions, refer to Set up SQL Server.