Monitoring Logs in Azure SQL Database

Why Collect Logs?

Logs provide insight into database health, security events, and query performance. Azure SQL offers several log sources that you can query, archive, and analyze.

Available Log Types

Log TypeDescriptionRetention
SQL AuditingCaptures database and server level activitiesUp to 2 years
Diagnostic SettingsWrites activity, errors, and slow query logs to Log Analytics / StorageUnlimited (depends on storage)
Extended EventsFineā€‘grained event collection for performance troubleshootingCustom (typically days)
Automatic Tuning LogsTracks recommended index actions and forceā€‘applied actions30 days

Querying Logs with Tā€‘SQL

Use the builtā€‘in views and functions to pull log data directly from the database.

-- Example: Retrieve recent audit events
SELECT 
    event_time,
    action_id,
    succeeded,
    session_server_principal_name,
    database_name,
    statement
FROM sys.dm_audit_actions
WHERE event_time > DATEADD(hour, -24, GETUTCDATE())
ORDER BY event_time DESC;

Extended Events can be queried via the sys.dm_xe_sessions and related DMVs.

-- Example: List recent deadlock events
SELECT 
    DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), SYSDATETIMEOFFSET()), event_data.value('(event/@timestamp)[1]', 'datetime2')) AS EventTime,
    event_data.value('(event/data/value)[1]', 'nvarchar(max)') AS DeadlockGraph
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report';

Archiving Logs to Azure Storage

Configure Diagnostic Settings to send logs to a storage account, Event Hub, or Log Analytics workspace.

az monitor diagnostic-settings create \
  --name sqlLogArchive \
  --resource /subscriptions/{subId}/resourceGroups/{rg}/providers/Microsoft.Sql/servers/{server} \
  --storage-account {storageAccountId} \
  --logs '[{"category":"SQLSecurityAuditEvents","enabled":true}]';

Once archived, you can use Azure Data Explorer or Power BI to visualize log trends.

Setting Up Alerts Based on Log Events

Create alert rules in Azure Monitor that trigger on specific log criteria.

az monitor metrics alert create \
  --name "HighAuditFailures" \
  --resource-group {rg} \
  --scopes /subscriptions/{subId}/resourceGroups/{rg}/providers/Microsoft.Sql/servers/{server} \
  --condition "count of audit_failure where EventTime > ago(5m) >= 5" \
  --action-group {actionGroupId};

Combine with Azure Functions for automated remediation.

Best Practices