Summary: Use the sys.dm_server_services DMV to quickly audit SQL Server service accounts, startup types, cluster status, and Instant File Initialization (IFI) settings.
Retrieve SQL Server Service Details and Configurations
Ever wondered about the nitty-gritty details of your SQL Server services? Instead of opening the Windows Services console (services.msc) or Configuration Manager, you can pull a comprehensive overview directly from a query window.
SQL Server Service Audit Script
This query provides a quick overview of essential information for each service associated with your current SQL Server instance.
-- Audit SQL Server services and configuration
SELECT
servicename AS [Service Name],
process_id AS [PID],
startup_type_desc AS [Startup Type],
status_desc AS [Current Status],
last_startup_time AS [Last Start Time],
service_account AS [Account Name],
is_clustered AS [Is Clustered?],
cluster_nodename AS [Active Node],
[filename] AS [Binary Path],
instant_file_initialization_enabled AS [IFI Enabled]
FROM sys.dm_server_services WITH (NOLOCK)
OPTION (RECOMPILE);
Key Insights from this Query
- Service Account: Quickly verify if your services are running under the correct least-privileged service accounts.
- Instant File Initialization (IFI): Ensure IFI is enabled to speed up data file allocations and restores (SQL Server 2016+).
- Startup Type: Identify services that might fail to start after a reboot because they are set to 'Manual'.
- Cluster Details: Confirm which node in a Failover Cluster Instance (FCI) is currently hosting the service.
For more in-depth information regarding the columns in this DMV, refer to the official Microsoft documentation.
Performing a Health Check? Checking your service accounts and IFI status is a foundational step in any SQL Server performance audit!
