---
title: "Troubleshoot SQL Server | Database Observability documentation"
description: "Troubleshoot SQL Server setup steps for Database Observability and validate configuration."
---

> For a curated documentation index, see [llms.txt](/llms.txt). For the complete documentation index, see [llms-full.txt](/llms-full.txt).

# 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.

> Note
> 
> The `database_observability.sql_server` Alloy component is experimental and its set up guide isn’t published yet. This page is kept in sync with that guide so it’s ready when SQL Server support is generally available.

## 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 code to clipboard](https://grafana.com/media/images/icons/icon-copy-small-2.svg) Copy

```sql
-- 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](https://grafana.com/docs/grafana-cloud/monitor-applications/database-observability/set-up/sqlserver/azure-sql-database/#verify-user-privileges).

## 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 code to clipboard](https://grafana.com/media/images/icons/icon-copy-small-2.svg) Copy

```sql
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 code to clipboard](https://grafana.com/media/images/icons/icon-copy-small-2.svg) Copy

```sql
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 code to clipboard](https://grafana.com/media/images/icons/icon-copy-small-2.svg) Copy

```sql
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](https://grafana.com/docs/grafana-cloud/monitor-applications/database-observability/troubleshoot/alloy/#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](https://grafana.com/docs/grafana-cloud/monitor-applications/database-observability/troubleshoot/alloy/).

## Next steps

For setup instructions, refer to [Set up SQL Server](https://grafana.com/docs/grafana-cloud/monitor-applications/database-observability/set-up/sqlserver/).