Snippet Name: AG_Dashboard_Query_Explanation
Tags:
Created Date: 2025-06-16 13:12:39
Last Modified Date: 2025-05-31 10:32:07
Dependencies:
DBA Notes:
Sample Output:
Title: AG Dashboard Query Explanation
Author: user
example the following SQL Server Query, and in two or three sentences, summarize what it is querying: `--desc: a TSQL version of Availability Groups Dashboard info
WITH AGStatus AS(
SELECT
name as AGname,
replica_server_name,
CASE WHEN (primary_replica = replica_server_name) THEN 1
ELSE '' END AS IsPrimaryServer,
secondary_role_allow_connections_desc AS ReadableSecondary,
[availability_mode] AS [Synchronous],
failover_mode_desc
FROM master.sys.availability_groups Groups
INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id
INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id
)
Select
[AGname],
[Replica_server_name],
[IsPrimaryServer],
[Synchronous],
[ReadableSecondary],
[Failover_mode_desc]
FROM AGStatus
--WHERE
--IsPrimaryServer = 1
--AND Synchronous = 1
ORDER BY
AGname ASC,
IsPrimaryServer DESC;
DECLARE @SecondaryReplicasOnly BIT;
IF OBJECT_ID('TempDB..#tmpag_availability_groups') IS NOT NULL
DROP TABLE [#tmpag_availability_groups];
SELECT *
INTO [#tmpag_availability_groups]
FROM [master].[sys].[availability_groups];
IF OBJECT_ID('TempDB..#tmpdbr_availability_replicas') IS NOT NULL
DROP TABLE [#tmpdbr_availability_replicas];
IF OBJECT_ID('TempDB..#tmpdbr_database_replica_cluster_states') IS NOT NULL
DROP TABLE [#tmpdbr_database_replica_cluster_states];
IF OBJECT_ID('TempDB..#tmpdbr_database_replica_states') IS NOT NULL
DROP TABLE [#tmpdbr_database_replica_states];
IF OBJECT_ID('TempDB..#tmpdbr_database_replica_states_primary_LCT') IS NOT NULL
DROP TABLE [#tmpdbr_database_replica_states_primary_LCT];
IF OBJECT_ID('TempDB..#tmpdbr_availability_replica_states') IS NOT NULL
DROP TABLE [#tmpdbr_availability_replica_states];
SELECT [group_id],
[replica_id],
[replica_server_name],
[availability_mode],
[availability_mode_desc]
INTO [#tmpdbr_availability_replicas]
FROM [master].[sys].[availability_replicas];
SELECT [replica_id],
[group_database_id],
[database_name],
[is_database_joined],
[is_failover_ready]
INTO [#tmpdbr_database_replica_cluster_states]
FROM [master].[sys].[dm_hadr_database_replica_cluster_states];
SELECT *
INTO [#tmpdbr_database_replica_states]
FROM [master].[sys].[dm_hadr_database_replica_states];
SELECT [replica_id],
[role],
[role_desc],
[is_local]
INTO [#tmpdbr_availability_replica_states]
FROM [master].[sys].[dm_hadr_availability_replica_states];
SELECT [ars].[role],
[drs].[database_id],
[drs].[replica_id],
[drs].[last_commit_time]
INTO [#tmpdbr_database_replica_states_primary_LCT]
FROM [#tmpdbr_database_replica_states] AS [drs]
LEFT JOIN [#tmpdbr_availability_replica_states] [ars] ON [drs].[replica_id] = [ars].[replica_id]
WHERE [ars].[role] = 1;
SELECT [AG].[name] AS [AvailabilityGroupName],
[AR].[replica_server_name] AS [AvailabilityReplicaServerName],
[dbcs].[database_name] AS [AvailabilityDatabaseName],
ISNULL([dbcs].[is_failover_ready],0) AS [IsFailoverReady],
ISNULL([arstates].[role_desc],3) AS [ReplicaRole],
[AR].[availability_mode_desc] AS [AvailabilityMode],
CASE [dbcs].[is_failover_ready]
WHEN 1
THEN 0
ELSE ISNULL(DATEDIFF([ss],[dbr].[last_commit_time],[dbrp].[last_commit_time]),0)
END AS [EstimatedDataLoss_(Seconds)],
ISNULL(CASE [dbr].[redo_rate]
WHEN 0
THEN-1
ELSE CAST([dbr].[redo_queue_size] AS FLOAT) / [dbr].[redo_rate]
END,-1) AS [EstimatedRecoveryTime_(Seconds)],
ISNULL([dbr].[is_suspended],0) AS [IsSuspended],
ISNULL([dbr].[suspend_reason_desc],'-') AS [SuspendReason],
ISNULL([dbr].[synchronization_state_desc],0) AS [SynchronizationState],
ISNULL([dbr].[last_received_time],0) AS [LastReceivedTime],
ISNULL([dbr].[last_redone_time],0) AS [LastRedoneTime],
ISNULL([dbr].[last_sent_time],0) AS [LastSentTime],
ISNULL([dbr].[log_send_queue_size],-1) AS [LogSendQueueSize],
ISNULL([dbr].[log_send_rate],-1) AS [LogSendRate_KB/S],
ISNULL([dbr].[redo_queue_size],-1) AS [RedoQueueSize_KB],
ISNULL([dbr].[redo_rate],-1) AS [RedoRate_KB/S],
ISNULL(CASE [dbr].[log_send_rate]
WHEN 0
THEN-1
ELSE CAST([dbr].[log_send_queue_size] AS FLOAT) / [dbr].[log_send_rate]
END,-1) AS [SynchronizationPerformance],
ISNULL([dbr].[filestream_send_rate],-1) AS [FileStreamSendRate],
ISNULL([dbcs].[is_database_joined],0) AS [IsJoined],
[arstates].[is_local] AS [IsLocal],
ISNULL([dbr].[last_commit_lsn],0) AS [LastCommitLSN],
ISNULL([dbr].[last_commit_time],0) AS [LastCommitTime],
ISNULL([dbr].[last_hardened_lsn],0) AS [LastHardenedLSN],
ISNULL([dbr].[last_hardened_time],0) AS [LastHardenedTime],
ISNULL([dbr].[last_received_lsn],0) AS [LastReceivedLSN],
ISNULL([dbr].[last_redone_lsn],0) AS [LastRedoneLSN]
FROM [#tmpag_availability_groups] AS [AG]
INNER JOIN [#tmpdbr_availability_replicas] AS [AR] ON [AR].[group_id] = [AG].[group_id]
INNER JOIN [#tmpdbr_database_replica_cluster_states] AS [dbcs] ON [dbcs].[replica_id] = [AR].[replica_id]
LEFT OUTER JOIN [#tmpdbr_database_replica_states] AS [dbr] ON [dbcs].[replica_id] = [dbr].[replica_id]
AND [dbcs].[group_database_id] = [dbr].[group_database_id]
LEFT OUTER JOIN [#tmpdbr_database_replica_states_primary_LCT] AS [dbrp] ON [dbr].[database_id] = [dbrp].[database_id]
INNER JOIN [#tmpdbr_availability_replica_states] AS [arstates] ON [arstates].[replica_id] = [AR].[replica_id]
ORDER BY [AvailabilityReplicaServerName] ASC,
[AvailabilityDatabaseName] ASC;
/*********************/
Author: ChatGPT
This query retrieves detailed information about SQL Server **Always On Availability Groups**, including replica roles, synchronization states, failover readiness, and estimated data loss. It first defines a common table expression (`AGStatus`) to extract primary and secondary replica details, then stores availability group metadata into temporary tables. Finally, it joins these temporary tables to generate a report on **replica synchronization status, failover conditions, estimated data loss, and recovery time** for each availability database. The query effectively mimics the **Availability Groups Dashboard** in SQL Server Management Studio.
Author: user
lets change the expected output, so that instead of summarizing the structure, liek using common table expressions or temp tables, summarize the data it woudl output and it's advantages
Author: ChatGPT
This query outputs a **detailed status report** of SQL Server **Always On Availability Groups**, listing each availability group, its replicas, and the associated databases. The results include critical metrics such as **failover readiness, synchronization state, estimated data loss (in seconds), recovery time, log send/redo queue sizes, and last commit timestamps**. These details help **DBAs quickly assess replication health, identify synchronization lags, and determine whether secondary replicas are ready for failover**. By providing **insight into performance bottlenecks and potential data loss**, the output is highly valuable for proactive monitoring and disaster recovery planning.
Author: user
that was excellent. please repeat for each of the various queries i will continue to paste in this session
Author: ChatGPT
Got it! Paste in your next query, and I'll summarize the expected output and its advantages just like before.
Author: user
declare @xel_path varchar(1024);
declare @utc_adjustment int = datediff(hour, getutcdate(), getdate());
-------------------------------------------------------------------------------
------------------- target event_file path retrieval --------------------------
-------------------------------------------------------------------------------
;with target_data_cte as
(
select
target_data =
convert(xml, target_data)
from sys.dm_xe_sessions s
inner join sys.dm_xe_session_targets st
on s.address = st.event_session_address
where s.name = 'alwayson_health'
and st.target_name = 'event_file'
),
full_path_cte as
(
select
full_path =
target_data.value('(EventFileTarget/File/@name)[1]', 'varchar(1024)')
from target_data_cte
)
select
@xel_path =
left(full_path, len(full_path) - charindex('\', reverse(full_path))) +
'\AlwaysOn_health*.xel'
from full_path_cte;
-------------------------------------------------------------------------------
------------------- replica state change events -------------------------------
-------------------------------------------------------------------------------
;with state_change_data as
(
select
object_name,
event_data =
convert(xml, event_data)
from sys.fn_xe_file_target_read_file(@xel_path, null, null, null)
)
select
object_name,
event_timestamp =
dateadd(hour, @utc_adjustment, event_data.value('(event/@timestamp)[1]', 'datetime')),
ag_name =
event_data.value('(event/data[@name = "availability_group_name"]/value)[1]', 'varchar(64)'),
previous_state =
event_data.value('(event/data[@name = "previous_state"]/text)[1]', 'varchar(64)'),
current_state =
event_data.value('(event/data[@name = "current_state"]/text)[1]', 'varchar(64)'),
ar.[replica_server_name],
CASE WHEN event_data.value('(event/data[@name = "current_state"]/text)[1]', 'varchar(64)') = 'PRIMARY_NORMAL' THEN ar.[replica_server_name] ELSE '' END AS PrimaryServerName,
CASE WHEN event_data.value('(event/data[@name = "current_state"]/text)[1]', 'varchar(64)') = 'SECONDARY_NORMAL' THEN ar.[replica_server_name] ELSE '' END AS SecondaryServerName
from state_change_data
JOIN sys.availability_replicas ar
ON ar.replica_id = event_data.value('(event/data/value)[5]', 'VARCHAR(255)')
where object_name = 'availability_replica_state_change'
order by event_timestamp desc;
Author: ChatGPT
This query retrieves **state change events for SQL Server Always On Availability Groups** by analyzing Extended Events (`alwayson_health`) logs. The output lists **availability group name, replica server name, previous state, current state, and timestamps** of state transitions. It identifies **which replica is the primary or secondary at any given time**, helping DBAs **track failovers, diagnose unexpected state changes, and monitor AG health over time**. The **time zone adjustment** ensures accurate reporting for local time analysis, making it easier to correlate with other monitoring data.
Author: user
DECLARE @FileName NVARCHAR(4000)
SELECT @FileName = target_data.value('(EventFileTarget/File/@name)[1]', 'nvarchar(4000)')
FROM (
SELECT CAST(target_data AS XML) target_data
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t
ON s.address = t.event_session_address
WHERE s.name = N'AlwaysOn_health'
) ft;
WITH base
AS (
SELECT XEData.value('(event/@timestamp)[1]', 'datetime2(3)') AS event_timestamp
,XEData.value('(event/data/text)[1]', 'VARCHAR(255)') AS previous_state
,XEData.value('(event/data/text)[2]', 'VARCHAR(255)') AS current_state
,ar.replica_server_name
FROM (
SELECT CAST(event_data AS XML) XEData
,*
FROM sys.fn_xe_file_target_read_file(@FileName, NULL, NULL, NULL)
WHERE object_name = 'availability_replica_state_change'
) event_data
JOIN sys.availability_replicas ar
ON ar.replica_id = XEData.value('(event/data/value)[5]', 'VARCHAR(255)')
)
SELECT DATEADD(HOUR, DATEDIFF(HOUR, GETUTCDATE(), GETDATE()), event_timestamp) AS event_timestamp
,previous_state
,current_state
,replica_server_name,
CASE WHEN current_state = 'PRIMARY_NORMAL' THEN replica_server_name ELSE '' END AS PrimaryServerName,
CASE WHEN current_state = 'SECONDARY_NORMAL' THEN replica_server_name ELSE '' END AS SecondaryServerName
FROM base
ORDER BY event_timestamp DESC;
Author: ChatGPT
This query extracts **state change events for SQL Server Always On Availability Groups** from the **AlwaysOn_health Extended Events session**, listing **previous and current replica states, timestamps, and server names**. The output helps **track failovers, identify when a replica transitions between primary and secondary roles, and diagnose unexpected availability group changes**. By adjusting timestamps to local time, it ensures easier correlation with other logs and monitoring tools, making it valuable for troubleshooting and proactive AG health monitoring.
Author: user
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Read-only routing url generation script.
-- Connect to each replica in your AlwaysOn cluster and run this script to get the read_only_routing_url for the replica.
-- Then set this to the read_only_routing_url for the availability group replica =>
-- alter availability group MyAvailabilityGroup modify replica on N'ThisReplica' with (secondary_role(read_only_routing_url=N'<url>'))
print 'Read-only-routing url script v.2012.1.24.1'
print 'This SQL Server instance version is &#91;' + cast(serverproperty('ProductVersion') as varchar(256)) + ']'
if (ServerProperty('IsClustered') = 1)
begin
print 'This SQL Server instance is a clustered SQL Server instance.'
end
else
begin
print 'This SQL Server instance is a standard (not clustered) SQL Server instance.'
end
if (ServerProperty('IsHadrEnabled') = 1)
begin
print 'This SQL Server instance is enabled for AlwaysOn.'
end
else
begin
print 'This SQL Server instance is NOT enabled for AlwaysOn.'
end
-- Detect SQL Azure instance.
declare @is_sql_azure bit
set @is_sql_azure = 0
begin try
set @is_sql_azure = 1
exec('declare @i int set @i = sql_connection_mode()')
print 'This SQL Server instance is a Sql Azure instance.'
end try
begin catch
set @is_sql_azure = 0
print 'This SQL Server instance is NOT a Sql Azure instance.'
end catch
-- Check that this is SQL 11 or later, otherwise fail fast.
if (@@microsoftversion / 0x01000000 < 11 or @is_sql_azure > 0)
begin
print 'This SQL Server instance does not support read-only routing, exiting script.'
end
else
begin -- if server supports read-only routing
-- Fetch the dedicated admin connection (dac) port.
-- Normally it's always port 1434, but to be safe here we fetch it from the instance.
-- We use this later to exclude the admin port from read_only_routing_url.
declare @dac_port int
declare @reg_value varchar(255)
exec xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp',
N'TcpDynamicPorts',
@reg_value output
set @dac_port = cast(@reg_value as int)
print 'This SQL Server instance DAC (dedicated admin) port is ' + cast(@dac_port as varchar(255))
if (@dac_port = 0)
begin
print 'Note a DAC port of zero means the dedicated admin port is not enabled.'
end
-- Fetch ListenOnAllIPs value.
-- If set to 1, this means the instance is listening to all IP addresses.
-- If set to 0, this means the instance is listening to specific IP addresses.
declare @listen_all int
exec xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\Tcp',
N'ListenOnAllIPs',
@listen_all output
if (@listen_all = 1)
begin
print 'This SQL Server instance is listening to all IP addresses (default mode).'
end
else
begin
print 'This SQL Server instance is listening to specific IP addresses (ListenOnAllIPs is disabled).'
end
-- Check for dynamic port configuration, not recommended with read-only routing.
declare @tcp_dynamic_ports varchar(255)
exec xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\Tcp\IPAll',
N'TcpDynamicPorts',
@tcp_dynamic_ports output
if (@tcp_dynamic_ports = '0')
begin
print 'This SQL Server instance is listening on a dynamic tcp port, this is NOT A RECOMMENDED CONFIGURATION when using read-only routing, because the instance port can change each time the instance is restarted.'
end
else
begin
print 'This SQL Server instance is listening on fixed tcp port(s) (it is not configured for dynamic ports), this is a recommended configuration when using read-only routing.'
end
-- Calculate the server domain and instance FQDN.
-- We use @server_domain later to build the FQDN to the clustered instance.
declare @instance_fqdn varchar(255)
declare @server_domain varchar(255)
-- Get the instance FQDN using the xp_getnetname API
-- Note all cluster nodes must be in same domain, so this works for calculating cluster FQDN.
set @instance_fqdn = ''
exec xp_getnetname @instance_fqdn output, 1
-- Remove embedded null character at end if found.
declare @terminator int
set @terminator = charindex(char(0), @instance_fqdn) - 1
if (@terminator > 0)
begin
set @instance_fqdn = substring(@instance_fqdn, 1, @terminator)
end
-- Build @server_domain using @instance_fqdn.
set @server_domain = @instance_fqdn
-- Remove trailing portion to extract domain name.
set @terminator = charindex('.', @server_domain)
if (@terminator > 0)
begin
set @server_domain = substring(@server_domain, @terminator+1, datalength(@server_domain))
end
print 'This SQL Server instance resides in domain ''' + @server_domain + ''''
if (ServerProperty('IsClustered') = 1)
begin
-- Fetch machine name, which for a clustered SQL instance returns the network name of the virtual server.
-- Append @server_domain to build the FQDN.
set @instance_fqdn = cast(serverproperty('MachineName') as varchar(255)) + '.' + @server_domain
end
declare @ror_url varchar(255)
declare @instance_port int
set @ror_url = ''
-- Get first available port for instance.
select
top 1 -- Select first matching port
@instance_port = port
from sys.dm_tcp_listener_states
where
type=0 -- Type 0 = TSQL (to avoid mirroring endpoint)
and
state=0 -- State 0 is online
and
port <> @dac_port -- Avoid DAC port (admin port)
and
-- Avoid availability group listeners
ip_address not in (select ip_address from sys.availability_group_listener_ip_addresses agls)
group by port
order by port asc -- Pick first port in ascending order
-- Check if there are multiple ports and warn if this is the case.
declare @list_of_ports varchar(max)
set @list_of_ports = ''
select
@list_of_ports = @list_of_ports +
case datalength(@list_of_ports)
when 0 then cast(port as varchar(max))
else ',' + cast(port as varchar(max))
end
from sys.dm_tcp_listener_states
where
type=0 -- Type 0 = TSQL (to avoid mirroring endpoint)
and
state=0 -- State 0 is online
and
port <> @dac_port -- Avoid DAC port (admin port)
and
-- Avoid availability group listeners
ip_address not in (select ip_address from sys.availability_group_listener_ip_addresses agls)
group by port
order by port asc
print 'This SQL Server instance FQDN (Fully Qualified Domain Name) is ''' + @instance_fqdn + ''''
print 'This SQL Server instance port is ' + cast(@instance_port as varchar(10))
set @ror_url = 'tcp://' + @instance_fqdn + ':' + cast(@instance_port as varchar(10))
print '****************************************************************************************************************'
print 'The read_only_routing_url for this SQL Server instance is ''' + @ror_url + ''''
print '****************************************************************************************************************'
-- If there is more than one instance port (unusual) list them out just in case.
if (charindex(',', @list_of_ports) > 0)
begin
print 'Note there is more than one instance port, the list of available instance ports for read_only_routing_url is (' + @list_of_ports + ')'
print 'The above URL just uses the first port in the list, but you can use any of these available ports.'
end
end -- if server supports read-only routing
Author: ChatGPT
This query generates the **Read-Only Routing URL** for an Always On Availability Group replica, which is required for configuring **read-only routing in SQL Server**. The output includes **server version, clustering status, Always On enablement, network configurations (FQDN, IP settings, ports), and the final Read-Only Routing URL**. This information helps **DBAs ensure correct routing of read-only queries to secondary replicas, optimize load balancing for reporting workloads, and verify that the SQL instance is properly configured for Always On functionality.** Additionally, it warns against potential misconfigurations like dynamic ports, which can disrupt routing behavior.
Author: user
SELECT
listz.&#91;dns_name],
listz.&#91;port],
listz.&#91;ip_configuration_string_from_cluster],
addrz.&#91;ip_address],
addrz.&#91;ip_subnet_mask],
addrz.&#91;state_desc]
FROM sys.availability_group_listeners listz
INNER JOIN sys.availability_group_listener_ip_addresses addrz
ON &#91;addrz].&#91;listener_id] = &#91;listz].&#91;listener_id]
Author: ChatGPT
This query retrieves **availability group listener details**, including **DNS name, port, IP address, subnet mask, and state description**. The output provides a **comprehensive view of how Always On Availability Groups are configured for client connections**, showing **which IPs and ports are assigned to each listener**. This information is useful for **troubleshooting connectivity issues, verifying failover configurations, and ensuring proper listener setup for high availability and disaster recovery**.
Author: user
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Read-only routing url generation script.
-- Connect to each replica in your AlwaysOn cluster and run this script to get the read_only_routing_url for the replica.
-- Then set this to the read_only_routing_url for the availability group replica =>
-- alter availability group MyAvailabilityGroup modify replica on N'ThisReplica' with (secondary_role(read_only_routing_url=N'<url>'))
IF OBJECT_ID('tempdb..&#91;#AGRouting]') IS NOT NULL
DROP TABLE &#91;#AGRouting]
CREATE TABLE &#91;#AGRouting] (
&#91;RoutingID]INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
&#91;RoutingNotes] VARCHAR(4000) NULL)
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'Read-only-routing url script v.2012.1.24.1'
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'This SQL Server instance version is &#91;' + cast(serverproperty('ProductVersion') as varchar(256)) + ']'
if (ServerProperty('IsClustered') = 1)
begin
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'This SQL Server instance is a clustered SQL Server instance.'
end
else
begin
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'This SQL Server instance is a standard (not clustered) SQL Server instance.'
end
if (ServerProperty('IsHadrEnabled') = 1)
begin
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'This SQL Server instance is enabled for AlwaysOn.'
end
else
begin
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'This SQL Server instance is NOT enabled for AlwaysOn.'
end
-- Detect SQL Azure instance.
declare @is_sql_azure bit
set @is_sql_azure = 0
begin try
set @is_sql_azure = 1
exec('declare @i int set @i = sql_connection_mode()')
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'This SQL Server instance is a Sql Azure instance.'
end try
begin catch
set @is_sql_azure = 0
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'This SQL Server instance is NOT a Sql Azure instance.'
end catch
-- Check that this is SQL 11 or later, otherwise fail fast.
if (@@microsoftversion / 0x01000000 < 11 or @is_sql_azure > 0)
begin
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'This SQL Server instance does not support read-only routing, exiting script.'
end
else
begin -- if server supports read-only routing
-- Fetch the dedicated admin connection (dac) port.
-- Normally it's always port 1434, but to be safe here we fetch it from the instance.
-- We use this later to exclude the admin port from read_only_routing_url.
declare @dac_port int
declare @reg_value varchar(255)
exec xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp',
N'TcpDynamicPorts',
@reg_value output
set @dac_port = cast(@reg_value as int)
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'This SQL Server instance DAC (dedicated admin) port is ' + cast(@dac_port as varchar(255))
if (@dac_port = 0)
begin
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'Note a DAC port of zero means the dedicated admin port is not enabled.'
end
-- Fetch ListenOnAllIPs value.
-- If set to 1, this means the instance is listening to all IP addresses.
-- If set to 0, this means the instance is listening to specific IP addresses.
declare @listen_all int
exec xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\Tcp',
N'ListenOnAllIPs',
@listen_all output
if (@listen_all = 1)
begin
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'This SQL Server instance is listening to all IP addresses (default mode).'
end
else
begin
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'This SQL Server instance is listening to specific IP addresses (ListenOnAllIPs is disabled).'
end
-- Check for dynamic port configuration, not recommended with read-only routing.
declare @tcp_dynamic_ports varchar(255)
exec xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\Tcp\IPAll',
N'TcpDynamicPorts',
@tcp_dynamic_ports output
if (@tcp_dynamic_ports = '0')
begin
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'This SQL Server instance is listening on a dynamic tcp port, this is NOT A RECOMMENDED CONFIGURATION when using read-only routing, because the instance port can change each time the instance is restarted.'
end
else
begin
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'This SQL Server instance is listening on fixed tcp port(s) (it is not configured for dynamic ports), this is a recommended configuration when using read-only routing.'
end
-- Calculate the server domain and instance FQDN.
-- We use @server_domain later to build the FQDN to the clustered instance.
declare @instance_fqdn varchar(255)
declare @server_domain varchar(255)
-- Get the instance FQDN using the xp_getnetname API
-- Note all cluster nodes must be in same domain, so this works for calculating cluster FQDN.
set @instance_fqdn = ''
exec xp_getnetname @instance_fqdn output, 1
-- Remove embedded null character at end if found.
declare @terminator int
set @terminator = charindex(char(0), @instance_fqdn) - 1
if (@terminator > 0)
begin
set @instance_fqdn = substring(@instance_fqdn, 1, @terminator)
end
-- Build @server_domain using @instance_fqdn.
set @server_domain = @instance_fqdn
-- Remove trailing portion to extract domain name.
set @terminator = charindex('.', @server_domain)
if (@terminator > 0)
begin
set @server_domain = substring(@server_domain, @terminator+1, datalength(@server_domain))
end
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'This SQL Server instance resides in domain ''' + @server_domain + ''''
if (ServerProperty('IsClustered') = 1)
begin
-- Fetch machine name, which for a clustered SQL instance returns the network name of the virtual server.
-- Append @server_domain to build the FQDN.
set @instance_fqdn = cast(serverproperty('MachineName') as varchar(255)) + '.' + @server_domain
end
declare @ror_url varchar(255)
declare @instance_port int
set @ror_url = ''
-- Get first available port for instance.
select
top 1 -- Select first matching port
@instance_port = port
from sys.dm_tcp_listener_states
where
type=0 -- Type 0 = TSQL (to avoid mirroring endpoint)
and
state=0 -- State 0 is online
and
port <> @dac_port -- Avoid DAC port (admin port)
and
-- Avoid availability group listeners
ip_address not in (select ip_address from sys.availability_group_listener_ip_addresses agls)
group by port
order by port asc -- Pick first port in ascending order
-- Check if there are multiple ports and warn if this is the case.
declare @list_of_ports varchar(max)
set @list_of_ports = ''
select
@list_of_ports = @list_of_ports +
case datalength(@list_of_ports)
when 0 then cast(port as varchar(max))
else ',' + cast(port as varchar(max))
end
from sys.dm_tcp_listener_states
where
type=0 -- Type 0 = TSQL (to avoid mirroring endpoint)
and
state=0 -- State 0 is online
and
port <> @dac_port -- Avoid DAC port (admin port)
and
-- Avoid availability group listeners
ip_address not in (select ip_address from sys.availability_group_listener_ip_addresses agls)
group by port
order by port asc
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'This SQL Server instance FQDN (Fully Qualified Domain Name) is ''' + @instance_fqdn + ''''
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'This SQL Server instance port is ' + cast(@instance_port as varchar(10))
set @ror_url = 'tcp://' + @instance_fqdn + ':' + cast(@instance_port as varchar(10))
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT '****************************************************************************************************************'
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'The read_only_routing_url for this SQL Server instance is ''' + @ror_url + ''''
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT '****************************************************************************************************************'
-- If there is more than one instance port (unusual) list them out just in case.
if (charindex(',', @list_of_ports) > 0)
begin
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'Note there is more than one instance port, the list of available instance ports for read_only_routing_url is (' + @list_of_ports + ')'
INSERT INTO &#91;#AGRouting](&#91;RoutingNotes]) SELECT 'The above URL just uses the first port in the list, but you can use any of these available ports.'
end
end -- if server supports read-only routing
SELECT * FROM &#91;#AGRouting] ORDER BY RoutingID
Author: ChatGPT
This query generates a **detailed report for configuring Read-Only Routing in SQL Server Always On Availability Groups** and stores the results in a temporary table (`#AGRouting`). The output provides **server version, clustering status, Always On enablement, network settings (FQDN, IP mode, ports), and the generated Read-Only Routing URL**. By **validating port configurations, detecting dynamic ports (which are not recommended), and checking domain details**, the query helps **DBAs ensure that read-only queries are correctly routed to secondary replicas**. This structured output makes it easy to **review, troubleshoot, and configure read-only routing settings across replicas**.
Author: user
DECLARE @Domain varchar(100), @key varchar(100)
SET @key = 'SYSTEM\ControlSet001\Services\Tcpip\Parameters\'
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@key,@value_name='Domain',@value=@Domain OUTPUT
SELECT CONVERT(VARCHAR(128),SERVERPROPERTY('MachineName')) + '.'+convert(varchar(100),@Domain)
;WITH ClusterActiveNode as
(
SELECT
CONVERT(VARCHAR(128),@@SERVERNAME) AS ServerName,
CONVERT(VARCHAR(128),SERVERPROPERTY('ComputerNamePhysicalNetBIOS')) as ActiveNodeName,
Cast('Active' as varchar(10)) as Active
),
ClusterNodes AS
(
SELECT NodeName FROM &#91;sys].&#91;dm_os_cluster_nodes]
)
,CurrentNodeList
AS
(
SELECT
CONVERT(VARCHAR(128),@@SERVERNAME) AS ServerName,
&#91;b].&#91;NodeName] AS CurrentNodeName,
a.&#91;ActiveNodeName] ,
ISNULL(&#91;a].&#91;Active],'') AS NodeStatus,
CONVERT(VARCHAR(128),SERVERPROPERTY('MachineName')) + '.'+convert(varchar(100),@Domain) AS FullMachineName,
CONVERT(VARCHAR(128),&#91;b].&#91;NodeName]) + '.'+convert(varchar(100),@Domain) AS FullNodeName,
CONVERT(VARCHAR(128),&#91;ActiveNodeName]) + '.'+convert(varchar(100),@Domain) AS FullActiveName
FROM ClusterNodes AS b
LEFT JOIN ClusterActiveNode AS a
ON &#91;a].&#91;ActiveNodeName] = &#91;b].&#91;NodeName]
)
SELECT &#91;T1].*
FROM CurrentNodeList T1
Author: ChatGPT
This query retrieves **SQL Server clustering and domain information**, specifically **the active cluster node, all cluster nodes, and the fully qualified domain names (FQDN) of the server, nodes, and active node**. The output helps **DBAs identify which node is currently active in a failover cluster, verify cluster configuration, and ensure correct domain settings**. This is especially useful for **troubleshooting failovers, validating Always On Availability Group setups, and ensuring correct hostname resolution for high availability environments**.
Author: user
DECLARE @ServerName VARCHAR(128)
DECLARE @ApplicationName VARCHAR(128)
DECLARE @AppEmailGroup VARCHAR(128)
--#################################################################################################
-- Database Specific Contact Groups if they exist
--#################################################################################################
SELECT @ApplicationName = CONVERT(VARCHAR(4000),prpz.value) FROM sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'ApplicationName'
SELECT @AppEmailGroup = CONVERT(VARCHAR(4000),prpz.value) FROM sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'AppEmailGroup'
SELECT @ServerName = CONVERT(VARCHAR(128),prpz.value) FROM sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'Cname'
--#################################################################################################
-- Server Level Contact Groups if Database Level are Blank
--#################################################################################################
IF @ApplicationName IS NULL
SELECT @ApplicationName = CONVERT(VARCHAR(4000),prpz.value) FROM &#91;master].sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'ApplicationName'
IF @ApplicationName IS NULL
SELECT @AppEmailGroup = CONVERT(VARCHAR(4000),prpz.value) FROM &#91;master].sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'AppEmailGroup'
IF @ServerName IS NULL
SELECT @ServerName = CONVERT(VARCHAR(128),prpz.value) FROM &#91;master].sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'Cname'
SELECT @ServerName = ISNULL(@ServerName,CONVERT(VARCHAR(128),@@SERVERNAME))
SELECT --@ServerName AS CName,
-- @ApplicationName AS ApplicationName,
-- @AppEmailGroup AS AppEmailGroup,
CONVERT(VARCHAR(128),SERVERPROPERTY('ComputerNamePhysicalNetBIOS')) AS CurrentNodeName,
s.Colzs AS PossibleNodes
FROM(SELECT
Colzs = STUFF((SELECT ',' +NodeName
FROM sys.&#91;dm_os_cluster_nodes] AS &#91;docn]
ORDER BY NodeName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
Author: ChatGPT
This query retrieves **server-specific metadata, including application contact groups, server alias (CName), and cluster node details** using **Extended Properties** and **cluster node information**. The output includes **the server???s friendly name (if defined), associated application name, email contact group, current active cluster node, and a list of all possible cluster nodes**. This helps **DBAs manage high availability environments by ensuring correct application-to-server mappings, identifying responsible teams for database alerts, and verifying failover node configurations for SQL Server clusters**.
Author: user
DECLARE @ServerName VARCHAR(128)
DECLARE @ApplicationName VARCHAR(128)
DECLARE @AppEmailGroup VARCHAR(128)
DECLARE @ServerLayer VARCHAR(128)
--#################################################################################################
-- Database Specific Contact Groups if they exist
--#################################################################################################
SELECT @ApplicationName = CONVERT(VARCHAR(4000),prpz.value) FROM sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'ApplicationName'
SELECT @AppEmailGroup = CONVERT(VARCHAR(4000),prpz.value) FROM sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'AppEmailGroup'
SELECT @ServerName = CONVERT(VARCHAR(128),prpz.value) FROM sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'Cname'
SELECT @ServerLayer = CONVERT(VARCHAR(128),prpz.value) FROM sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'ServerLayer'
-- SELECT @ServerName AS CName,@ServerLayer AS ServerLayer,@ApplicationName AS ApplicationName,@AppEmailGroup AS AppEmailGroup
--#################################################################################################
-- Server Level Contact Groups if Database Level are Blank
--#################################################################################################
IF @ApplicationName IS NULL
SELECT @ApplicationName = CONVERT(VARCHAR(4000),prpz.value) FROM &#91;master].sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'ApplicationName'
IF @AppEmailGroup IS NULL
SELECT @AppEmailGroup = CONVERT(VARCHAR(4000),prpz.value) FROM &#91;master].sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'AppEmailGroup'
IF @ServerName IS NULL
SELECT @ServerName = CONVERT(VARCHAR(128),prpz.value) FROM &#91;master].sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'Cname'
IF @ServerLayer IS NULL
SELECT @ServerLayer = CONVERT(VARCHAR(128),prpz.value) FROM &#91;master].sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'ServerLayer'
SELECT @ServerName = ISNULL(@ServerName,CONVERT(VARCHAR(128),@@SERVERNAME))
--desc: compare local to the last know patches available
--cluster information, including preferred node and current node names
;WITH ClusterActiveNode AS
(
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NodeName, CAST('Active' AS VARCHAR(10)) AS Active
),
ClusterNodes AS
(
SELECT NodeName FROM sys.dm_os_cluster_nodes
)
,AllClusterNodes AS
(
SELECT s.Colzs AS AllNodes
FROM(SELECT
Colzs = STUFF((SELECT ',' + NodeName
FROM &#91;sys].&#91;dm_os_cluster_nodes]
ORDER BY NodeName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
)
,AllAGNodes AS
(
SELECT s.Colzs AS AllNodes
FROM(SELECT
Colzs = STUFF((SELECT ',' + replica_server_name
FROM master.sys.availability_groups Groups
INNER JOIN master.sys.availability_replicas Replicas
ON Groups.group_id = Replicas.group_id
ORDER BY replica_server_name
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
)
,
Clusterinfo
AS
(
SELECT CONVERT(VARCHAR(128),@@SERVERNAME) AS ServerName,b.NodeName, ISNULL(&#91;a].&#91;Active],'Passive') AS NodeStatus FROM ClusterNodes AS b LEFT JOIN ClusterActiveNode AS a
ON &#91;a].&#91;NodeName] = b.NodeName
)
, MyCTE(&#91;SQLVersion],&#91;PatchVersion])
AS
(
--SELECT tbl FROM &#91;DBAResources].&#91;dbo].&#91;vwLatestSqlServerServicePacks]
SELECT '2014','12.0.6449.1' UNION ALL
SELECT '2016','13.0.7037.1' UNION ALL
SELECT '2017','14.0.3471.2' UNION ALL
SELECT '2019','15.0.4385.2' UNION ALL
SELECT '2022','16.0.4135.4'
)
,TheData
AS
(
SELECT
CASE WHEN &#91;PatchVersion] = CONVERT(VARCHAR(128),SERVERPROPERTY('productversion')) THEN '' ELSE 'Patch Available' END AS PatchStatus,
CONVERT(VARCHAR(128),@@SERVERNAME) AS ServerName,
SQLVersion,
CONVERT(VARCHAR(128),SERVERPROPERTY('productversion')) AS ProductVersion,
&#91;PatchVersion],
(SELECT create_date FROM sys.databases WHERE name = 'tempdb') AS ServerRestarted,
--CASE WHEN EXISTS (SELECT * FROM sys.&#91;dm_os_cluster_nodes]) THEN 'IsClustered' ELSE '' END AS isClustered,
--CASE WHEN EXISTS (SELECT * FROM master.sys.availability_groups) THEN 'IsAAG' ELSE '' END AS isAAG
'' AS Filler
FROM MyCTE
WHERE LEFT(CONVERT(VARCHAR(128),SERVERPROPERTY('productversion')),2) = LEFT(&#91;PatchVersion],2)
)
SELECT @ServerName AS CName,@ServerLayer AS ServerLayer,@ApplicationName AS ApplicationName,@AppEmailGroup AS AppEmailGroup,
ISNULL(&#91;Clusterinfo].&#91;ServerName],@@ServerName) AS &#91;ServerName],
ISNULL(&#91;Clusterinfo].&#91;NodeName],'--') AS &#91;NodeName],
ISNULL(&#91;Clusterinfo].&#91;NodeStatus],'--') AS &#91;NodeStatus],
ISNULL(&#91;AllClusterNodes].&#91;AllNodes],'') AS AllClusterNodes,
ISNULL(&#91;AllAGNodes].&#91;AllNodes],'') AS AllAGNodes,
&#91;TheData].&#91;PatchStatus],
&#91;TheData].&#91;ServerName],
&#91;TheData].&#91;SQLVersion],
&#91;TheData].&#91;ProductVersion],
&#91;TheData].&#91;PatchVersion],
&#91;TheData].&#91;ServerRestarted],
&#91;TheData].&#91;Filler] FROM TheData
LEFT JOIN Clusterinfo ON &#91;TheData].&#91;ServerName] = &#91;Clusterinfo].&#91;ServerName]
CROSS JOIN &#91;AllClusterNodes]
CROSS JOIN &#91;AllAGNodes]
--WHERE PatchStatus > ''
Author: ChatGPT
This query provides a **comprehensive SQL Server health check**, including **application metadata, clustering information, availability group nodes, and patch version comparisons**. The output includes:
- **Application Details:** Retrieves `ApplicationName`, `AppEmailGroup`, `CName`, and `ServerLayer` from extended properties.
- **Cluster Information:** Identifies the **active/passive cluster node**, all nodes in the cluster, and all replicas in an **Always On Availability Group**.
- **Patch Status:** Compares the **current SQL Server version** against a predefined list of the latest patch versions for **SQL Server 2014???2022**, flagging if an update is available.
- **Last Server Restart Time:** Uses `tempdb` creation date to estimate the **last SQL Server restart**.
This query is valuable for **SQL Server administrators** by providing **a single view of Always On cluster health, patch compliance, and server metadata**, helping **proactively manage updates, track failover status, and ensure high availability configurations are correctly set up**.
Author: user
--desc: cluster information, including preferred node and current node names
;WITH ClusterActiveNode AS
(
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NodeName, CAST('Active' AS VARCHAR(10)) AS Active
),
ClusterNodes AS
(
SELECT NodeName FROM sys.dm_os_cluster_nodes
)
SELECT CONVERT(VARCHAR(128),@@SERVERNAME) AS ServerName,b.NodeName, ISNULL(Active,'Passive') AS NodeStatus FROM ClusterNodes AS b LEFT JOIN ClusterActiveNode AS a
ON a.NodeName = b.NodeName
;WITH ClusterActiveNode AS
(
SELECT
CASE
WHEN CHARINDEX('\',CONVERT(VARCHAR(128),@@SERVERNAME)) > 0
THEN LEFT(CONVERT(VARCHAR(128),@@SERVERNAME),CHARINDEX('\',CONVERT(VARCHAR(128),@@SERVERNAME)) -1)
ELSE CONVERT(VARCHAR(128),@@SERVERNAME)
END AS ServerInstanceAlias,
CONVERT(VARCHAR(128),SERVERPROPERTY('ComputerNamePhysicalNetBIOS')) AS NodeName,
CAST('Active' AS VARCHAR(10)) AS Active
),
ClusterNodes AS
(
SELECT CONVERT(VARCHAR(128),@@SERVERNAME) AS ServerName,NodeName FROM sys.dm_os_cluster_nodes
),
CurrentAlignment
AS
(
SELECT
b.ServerName
ServerInstanceAlias,
b.NodeName,
ISNULL(Active,'Passive') AS NodeStatus ,
--CASE
-- WHEN CHARINDEX(RIGHT(a.NodeName,4) ,RIGHT(a.ServerInstanceAlias,4) ) > 0
-- THEN 'Preferred'
-- ELSE ''
--END AS PreferredNode,
CASE
WHEN b.ServerName LIKE '%' + RIGHT(b.NodeName,4) + '%' THEN 'Preferred'
WHEN b.ServerName LIKE '%' + RIGHT(b.NodeName,3) + '%' THEN 'Preferred'
ELSE ''
END AS PreferredNodeNew
FROM ClusterNodes AS b
LEFT JOIN ClusterActiveNode AS a
ON a.NodeName = b.NodeName
--ORDER BY ServerInstanceAlias,NodeStatus
)
SELECT CASE WHEN NodeStatus='Active' AND PreferredNodeNew='' THEN 'Not On PreferredNode' ELSE '' END AS NodeStatus,*
FROM CurrentAlignment
WHERE CASE WHEN NodeStatus='Active' AND PreferredNodeNew='' THEN 'Not On PreferredNode' ELSE '' END = 'Not On PreferredNode'
Author: ChatGPT
This query retrieves **SQL Server Always On cluster information**, specifically **the active node, all cluster nodes, and whether the active node is the preferred node**. The output includes:
- **Server Name & Active Node:** Identifies the **current active node** in the cluster.
- **Cluster Nodes:** Lists all nodes participating in the **SQL Server failover cluster**.
- **Preferred Node Detection:** Determines whether the **active node is the preferred node** based on a pattern match in the node and instance names.
- **Health Check:** Flags if the **active node is not the preferred node**, helping **DBAs detect unexpected failovers**.
This information is **critical for SQL Server high availability monitoring**, allowing administrators to **quickly identify failovers, ensure that workloads are running on the correct node, and troubleshoot cluster misalignment issues**.
Author: user
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--desc: if an extended property for the cname was created, list it here.
--verify the cname
DECLARE @ServerName VARCHAR(128)
SELECT
@ServerName = CONVERT(VARCHAR(128),prpz.value) --AS CName
FROM &#91;master].sys.&#91;extended_properties] prpz
WHERE prpz.Class=0
AND prpz.name = 'Cname'
SELECT @ServerName = ISNULL(@ServerName,CONVERT(VARCHAR(128),@@SERVERNAME))
IF OBJECT_ID('tempdb..&#91;#xpdrives]') IS NOT NULL
DROP TABLE &#91;#xpdrives]
CREATE TABLE #xpdrives(DriveLetter VARCHAR(30),MBFree INT, GBFree AS CONVERT(VARCHAR(30),CONVERT(DECIMAL(6,0),(MBFree/1000.0))) + 'GB' )
INSERT INTO #xpdrives(DriveLetter,MBFree)
EXECUTE xp_fixeddrives
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results(ResultsId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,ResultsItem VARCHAR(50),ResultsValue varchar(1000) )
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'CName' AS Item, @ServerName AS Value
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'AppEmailGroup',ISNULL((SELECT CONVERT(VARCHAR(128),&#91;value]) FROM master.sys.&#91;extended_properties] WHERE &#91;major_id]=0 AND name = 'AppEmailGroup'),'')
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'ServerName' AS Item,CONVERT(VARCHAR(128),@@ServerName) AS Value
IF OBJECT_ID('tempdb.&#91;dbo].&#91;#CPUData]') IS NOT NULL
DROP TABLE &#91;dbo].&#91;#CPUData]
CREATE TABLE #CPUData (cpu_count int, hyperthread_ratio int,physicialCPUSockets int,PhysicalMemoryMB money,VirtualMachine varchar(50),VirtualMachineType varchar(50))
--SQL2014 and above, column is different
--select Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4) + '.' + Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
IF CONVERT(DECIMAL(5,2),PARSENAME(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)
+ '.'
+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
)>=11.0
BEGIN
INSERT INTO #CPUData(&#91;cpu_count],&#91;hyperthread_ratio],&#91;physicialCPUSockets],&#91;PhysicalMemoryMB],&#91;VirtualMachine],&#91;VirtualMachineType])
EXEC(
'SELECT
s.cpu_count ,
s.hyperthread_ratio ,
s.cpu_count / s.hyperthread_ratio As NumberofCores,
s.physical_memory_kb/1000.0 As MemoryinMb,
s.virtual_machine_type_desc,
CASE WHEN s.virtual_machine_type > 0 THEN ''Virtual'' ELSE '''' END As IsVirtual--1 = hypervisor, 2 = other type of virtual
FROM sys.dm_os_sys_info s');
END
ELSE
BEGIN
INSERT INTO #CPUData(&#91;cpu_count],&#91;hyperthread_ratio],&#91;physicialCPUSockets],&#91;PhysicalMemoryMB],&#91;VirtualMachine],&#91;VirtualMachineType])
EXEC(
'SELECT
s.cpu_count ,
s.hyperthread_ratio ,
s.cpu_count / s.hyperthread_ratio As NumberofCores,
s.physical_memory_in_bytes/1048576.0 As MemoryinMb,
'''' AS virtual_machine_type_desc,
'''' As IsVirtual
FROM sys.dm_os_sys_info s');
END
IF EXISTS (SELECT * FROM sys.all_columns WHERE object_name(object_id) ='dm_os_sys_info' AND name = 'virtual_machine_type')
BEGIN
EXEC(
'UPDATE MyTarget
SET VirtualMachine = CASE WHEN s.virtual_machine_type > 0 THEN ''Virtual'' ELSE '''' END
--SELECT CASE WHEN s.virtual_machine_type > 0 THEN ''Virtual'' ELSE '''' END --1 = hypervisor, 2 = other type of virtual
FROM sys.dm_os_sys_info s
CROSS JOIN #CPUData MyTarget');
END
UPDATE MyTarget SET &#91;VirtualMachine] = 'Virtual',&#91;VirtualMachineType] = 1 FROM #CPUData MyTarget WHERE CONVERT(VARCHAR(4000),@@VERSION) LIKE'%(VM)%'
UPDATE MyTarget SET &#91;VirtualMachine] = 'SQL2005/Cannot Determine',&#91;VirtualMachineType] = 0 FROM #CPUData MyTarget WHERE ISNULL(PARSENAME(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4),'') + '.' + ISNULL(Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3),'')='9.00'
--SELECT ISNULL(PARSENAME(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4),'') + '.' + ISNULL(Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3),'')
;WITH MyCTE(&#91;InternalName],&#91;HexValue],&#91;OSName])
AS
(
SELECT 'PRODUCT_BUSINESS',0x00000006,'Business' UNION ALL
SELECT 'PRODUCT_BUSINESS_N',0x00000010,'Business N' UNION ALL
SELECT 'PRODUCT_CLUSTER_SERVER',0x00000012,'HPC Edition' UNION ALL
SELECT 'PRODUCT_CLUSTER_SERVER_V',0x00000040,'Server Hyper Core V' UNION ALL
SELECT 'PRODUCT_CORE',0x00000065,'Windows 10 Home' UNION ALL
SELECT 'PRODUCT_CORE_COUNTRYSPECIFIC',0x00000063,'Windows 10 Home China' UNION ALL
SELECT 'PRODUCT_CORE_N',0x00000062,'Windows 10 Home N' UNION ALL
SELECT 'PRODUCT_CORE_SINGLELANGUAGE',0x00000064,'Windows 10 Home Single Language' UNION ALL
SELECT 'PRODUCT_DATACENTER_EVALUATION_SERVER',0x00000050,'Server Datacenter (evaluation installation)' UNION ALL
SELECT 'PRODUCT_DATACENTER_A_SERVER_CORE',0x00000091,'Server Datacenter, Semi-Annual Channel (core installation)' UNION ALL
SELECT 'PRODUCT_STANDARD_A_SERVER_CORE',0x00000092,'Server Standard, Semi-Annual Channel (core installation)' UNION ALL
SELECT 'PRODUCT_DATACENTER_SERVER',0x00000008,'Server Datacenter Edition(full installation)' UNION ALL
SELECT 'PRODUCT_DATACENTER_SERVER_CORE',0x0000000C,'Server Datacenter (core installation, Windows Server 2008 R2 and earlier)' UNION ALL
SELECT 'PRODUCT_DATACENTER_SERVER_CORE_V',0x00000027,'Server Datacenter without Hyper-V (core installation)' UNION ALL
SELECT 'PRODUCT_DATACENTER_SERVER_V',0x00000025,'Server Datacenter without Hyper-V (full installation)' UNION ALL
SELECT 'PRODUCT_EDUCATION',0x00000079,'Windows 10 Education' UNION ALL
SELECT 'PRODUCT_EDUCATION_N',0x0000007A,'Windows 10 Education N' UNION ALL
SELECT 'PRODUCT_ENTERPRISE',0x00000004,'Windows 10 Enterprise' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_E',0x00000046,'Windows 10 Enterprise E' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_EVALUATION',0x00000048,'Windows 10 Enterprise Evaluation' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_N',0x0000001B,'Windows 10 Enterprise N' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_N_EVALUATION',0x00000054,'Windows 10 Enterprise N Evaluation' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_S',0x0000007D,'Windows 10 Enterprise 2015 LTSB' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_S_EVALUATION',0x00000081,'Windows 10 Enterprise 2015 LTSB Evaluation' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_S_N',0x0000007E,'Windows 10 Enterprise 2015 LTSB N' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_S_N_EVALUATION',0x00000082,'Windows 10 Enterprise 2015 LTSB N Evaluation' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_SERVER',0x0000000A,'Server Enterprise (full installation)' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_SERVER_CORE',0x0000000E,'Server Enterprise (core installation)' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_SERVER_CORE_V',0x00000029,'Server Enterprise without Hyper-V (core installation)' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_SERVER_IA64',0x0000000F,'Server Enterprise for Itanium-based Systems' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_SERVER_V',0x00000026,'Server Enterprise without Hyper-V (full installation)' UNION ALL
SELECT 'PRODUCT_ESSENTIALBUSINESS_SERVER_ADDL',0x0000003C,'Windows Essential Server Solution Additional' UNION ALL
SELECT 'PRODUCT_ESSENTIALBUSINESS_SERVER_ADDLSVC',0x0000003E,'Windows Essential Server Solution Additional SVC' UNION ALL
SELECT 'PRODUCT_ESSENTIALBUSINESS_SERVER_MGMT',0x0000003B,'Windows Essential Server Solution Management' UNION ALL
SELECT 'PRODUCT_ESSENTIALBUSINESS_SERVER_MGMTSVC',0x0000003D,'Windows Essential Server Solution Management SVC' UNION ALL
SELECT 'PRODUCT_HOME_BASIC',0x00000002,'Home Basic' UNION ALL
SELECT 'PRODUCT_HOME_BASIC_E',0x00000043,'Not supported' UNION ALL
SELECT 'PRODUCT_HOME_BASIC_N',0x00000005,'Home Basic N' UNION ALL
SELECT 'PRODUCT_HOME_PREMIUM',0x00000003,'Home Premium' UNION ALL
SELECT 'PRODUCT_HOME_PREMIUM_E',0x00000044,'Not supported' UNION ALL
SELECT 'PRODUCT_HOME_PREMIUM_N',0x0000001A,'Home Premium N' UNION ALL
SELECT 'PRODUCT_HOME_PREMIUM_SERVER',0x00000022,'Windows Home Server 2011' UNION ALL
SELECT 'PRODUCT_HOME_SERVER',0x00000013,'Windows Storage Server 2008 R2 Essentials' UNION ALL
SELECT 'PRODUCT_HYPERV',0x0000002A,'Microsoft Hyper-V Server' UNION ALL
SELECT 'PRODUCT_IOTUAP',0x0000007B,'Windows 10 IoT Core' UNION ALL
SELECT 'PRODUCT_IOTUAPCOMMERCIAL',0x00000083,'Windows 10 IoT Core Commercial' UNION ALL
SELECT 'PRODUCT_MEDIUMBUSINESS_SERVER_MANAGEMENT',0x0000001E,'Windows Essential Business Server Management Server' UNION ALL
SELECT 'PRODUCT_MEDIUMBUSINESS_SERVER_MESSAGING',0x00000020,'Windows Essential Business Server Messaging Server' UNION ALL
SELECT 'PRODUCT_MEDIUMBUSINESS_SERVER_SECURITY',0x0000001F,'Windows Essential Business Server Security Server' UNION ALL
SELECT 'PRODUCT_MOBILE_CORE',0x00000068,'Windows 10 Mobile' UNION ALL
SELECT 'PRODUCT_MOBILE_ENTERPRISE',0x00000085,'Windows 10 Mobile Enterprise' UNION ALL
SELECT 'PRODUCT_MULTIPOINT_PREMIUM_SERVER',0x0000004D,'Windows MultiPoint Server Premium (full installation)' UNION ALL
SELECT 'PRODUCT_MULTIPOINT_STANDARD_SERVER',0x0000004C,'Windows MultiPoint Server Standard (full installation)' UNION ALL
SELECT 'PRODUCT_PRO_WORKSTATION',0x000000A1,'Windows 10 Pro for Workstations' UNION ALL
SELECT 'PRODUCT_PRO_WORKSTATION_N',0x000000A2,'Windows 10 Pro for Workstations N' UNION ALL
SELECT 'PRODUCT_PROFESSIONAL',0x00000030,'Windows 10 Pro' UNION ALL
SELECT 'PRODUCT_PROFESSIONAL_E',0x00000045,'Not supported' UNION ALL
SELECT 'PRODUCT_PROFESSIONAL_N',0x00000031,'Windows 10 Pro N' UNION ALL
SELECT 'PRODUCT_PROFESSIONAL_WMC',0x00000067,'Professional with Media Center' UNION ALL
SELECT 'PRODUCT_SB_SOLUTION_SERVER',0x00000032,'Windows Small Business Server 2011 Essentials' UNION ALL
SELECT 'PRODUCT_SB_SOLUTION_SERVER_EM',0x00000036,'Server For SB Solutions EM' UNION ALL
SELECT 'PRODUCT_SERVER_FOR_SB_SOLUTIONS',0x00000033,'Server For SB Solutions' UNION ALL
SELECT 'PRODUCT_SERVER_FOR_SB_SOLUTIONS_EM',0x00000037,'Server For SB Solutions EM' UNION ALL
SELECT 'PRODUCT_SERVER_FOR_SMALLBUSINESS',0x00000018,'Windows Server 2008 for Windows Essential Server Solutions' UNION ALL
SELECT 'PRODUCT_SERVER_FOR_SMALLBUSINESS_V',0x00000023,'Windows Server 2008 without Hyper-V for Windows Essential Server Solutions' UNION ALL
SELECT 'PRODUCT_SERVER_FOUNDATION',0x00000021,'Server Foundation' UNION ALL
SELECT 'PRODUCT_SMALLBUSINESS_SERVER',0x00000009,'Windows Small Business Server' UNION ALL
SELECT 'PRODUCT_SMALLBUSINESS_SERVER_PREMIUM',0x00000019,'Small Business Server Premium' UNION ALL
SELECT 'PRODUCT_SMALLBUSINESS_SERVER_PREMIUM_CORE',0x0000003F,'Small Business Server Premium (core installation)' UNION ALL
SELECT 'PRODUCT_SOLUTION_EMBEDDEDSERVER',0x00000038,'Windows MultiPoint Server' UNION ALL
SELECT 'PRODUCT_STANDARD_EVALUATION_SERVER',0x0000004F,'Server Standard (evaluation installation)' UNION ALL
SELECT 'PRODUCT_STANDARD_SERVER',0x00000007,'Server Standard (full installation. For Server Core installations of Windows Server 2012 and later, use the method, Determining whether Server Core is running.)' UNION ALL
SELECT 'PRODUCT_STANDARD_SERVER_CORE',0x0000000D,'Server Standard (core installation, Windows Server 2008 R2 and earlier)' UNION ALL
SELECT 'PRODUCT_STANDARD_SERVER_CORE_V',0x00000028,'Server Standard without Hyper-V (core installation)' UNION ALL
SELECT 'PRODUCT_STANDARD_SERVER_V',0x00000024,'Server Standard without Hyper-V' UNION ALL
SELECT 'PRODUCT_STANDARD_SERVER_SOLUTIONS',0x00000034,'Server Solutions Premium' UNION ALL
SELECT 'PRODUCT_STANDARD_SERVER_SOLUTIONS_CORE',0x00000035,'Server Solutions Premium (core installation)' UNION ALL
SELECT 'PRODUCT_STARTER',0x0000000B,'Starter' UNION ALL
SELECT 'PRODUCT_STARTER_E',0x00000042,'Not supported' UNION ALL
SELECT 'PRODUCT_STARTER_N',0x0000002F,'Starter N' UNION ALL
SELECT 'PRODUCT_STORAGE_ENTERPRISE_SERVER',0x00000017,'Storage Server Enterprise' UNION ALL
SELECT 'PRODUCT_STORAGE_ENTERPRISE_SERVER_CORE',0x0000002E,'Storage Server Enterprise (core installation)' UNION ALL
SELECT 'PRODUCT_STORAGE_EXPRESS_SERVER',0x00000014,'Storage Server Express' UNION ALL
SELECT 'PRODUCT_STORAGE_EXPRESS_SERVER_CORE',0x0000002B,'Storage Server Express (core installation)' UNION ALL
SELECT 'PRODUCT_STORAGE_STANDARD_EVALUATION_SERVER',0x00000060,'Storage Server Standard (evaluation installation)' UNION ALL
SELECT 'PRODUCT_STORAGE_STANDARD_SERVER',0x00000015,'Storage Server Standard' UNION ALL
SELECT 'PRODUCT_STORAGE_STANDARD_SERVER_CORE',0x0000002C,'Storage Server Standard (core installation)' UNION ALL
SELECT 'PRODUCT_STORAGE_WORKGROUP_EVALUATION_SERVER',0x0000005F,'Storage Server Workgroup (evaluation installation)' UNION ALL
SELECT 'PRODUCT_STORAGE_WORKGROUP_SERVER',0x00000016,'Storage Server Workgroup' UNION ALL
SELECT 'PRODUCT_STORAGE_WORKGROUP_SERVER_CORE',0x0000002D,'Storage Server Workgroup (core installation)' UNION ALL
SELECT 'PRODUCT_ULTIMATE',0x00000001,'Ultimate' UNION ALL
SELECT 'PRODUCT_ULTIMATE_E',0x00000047,'Not supported' UNION ALL
SELECT 'PRODUCT_ULTIMATE_N',0x0000001C,'Ultimate N' UNION ALL
SELECT 'PRODUCT_UNDEFINED',0x00000000,'An unknown product' UNION ALL
SELECT 'PRODUCT_WEB_SERVER',0x00000011,'Web Server (full installation)' UNION ALL
SELECT 'PRODUCT_WEB_SERVER_CORE',0x0000001D,'Web Server (core installation)'
)
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'WindowsOperatingSystem',MyCTE.&#91;OSName] FROM sys.dm_os_windows_info osinfo
INNER JOIN MyCTE ON osinfo.&#91;windows_sku] = MyCTE.&#91;HexValue]
--INSERT INTO #Results(ResultsItem,ResultsValue)
--SELECT 'WindowsOperatingSystem', CASE
-- WHEN CHARINDEX (' 11.0',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2019'
-- WHEN CHARINDEX (' 10.0',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2016'
-- WHEN CHARINDEX ('NT 6.3',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2013'
-- WHEN CHARINDEX ('Standard 6.3',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2012R2'
-- WHEN CHARINDEX ('Standard 6.2',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2012'
-- WHEN CHARINDEX ('NT 6.2',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2012'
-- WHEN CHARINDEX ('NT 6.1',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2008R2'
-- WHEN CHARINDEX ('NT 6.0',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2008'
-- WHEN CHARINDEX ('NT 5.2',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2003R2'
-- WHEN CHARINDEX ('NT 5.1',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2003'
-- WHEN CHARINDEX ('NT 5.0',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2000'
-- WHEN CHARINDEX ('NT 4.0',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server NT 4.0'
-- ELSE 'Not Detected?'
-- END + ' -- ' + CONVERT(VARCHAR(20),CONVERT(MONEY,s.PhysicalMemoryMB),1) + ' MB,'
--+ CASE WHEN s.VirtualMachine='' THEN 'Physical Machine ' ELSE s.VirtualMachine + ' Machine: ' END
--+ CONVERT(VARCHAR(3),s.cpu_count) + ' Core /'
--+ CONVERT(VARCHAR(3),s.physicialCPUSockets) + ' Sockets' AS WindowsOperatingSystem
-- FROM #CPUData s
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'Memory', s.PhysicalMemoryMB FROM #CPUData s ;
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'CPUCoreCounts', CONVERT(VARCHAR(3),s.cpu_count) + ' Core /'
+ CONVERT(VARCHAR(3),s.physicialCPUSockets) + ' Sockets' AS CPUCoreCounts
FROM #CPUData s
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'DrivesAndFreeSpace',s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT '; ' + DriveLetter + ':\' + GBFree
FROM &#91;#xpdrives]
ORDER BY DriveLetter
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,2,'')
) s
;WITH Delimiters(sDelimiter,sInputList)
AS
(
SELECT CHAR(10) AS sDelimiter,
REPLACE(CONVERT(VARCHAR(8000),@@VERSION),CHAR(9),' ') AS sInputList
),
E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1), -- 10 or 10E01 rows
E02(N) AS (SELECT 1 FROM E01 a, E01 b), -- 100 or 10E02 rows
E04(N) AS (SELECT 1 FROM E02 a, E02 b), -- 10,000 or 10E04 rows
E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000 or 10E08 rows
E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need
Tally(number) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E16)
,
FormattedVersion(item)
AS
( SELECT
SUBSTRING(sDelimiter + sInputList + sDelimiter, &#91;Tally].&#91;number] + LEN(sDelimiter),
CHARINDEX(sDelimiter, sDelimiter + sInputList + sDelimiter, &#91;Tally].&#91;number] + LEN(sDelimiter)) - &#91;Tally].&#91;number] -LEN(sDelimiter))AS item
--select *
FROM Tally CROSS APPLY Delimiters
WHERE &#91;Tally].&#91;number] <= LEN(sDelimiter + sInputList + sDelimiter) - LEN(sDelimiter)
AND SUBSTRING(sDelimiter + sInputList + sDelimiter, &#91;Tally].&#91;number], LEN(sDelimiter)) = sDelimiter
)
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'SQLServerVersion',
ISNULL(CASE ISNULL(PARSENAME(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4),'')
+ '.'
+ ISNULL(Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3),'')
WHEN '.8' THEN 'SQL 2000'
WHEN '9.00' THEN 'SQL 2005'
WHEN '10.0' THEN 'SQL 2008'
WHEN '10.50' THEN 'SQL 2008R2'
WHEN '11.0' THEN 'SQL 2012'
WHEN '12.0' THEN 'SQL 2014'
WHEN '13.0' THEN 'SQL 2016'
WHEN '14.0' THEN 'SQL 2017'
WHEN '15.0' THEN 'SQL 2019'
WHEN '16.0' THEN 'SQL 2022'
WHEN '17.0' THEN 'SQL 2025'
ELSE ISNULL(PARSENAME(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4),'')
+ '.'
+ ISNULL(Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3),'')
END,'??') + '::' +item
FROM FormattedVersion
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'SQL Edition',CONVERT(VARCHAR(128), Serverproperty('Edition')) AS Edition
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'TCPIP Port',MAX(local_tcp_port) FROM sys.dm_exec_connections WHERE net_transport='TCP'
--DBRecap
;WITH AllDatabases
AS
(
SELECT &#91;t].&#91;database_id],&#91;t].&#91;name] AS TheDatabase,&#91;t].&#91;recovery_model_desc],&#91;t].&#91;user_access_desc],&#91;t].&#91;state_desc],t.&#91;is_read_only] FROM &#91;master].&#91;sys].&#91;databases] t WHERE &#91;t].&#91;name] NOT IN('master','tempdb','model','msdb')
),
myCTE
AS
(
SELECT
&#91;AllDatabases].&#91;TheDatabase],
&#91;AllDatabases].&#91;recovery_model_desc],
&#91;AllDatabases].&#91;user_access_desc],
&#91;AllDatabases].&#91;state_desc],
CONVERT(INT,&#91;AllDatabases].&#91;is_read_only]) AS &#91;is_read_only],
&#91;statz].&#91;last_user_seek],
&#91;statz].&#91;last_user_scan],
&#91;statz].&#91;last_user_lookup],
&#91;statz].&#91;last_user_update]
FROM AllDatabases
LEFT OUTER JOIN &#91;sys].&#91;dm_db_index_usage_stats] statz
ON &#91;AllDatabases].&#91;database_id] = &#91;statz].&#91;database_id]
),NearlyDone
AS
(
SELECT
ServerRestartedDate = (SELECT CREATE_DATE FROM sys.databases where name='tempdb'),
&#91;x].&#91;TheDatabase],
MAX(&#91;x].&#91;recovery_model_desc]) AS recovery_model_desc,
MAX(&#91;x].&#91;user_access_desc]) AS user_access_desc,
MAX(&#91;x].&#91;state_desc]) AS state_desc,
MAX(&#91;x].&#91;is_read_only]) AS is_read_only,
MAX(&#91;x].&#91;last_read]) AS last_read,
MAX(&#91;x].&#91;last_write]) AS last_write
FROM
(
SELECT &#91;myCTE].&#91;TheDatabase],&#91;myCTE].&#91;recovery_model_desc],&#91;myCTE].is_read_only,&#91;myCTE].&#91;user_access_desc],&#91;myCTE].&#91;state_desc],&#91;myCTE].&#91;last_user_seek] AS last_read, NULL AS last_write FROM myCTE
UNION ALL
SELECT &#91;myCTE].&#91;TheDatabase],NULL,NULL,NULL,NULL,&#91;myCTE].&#91;last_user_scan], NULL FROM myCTE
UNION ALL
SELECT &#91;myCTE].&#91;TheDatabase],NULL,NULL,NULL,NULL,&#91;myCTE].&#91;last_user_lookup], NULL FROM myCTE
UNION ALL
SELECT &#91;myCTE].&#91;TheDatabase],NULL,NULL,NULL,NULL,NULL, &#91;myCTE].&#91;last_user_update] FROM myCTE
) AS x
GROUP BY TheDatabase
)
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'Databases',
ISNULL('TotalDBs = '
+ CONVERT(VARCHAR(5),COUNT(*))
+ ', OffLine='
+ CONVERT(VARCHAR(5),SUM(CASE WHEN NearlyDone.state_desc <> 'ONLINE' THEN 1 ELSE 0 END))
+ ', ReadOnly='
+ CONVERT(VARCHAR(5),SUM(CASE WHEN NearlyDone.&#91;is_read_only] = 1 THEN 1 ELSE 0 END))
+', Unused='
+ CONVERT(VARCHAR(5),SUM(CASE WHEN NearlyDone.&#91;is_read_only] = 0 AND NearlyDone.&#91;last_read] IS NULL AND NearlyDone.&#91;last_write] IS NULL THEN 1 ELSE 0 END))
+ ' Since '
+ CONVERT(VARCHAR(40),MAX(NearlyDone.ServerRestartedDate),120),'No User Databases')
FROM NearlyDone
WHERE &#91;TheDatabase] NOT IN('master','model','tempdb','msdb')
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'Database Size', 'Databases Are ' + CONVERT(VARCHAR(10),CONVERT(decimal(18,2),SUM(size*8.0) / (1024.0*1024.0))) + 'GB in size' FROM sysaltfiles WHERE dbid > 4
DECLARE @SQLMemory INT;
DECLARE @PhysicalMemoryMB int;
SELECT @SQLMemory = CONVERT(int,value) FROM sys.&#91;configurations] WHERE name = 'max server memory (MB)'
SELECT @PhysicalMemoryMB = s.PhysicalMemoryMB FROM #CPUData s ;
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'Memory','SQL MaxMemory: ' + CONVERT(VARCHAR,@SQLMemory / 1024) + ' GB | TotalMemory: ' + CONVERT(VARCHAR,@PhysicalMemoryMB / 1024 )+ ' GB'
IF EXISTS(SELECT * FROM sys.all_objects WHERE name = 'dm_os_buffer_pool_extension_configuration')
EXECUTE('INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT ''BufferPoolExtensions'', CONVERT(VARCHAR(30),ISNULL((cnfgz.current_size_in_kb / (1024*1024)),0)) + '' GB''
FROM sys.dm_os_buffer_pool_extension_configuration cnfgz
GROUP BY cnfgz.current_size_in_kb')
ELSE INSERT INTO #Results(ResultsItem,ResultsValue) SELECT 'BufferPoolExtensions','Buffer Pool Not an option due to SQL Version.'
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'SQLLogins',
ISNULL('TotalSQLLogins = ' + CONVERT(VARCHAR(5),COUNT(1))
+ ', Disabled = '
+ CONVERT(VARCHAR(5),SUM (CASE WHEN prnz.is_disabled =1 THEN 1 ELSE 0 END))
+ ', PasswordReset90DayViolation='
+ CONVERT(VARCHAR(5),ISNULL(SUM (CASE WHEN prnz.is_policy_checked=0 AND LOGINPROPERTY(prnz.name, 'PasswordLastSetTime') < DATEADD(dd,-91,GETDATE()) THEN 1 ELSE 0 END),0)),'No SQL Logins')
FROM sys.sql_logins prnz WHERE prnz.type_desc='SQL_Login'
AND prnz.name NOT LIKE '##%'
AND prnz.principal_id > 1
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT CONVERT(VARCHAR(128), name),CONVERT(VARCHAR(128), name) + ' = ' + CONVERT(VARCHAR(30),value) FROM sys.configurations WHERE name IN ('remote admin connections','optimize for ad hoc workloads','backup compression default','affinity mask','cost threshold for parallelism','max degree of parallelism')
ORDER BY name
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT servicename,service_account FROM sys.dm_server_services
SELECT * FROM #Results
Author: ChatGPT
This query performs a **comprehensive SQL Server system health check**, gathering **server metadata, hardware details, SQL configuration settings, and database statistics** into a structured report. The output includes:
- **Server & Environment Details**: Retrieves **CName, SQL Server version, edition, TCP/IP port, Windows OS type, and virtualization status**.
- **Hardware & Performance Metrics**: Gathers **CPU count, memory allocation, buffer pool extension details, disk space usage, and SQL memory settings**.
- **Cluster & Availability Group Info**: Lists **cluster nodes, active node, and availability group replicas**.
- **Database Overview**: Summarizes **total databases, offline/read-only databases, unused databases, and overall database size**.
- **Security & Configuration Checks**: Reports **SQL logins (including disabled accounts and password policy violations), key SQL configuration settings (`max degree of parallelism`, `cost threshold for parallelism`), and service account details**.
This report is invaluable for **SQL Server administrators** as it consolidates key information into a **single structured output**, allowing for **quick system assessments, performance tuning, security audits, and proactive troubleshooting**.
Author: user
EXECUTE &#91;dbo].&#91;sp_BlitzAnalysis]
Author: ChatGPT
### **sp_BlitzAnalysis Execution Summary**
Executing `&#91;dbo].&#91;sp_BlitzAnalysis]` runs a **detailed SQL Server health check and performance analysis** using **Brent Ozar???s First Responder Kit (sp_Blitz, sp_BlitzCache, sp_BlitzIndex, etc.)** or a **custom in-house version** of an analysis procedure. The expected output provides:
1. **Overall SQL Server Health Check**
- Identifies **configuration issues, security vulnerabilities, missing indexes, query performance bottlenecks, and wait statistics**.
- Flags **high CPU/memory usage, long-running transactions, and potential deadlocks**.
2. **Query Performance Insights**
- Analyzes **query execution plans** to detect **expensive queries, missing or unused indexes, and high CPU/memory-consuming queries**.
- Provides recommendations for **query tuning and indexing strategies**.
3. **Storage & I/O Performance**
- Detects **high disk latency, excessive reads/writes, and inefficient indexing**.
- Identifies **log file autogrowth issues and tempdb contention**.
4. **Blocking & Deadlock Detection**
- Reports on **blocking chains, deadlocks, and long-running queries** affecting server performance.
### **Advantages of Running sp_BlitzAnalysis**
- Provides a **holistic view** of SQL Server performance and potential problems.
- Helps DBAs **prioritize fixes** by categorizing issues as **critical, warning, or informational**.
- Offers **actionable recommendations** to optimize **query performance, indexing, and server configurations**.
- Saves time by **automating SQL Server health assessments** instead of manual troubleshooting.
Would you like a breakdown of specific sections of the report, such as **query performance, index tuning, or security recommendations**?
Author: user
EXECUTE &#91;dbo].&#91;sp_BlitzBackups]
Author: ChatGPT
### **sp_BlitzBackups Execution Summary**
Executing `&#91;dbo].&#91;sp_BlitzBackups]` provides a **detailed analysis of SQL Server backups**, helping **DBAs ensure proper backup policies, detect gaps, and optimize recovery strategies**. The expected output includes:
1. **Backup Frequency & Gaps**
- Lists the most recent **full, differential, and transaction log backups** for each database.
- Flags databases that **haven't been backed up recently** or **lack transaction log backups**, increasing recovery risks.
2. **Backup Storage & Performance**
- Reports on **backup file locations, sizes, and compression status** to identify **storage inefficiencies**.
- Highlights **long-running backups**, which could indicate I/O bottlenecks or excessive database growth.
3. **Recovery Readiness**
- Determines if databases are in **FULL or SIMPLE recovery mode**, ensuring that **point-in-time recovery is possible**.
- Identifies **databases missing log backups** in **FULL recovery mode**, which could lead to bloated transaction log files.
4. **Backup Strategy Recommendations**
- Provides **actionable insights** on improving **backup scheduling, retention policies, and compression settings**.
- Warns about **potential RPO (Recovery Point Objective) and RTO (Recovery Time Objective) violations**.
### **Advantages of Running sp_BlitzBackups**
- **Quickly audits backup health** for disaster recovery readiness.
- **Detects missing or outdated backups** that could cause data loss.
- **Optimizes backup storage usage** and **prevents log growth issues**.
- **Improves recovery planning** by ensuring that **point-in-time recovery is feasible**.
Would you like assistance interpreting a specific **backup gap, log growth issue, or storage concern** from the results?
Author: user
EXECUTE &#91;dbo].&#91;sp_BlitzCache]
Author: ChatGPT
### **sp_BlitzCache Execution Summary**
Executing `&#91;dbo].&#91;sp_BlitzCache]` provides a **detailed analysis of the SQL Server plan cache**, helping **DBAs identify expensive queries, inefficient execution plans, and potential tuning opportunities**. The output focuses on **query performance, execution statistics, and caching behavior**.
### **Key Information in the Output:**
1. **Top Resource-Consuming Queries**
- Identifies queries with the **highest CPU, I/O, memory, and execution times**.
- Highlights **queries causing excessive recompilation** or using **a lot of worker threads**.
2. **Inefficient Execution Plans**
- Detects **queries with missing indexes, implicit conversions, or large memory grants**.
- Identifies **plan cache bloating**, which can cause **poor reuse of execution plans**.
3. **Parameter Sniffing & Recompilation Issues**
- Flags **queries suffering from parameter sniffing**, leading to inconsistent performance.
- Detects **stored procedures and ad-hoc queries frequently recompiling**, which increases CPU usage.
4. **Expensive Query Patterns**
- Lists **queries with the longest execution time, highest reads/writes, or frequent parallelism**.
- Highlights **TOP (N) and OFFSET queries that could be optimized for better performance**.
5. **Query Store & Forced Plans Insights** *(if enabled)*
- Provides details on **Query Store usage, forced plans, and automatic tuning recommendations**.
### **Advantages of Running sp_BlitzCache**
- Helps **DBAs quickly find and optimize expensive queries**.
- Identifies **bad execution plans, missing indexes, and inefficient query structures**.
- Detects **parameter sniffing issues and excessive recompilation**, preventing CPU spikes.
- Improves **SQL Server performance by tuning cache behavior and query execution plans**.
Would you like guidance on **fixing a specific query performance issue** from your `sp_BlitzCache` results?
Author: user
EXECUTE &#91;dbo].&#91;sp_BlitzIndex]
Author: ChatGPT
### **sp_BlitzIndex Execution Summary**
Executing `&#91;dbo].&#91;sp_BlitzIndex]` provides a **comprehensive analysis of SQL Server indexing**, helping **DBAs optimize query performance, detect missing or unused indexes, and resolve fragmentation issues**. The output focuses on **index usage, health, and potential improvements**.
### **Key Information in the Output:**
1. **Missing Indexes**
- Identifies indexes **SQL Server recommends but are missing**, along with their estimated impact on query performance.
- Helps **DBAs decide which indexes to create** to **reduce scan times and optimize query execution**.
2. **Unused & Duplicate Indexes**
- Lists **indexes that haven???t been used** in a significant period, potentially adding unnecessary overhead.
- Detects **duplicate or overlapping indexes**, which waste storage and slow down insert/update operations.
3. **Fragmented Indexes**
- Highlights indexes **with high fragmentation**, which can **slow down read performance and increase I/O costs**.
- Suggests whether to **rebuild or reorganize indexes** based on fragmentation level.
4. **Large Indexes & Disk Usage**
- Reports **indexes consuming excessive disk space**, helping DBAs **identify bloated indexes that need optimization**.
5. **Index Recommendations & Warnings**
- Flags **indexes that might cause performance issues**, such as **wide clustered indexes, high write latency, or incorrect key column choices**.
- Suggests **improvements like composite indexes, included columns, or index compression**.
### **Advantages of Running sp_BlitzIndex**
- **Improves query performance** by identifying **missing indexes** and **suggesting optimizations**.
- **Reduces storage and maintenance overhead** by detecting **unused or duplicate indexes**.
- **Optimizes I/O efficiency** by recommending **index fragmentation fixes**.
- **Provides actionable insights** to **fine-tune indexing strategy** based on workload patterns.
Would you like help **interpreting a specific finding** or **prioritizing index changes** from your `sp_BlitzIndex` results?
Author: user
EXECUTE &#91;dbo].&#91;sp_BlitzInMemoryOLTP]
Author: ChatGPT
### **sp_BlitzInMemoryOLTP Execution Summary**
Executing `&#91;dbo].&#91;sp_BlitzInMemoryOLTP]` provides **a detailed analysis of SQL Server's In-Memory OLTP (Hekaton) feature**, helping **DBAs evaluate memory-optimized tables, natively compiled stored procedures, and potential performance issues**.
### **Key Information in the Output:**
1. **Memory-Optimized Tables**
- Lists all **memory-optimized tables**, their **sizes, row counts, and index types (hash/non-clustered)**.
- Identifies **tables with high memory usage**, which could affect overall server performance.
2. **Natively Compiled Stored Procedures**
- Reports on **stored procedures compiled for In-Memory OLTP**.
- Highlights **procedures that could benefit from memory optimization**.
3. **Checkpoint File Pairs & Garbage Collection**
- Shows **checkpoint file usage**, which impacts transaction log persistence for In-Memory OLTP.
- Detects **garbage collection inefficiencies**, which could lead to excessive log growth.
4. **Transaction Performance & Contention**
- Reports **transactions with excessive latency**, helping pinpoint **bottlenecks in memory-optimized workloads**.
- Detects **locking/latching issues in mixed-memory and disk-based transactions**.
5. **Durability & Logging Issues**
- Flags **memory-optimized tables with durability issues**, ensuring **data persistence after a crash or restart**.
- Warns about **heavy disk I/O caused by excessive checkpoint file generation**.
### **Advantages of Running sp_BlitzInMemoryOLTP**
- **Optimizes In-Memory OLTP performance** by analyzing table and transaction efficiency.
- **Detects durability issues**, ensuring **data persistence and recovery integrity**.
- **Identifies high-memory consumption**, helping **DBAs fine-tune memory allocations**.
- **Provides actionable insights** on **improving query execution and reducing contention**.
Would you like help **interpreting findings or optimizing your In-Memory OLTP implementation** based on your `sp_BlitzInMemoryOLTP` results?
Author: user
EXECUTE &#91;dbo].&#91;sp_BlitzLock]
Author: ChatGPT
### **sp_BlitzLock Execution Summary**
Executing `&#91;dbo].&#91;sp_BlitzLock]` provides a **detailed analysis of deadlocks and blocking issues** in SQL Server, helping **DBAs diagnose and resolve concurrency problems**. This stored procedure retrieves **historical deadlock data** and highlights **queries causing contention**.
### **Key Information in the Output:**
1. **Deadlock History & Analysis**
- Extracts **recent deadlocks** from the **system_health** or **custom Extended Events sessions**.
- Displays **which queries, objects, and transactions were involved in the deadlock**.
- Identifies the **victim process (query that was terminated)** and the **winner (query that continued execution)**.
2. **Blocking & Concurrency Issues**
- Lists **queries that have been blocking other transactions**, showing their **duration and impact**.
- Identifies **high-frequency blocking transactions**, which can **degrade system performance**.
3. **Lock Types & Escalation Detection**
- Reports **lock types (e.g., shared, exclusive, update locks) and escalation events**.
- Detects **excessive lock contention** that could be **impacting performance**.
4. **Index & Query Optimization Recommendations**
- Suggests **indexing strategies to reduce lock contention**.
- Highlights **query patterns prone to deadlocks**, such as **update conflicts or missing indexes**.
5. **Isolation Level & Transaction Handling Insights**
- Helps **detect inappropriate isolation levels**, such as **overuse of SERIALIZABLE or REPEATABLE READ**.
- Identifies **long-running transactions** contributing to lock waits.
### **Advantages of Running sp_BlitzLock**
- **Helps pinpoint deadlock root causes** and **provides insights into resolving them**.
- **Detects blocking queries** that are **slowing down performance**.
- **Optimizes locking strategies** by **identifying problematic transactions and indexes**.
- **Improves database concurrency** by recommending **better isolation levels and indexing strategies**.
Would you like assistance **interpreting a deadlock report** or **resolving a specific locking issue** from your `sp_BlitzLock` results?
Author: user
EXECUTE &#91;dbo].&#91;sp_BlitzQueryStore]
Author: ChatGPT
### **sp_BlitzQueryStore Execution Summary**
Executing `&#91;dbo].&#91;sp_BlitzQueryStore]` provides a **detailed analysis of Query Store data**, helping **DBAs identify performance issues, regressions, and inefficient execution plans**. It leverages **SQL Server???s Query Store** to retrieve **historical query performance insights**.
### **Key Information in the Output:**
1. **Top Resource-Consuming Queries**
- Identifies queries with the **highest CPU, memory, I/O, or execution time usage**.
- Helps **pinpoint performance bottlenecks** caused by **inefficient queries**.
2. **Query Plan Regressions**
- Detects **queries where performance has degraded due to plan changes**.
- Identifies **forced plans that may need to be reviewed** for efficiency.
3. **Execution Plan Variability**
- Lists queries with **multiple execution plans**, which could indicate **parameter sniffing issues**.
- Highlights **queries with excessive recompilations**, leading to **high CPU usage**.
4. **Wait Statistics for Queries**
- Analyzes **wait types associated with queries** (e.g., CPU waits, I/O waits, memory pressure).
- Helps **DBAs diagnose delays due to resource contention**.
5. **Query Store Configuration & Recommendations**
- Checks if **Query Store is enabled and properly configured**.
- Suggests **best practices for retention settings, cleanup, and forced plan usage**.
### **Advantages of Running sp_BlitzQueryStore**
- **Optimizes query performance** by **identifying top slow-running queries**.
- **Detects execution plan regressions**, allowing **DBAs to force stable plans if needed**.
- **Helps diagnose parameter sniffing issues** and **improves plan reuse**.
- **Provides actionable insights** on **Query Store configuration and maintenance**.
Would you like help **interpreting Query Store data, forcing stable plans, or troubleshooting specific query regressions** from your `sp_BlitzQueryStore` results?
Author: user
EXECUTE &#91;dbo].&#91;sp_BlitzFirst] @ExpertMode=1,@SinceStartup= 1
Author: ChatGPT
### **sp_BlitzFirst Execution Summary**
Executing `&#91;dbo].&#91;sp_BlitzFirst] @ExpertMode=1, @SinceStartup=1` provides **a real-time performance snapshot of SQL Server**, focusing on **resource usage, active queries, and wait statistics**. The `@ExpertMode=1` parameter enables **detailed diagnostics**, while `@SinceStartup=1` retrieves **cumulative performance metrics since SQL Server last restarted**.
### **Key Information in the Output:**
1. **SQL Server Workload & Performance Overview**
- Shows **CPU, memory, disk, and I/O usage trends since the server last restarted**.
- Reports **buffer pool usage, page life expectancy (PLE), and checkpoint activity**.
2. **Active Wait Statistics & Bottlenecks**
- Displays **top wait types** affecting SQL Server since startup.
- Helps diagnose **CPU pressure, memory contention, disk bottlenecks, and blocking issues**.
3. **Top Running Queries & Resource Consumption**
- Identifies **queries consuming the most CPU, memory, or I/O since startup**.
- Highlights **queries suffering from long execution times, recompilation, or excessive reads/writes**.
4. **Blocking & Deadlock Analysis**
- Reports **queries currently causing blocking** and how long they have been running.
- Detects **recent deadlocks and transaction contention issues**.
5. **Index & TempDB Insights**
- Checks **TempDB contention, page allocation rates, and TempDB wait stats**.
- Reports **missing or heavily used indexes** that might need tuning.
6. **SQL Agent & Job Performance** *(if applicable)*
- Detects **jobs consuming high CPU or running for extended periods**.
- Helps identify **scheduling conflicts or job failures impacting performance**.
### **Advantages of Running sp_BlitzFirst @ExpertMode=1, @SinceStartup=1**
- **Provides a high-level and detailed breakdown of SQL Server performance since startup**.
- **Helps identify CPU, memory, and I/O bottlenecks affecting system performance**.
- **Finds the most expensive queries and recommends tuning opportunities**.
- **Detects long-running blocking transactions and deadlocks** for resolution.
- **Gives instant insights into TempDB health and SQL Agent job efficiency**.
Would you like **help interpreting specific performance bottlenecks** from your `sp_BlitzFirst` results, such as **high CPU usage, slow queries, or blocking issues**?
Author: user
EXEC sp_BlitzFirst
Author: ChatGPT
### **sp_BlitzFirst Execution Summary**
Executing `sp_BlitzFirst` provides a **real-time performance snapshot of SQL Server**, capturing **resource usage, active queries, and system bottlenecks** at the moment of execution. Unlike `@SinceStartup=1`, which gathers cumulative metrics, running `sp_BlitzFirst` alone focuses on **what is happening right now**.
### **Key Information in the Output:**
1. **Current CPU, Memory, and I/O Usage**
- Displays **current CPU consumption, available memory, and disk activity**.
- Helps identify **whether SQL Server is experiencing high CPU usage or I/O bottlenecks**.
2. **Top Running Queries & Query Bottlenecks**
- Identifies **queries currently consuming the most resources (CPU, reads, writes, execution time)**.
- Highlights **queries causing blocking or suffering from poor execution plans**.
3. **Active Wait Statistics**
- Shows the **current top wait types**, helping diagnose **memory pressure, CPU contention, or I/O slowdowns**.
- Helps identify whether waits are related to **locking, parallelism, disk latency, or network delays**.
4. **Blocking & Deadlock Detection**
- Reports **queries currently causing blocking and their impact on other sessions**.
- Helps diagnose **transaction contention and deadlocks in real-time**.
5. **TempDB & Resource Monitor Health**
- Checks for **TempDB contention**, including **PFS, GAM, and SGAM waits**.
- Reports on **memory grants and available worker threads**, helping detect **resource exhaustion**.
6. **SQL Agent & Job Performance** *(if applicable)*
- Identifies **long-running SQL Agent jobs** that may be affecting performance.
- Helps troubleshoot **job failures or scheduling conflicts**.
### **Advantages of Running sp_BlitzFirst**
- **Provides a live snapshot of SQL Server???s performance at the time of execution**.
- **Helps diagnose real-time blocking, deadlocks, and slow-running queries**.
- **Identifies resource-intensive queries and potential tuning opportunities**.
- **Assists in finding CPU, memory, and disk bottlenecks instantly**.
- **Quickly pinpoints SQL Server health issues for immediate action**.
Would you like help **analyzing specific real-time performance issues** from your `sp_BlitzFirst` results, such as **blocking transactions, CPU spikes, or TempDB contention**?
Author: user
EXEC sp_Blitz @CheckUserDatabaseObjects = 0;
Author: ChatGPT
### **sp_Blitz Execution Summary**
Executing `sp_Blitz @CheckUserDatabaseObjects = 0` runs a **comprehensive SQL Server health check**, but **excludes user database objects**, focusing only on **server-wide configuration, security risks, and performance bottlenecks**.
### **Key Information in the Output:**
1. **Critical SQL Server Configuration Issues**
- Identifies **misconfigurations** that could affect **performance, security, or availability**.
- Highlights **problems like out-of-date SQL Server versions, database corruption risks, or excessive memory grants**.
2. **Security & Compliance Risks**
- Checks for **publicly accessible logins, weak passwords, and overprivileged accounts**.
- Detects **SQL Server Agent jobs running as `sa` or security risks from linked servers**.
3. **Wait Statistics & Performance Bottlenecks**
- Displays **the most common wait types affecting system performance**.
- Helps diagnose **CPU pressure, memory constraints, or I/O bottlenecks**.
4. **SQL Server Configuration & Best Practices**
- Checks for **missing best practices** in **MaxDOP, cost threshold for parallelism, and memory settings**.
- Flags **misconfigured TempDB settings** or **autogrowth issues in databases**.
5. **Indexing & Query Performance Insights** *(User Objects Excluded)*
- Since `@CheckUserDatabaseObjects = 0`, it **skips missing or unused indexes in user databases**.
- Focuses instead on **server-wide indexing issues**, such as **missing TempDB indexes**.
6. **SQL Agent & Backup Monitoring**
- Flags **failing SQL Agent jobs, missing database backups, or outdated recovery models**.
- Helps ensure **that disaster recovery plans are up-to-date**.
### **Advantages of Running `sp_Blitz @CheckUserDatabaseObjects = 0`**
- **Fastest way to analyze SQL Server health without scanning every user database.**
- **Identifies system-wide risks** related to **security, performance, and configuration**.
- **Useful for DBAs focusing on infrastructure-level SQL Server optimization**.
- **Great for compliance checks** where **database-specific details aren't needed**.
Would you like assistance **interpreting findings, optimizing configurations, or addressing security risks** from your `sp_Blitz` results?
Author: user
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--desc: if an extended property for the cname was created, list it here.
--verify the cname
DECLARE @ServerName VARCHAR(128)
SELECT
@ServerName = CONVERT(VARCHAR(128),prpz.value) --AS CName
FROM &#91;master].sys.&#91;extended_properties] prpz
WHERE prpz.Class=0
AND prpz.name = 'Cname'
SELECT @ServerName = ISNULL(@ServerName,CONVERT(VARCHAR(128),@@SERVERNAME))
IF OBJECT_ID('tempdb..&#91;#xpdrives]') IS NOT NULL
DROP TABLE &#91;#xpdrives]
CREATE TABLE #xpdrives(DriveLetter VARCHAR(30),MBFree INT, GBFree AS CONVERT(VARCHAR(30),CONVERT(DECIMAL(6,0),(MBFree/1000.0))) + 'GB' )
INSERT INTO #xpdrives(DriveLetter,MBFree)
EXECUTE xp_fixeddrives
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results(ResultsId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,ResultsItem VARCHAR(50),ResultsValue varchar(1000) )
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'CName' AS Item, @ServerName AS Value
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'AppEmailGroup',ISNULL((SELECT CONVERT(VARCHAR(128),&#91;value]) FROM master.sys.&#91;extended_properties] WHERE &#91;major_id]=0 AND name = 'AppEmailGroup'),'')
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'ServerName' AS Item,CONVERT(VARCHAR(128),@@ServerName) AS Value
IF OBJECT_ID('tempdb.&#91;dbo].&#91;#CPUData]') IS NOT NULL
DROP TABLE &#91;dbo].&#91;#CPUData]
CREATE TABLE #CPUData (cpu_count int, hyperthread_ratio int,physicialCPUSockets int,PhysicalMemoryMB money,VirtualMachine varchar(50),VirtualMachineType varchar(50))
--SQL2014 and above, column is different
--select Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4) + '.' + Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
IF CONVERT(DECIMAL(5,2),PARSENAME(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)
+ '.'
+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
)>=11.0
BEGIN
INSERT INTO #CPUData(&#91;cpu_count],&#91;hyperthread_ratio],&#91;physicialCPUSockets],&#91;PhysicalMemoryMB],&#91;VirtualMachine],&#91;VirtualMachineType])
EXEC(
'SELECT
s.cpu_count ,
s.hyperthread_ratio ,
s.cpu_count / s.hyperthread_ratio As NumberofCores,
s.physical_memory_kb/1000.0 As MemoryinMb,
s.virtual_machine_type_desc,
CASE WHEN s.virtual_machine_type > 0 THEN ''Virtual'' ELSE '''' END As IsVirtual--1 = hypervisor, 2 = other type of virtual
FROM sys.dm_os_sys_info s');
END
ELSE
BEGIN
INSERT INTO #CPUData(&#91;cpu_count],&#91;hyperthread_ratio],&#91;physicialCPUSockets],&#91;PhysicalMemoryMB],&#91;VirtualMachine],&#91;VirtualMachineType])
EXEC(
'SELECT
s.cpu_count ,
s.hyperthread_ratio ,
s.cpu_count / s.hyperthread_ratio As NumberofCores,
s.physical_memory_in_bytes/1048576.0 As MemoryinMb,
'''' AS virtual_machine_type_desc,
'''' As IsVirtual
FROM sys.dm_os_sys_info s');
END
IF EXISTS (SELECT * FROM sys.all_columns WHERE object_name(object_id) ='dm_os_sys_info' AND name = 'virtual_machine_type')
BEGIN
EXEC(
'UPDATE MyTarget
SET VirtualMachine = CASE WHEN s.virtual_machine_type > 0 THEN ''Virtual'' ELSE '''' END
--SELECT CASE WHEN s.virtual_machine_type > 0 THEN ''Virtual'' ELSE '''' END --1 = hypervisor, 2 = other type of virtual
FROM sys.dm_os_sys_info s
CROSS JOIN #CPUData MyTarget');
END
UPDATE MyTarget SET &#91;VirtualMachine] = 'Virtual',&#91;VirtualMachineType] = 1 FROM #CPUData MyTarget WHERE CONVERT(VARCHAR(4000),@@VERSION) LIKE'%(VM)%'
UPDATE MyTarget SET &#91;VirtualMachine] = 'SQL2005/Cannot Determine',&#91;VirtualMachineType] = 0 FROM #CPUData MyTarget WHERE ISNULL(PARSENAME(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4),'') + '.' + ISNULL(Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3),'')='9.00'
--SELECT ISNULL(PARSENAME(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4),'') + '.' + ISNULL(Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3),'')
;WITH MyCTE(&#91;InternalName],&#91;HexValue],&#91;OSName])
AS
(
SELECT 'PRODUCT_BUSINESS',0x00000006,'Business' UNION ALL
SELECT 'PRODUCT_BUSINESS_N',0x00000010,'Business N' UNION ALL
SELECT 'PRODUCT_CLUSTER_SERVER',0x00000012,'HPC Edition' UNION ALL
SELECT 'PRODUCT_CLUSTER_SERVER_V',0x00000040,'Server Hyper Core V' UNION ALL
SELECT 'PRODUCT_CORE',0x00000065,'Windows 10 Home' UNION ALL
SELECT 'PRODUCT_CORE_COUNTRYSPECIFIC',0x00000063,'Windows 10 Home China' UNION ALL
SELECT 'PRODUCT_CORE_N',0x00000062,'Windows 10 Home N' UNION ALL
SELECT 'PRODUCT_CORE_SINGLELANGUAGE',0x00000064,'Windows 10 Home Single Language' UNION ALL
SELECT 'PRODUCT_DATACENTER_EVALUATION_SERVER',0x00000050,'Server Datacenter (evaluation installation)' UNION ALL
SELECT 'PRODUCT_DATACENTER_A_SERVER_CORE',0x00000091,'Server Datacenter, Semi-Annual Channel (core installation)' UNION ALL
SELECT 'PRODUCT_STANDARD_A_SERVER_CORE',0x00000092,'Server Standard, Semi-Annual Channel (core installation)' UNION ALL
SELECT 'PRODUCT_DATACENTER_SERVER',0x00000008,'Server Datacenter Edition(full installation)' UNION ALL
SELECT 'PRODUCT_DATACENTER_SERVER_CORE',0x0000000C,'Server Datacenter (core installation, Windows Server 2008 R2 and earlier)' UNION ALL
SELECT 'PRODUCT_DATACENTER_SERVER_CORE_V',0x00000027,'Server Datacenter without Hyper-V (core installation)' UNION ALL
SELECT 'PRODUCT_DATACENTER_SERVER_V',0x00000025,'Server Datacenter without Hyper-V (full installation)' UNION ALL
SELECT 'PRODUCT_EDUCATION',0x00000079,'Windows 10 Education' UNION ALL
SELECT 'PRODUCT_EDUCATION_N',0x0000007A,'Windows 10 Education N' UNION ALL
SELECT 'PRODUCT_ENTERPRISE',0x00000004,'Windows 10 Enterprise' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_E',0x00000046,'Windows 10 Enterprise E' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_EVALUATION',0x00000048,'Windows 10 Enterprise Evaluation' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_N',0x0000001B,'Windows 10 Enterprise N' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_N_EVALUATION',0x00000054,'Windows 10 Enterprise N Evaluation' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_S',0x0000007D,'Windows 10 Enterprise 2015 LTSB' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_S_EVALUATION',0x00000081,'Windows 10 Enterprise 2015 LTSB Evaluation' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_S_N',0x0000007E,'Windows 10 Enterprise 2015 LTSB N' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_S_N_EVALUATION',0x00000082,'Windows 10 Enterprise 2015 LTSB N Evaluation' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_SERVER',0x0000000A,'Server Enterprise (full installation)' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_SERVER_CORE',0x0000000E,'Server Enterprise (core installation)' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_SERVER_CORE_V',0x00000029,'Server Enterprise without Hyper-V (core installation)' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_SERVER_IA64',0x0000000F,'Server Enterprise for Itanium-based Systems' UNION ALL
SELECT 'PRODUCT_ENTERPRISE_SERVER_V',0x00000026,'Server Enterprise without Hyper-V (full installation)' UNION ALL
SELECT 'PRODUCT_ESSENTIALBUSINESS_SERVER_ADDL',0x0000003C,'Windows Essential Server Solution Additional' UNION ALL
SELECT 'PRODUCT_ESSENTIALBUSINESS_SERVER_ADDLSVC',0x0000003E,'Windows Essential Server Solution Additional SVC' UNION ALL
SELECT 'PRODUCT_ESSENTIALBUSINESS_SERVER_MGMT',0x0000003B,'Windows Essential Server Solution Management' UNION ALL
SELECT 'PRODUCT_ESSENTIALBUSINESS_SERVER_MGMTSVC',0x0000003D,'Windows Essential Server Solution Management SVC' UNION ALL
SELECT 'PRODUCT_HOME_BASIC',0x00000002,'Home Basic' UNION ALL
SELECT 'PRODUCT_HOME_BASIC_E',0x00000043,'Not supported' UNION ALL
SELECT 'PRODUCT_HOME_BASIC_N',0x00000005,'Home Basic N' UNION ALL
SELECT 'PRODUCT_HOME_PREMIUM',0x00000003,'Home Premium' UNION ALL
SELECT 'PRODUCT_HOME_PREMIUM_E',0x00000044,'Not supported' UNION ALL
SELECT 'PRODUCT_HOME_PREMIUM_N',0x0000001A,'Home Premium N' UNION ALL
SELECT 'PRODUCT_HOME_PREMIUM_SERVER',0x00000022,'Windows Home Server 2011' UNION ALL
SELECT 'PRODUCT_HOME_SERVER',0x00000013,'Windows Storage Server 2008 R2 Essentials' UNION ALL
SELECT 'PRODUCT_HYPERV',0x0000002A,'Microsoft Hyper-V Server' UNION ALL
SELECT 'PRODUCT_IOTUAP',0x0000007B,'Windows 10 IoT Core' UNION ALL
SELECT 'PRODUCT_IOTUAPCOMMERCIAL',0x00000083,'Windows 10 IoT Core Commercial' UNION ALL
SELECT 'PRODUCT_MEDIUMBUSINESS_SERVER_MANAGEMENT',0x0000001E,'Windows Essential Business Server Management Server' UNION ALL
SELECT 'PRODUCT_MEDIUMBUSINESS_SERVER_MESSAGING',0x00000020,'Windows Essential Business Server Messaging Server' UNION ALL
SELECT 'PRODUCT_MEDIUMBUSINESS_SERVER_SECURITY',0x0000001F,'Windows Essential Business Server Security Server' UNION ALL
SELECT 'PRODUCT_MOBILE_CORE',0x00000068,'Windows 10 Mobile' UNION ALL
SELECT 'PRODUCT_MOBILE_ENTERPRISE',0x00000085,'Windows 10 Mobile Enterprise' UNION ALL
SELECT 'PRODUCT_MULTIPOINT_PREMIUM_SERVER',0x0000004D,'Windows MultiPoint Server Premium (full installation)' UNION ALL
SELECT 'PRODUCT_MULTIPOINT_STANDARD_SERVER',0x0000004C,'Windows MultiPoint Server Standard (full installation)' UNION ALL
SELECT 'PRODUCT_PRO_WORKSTATION',0x000000A1,'Windows 10 Pro for Workstations' UNION ALL
SELECT 'PRODUCT_PRO_WORKSTATION_N',0x000000A2,'Windows 10 Pro for Workstations N' UNION ALL
SELECT 'PRODUCT_PROFESSIONAL',0x00000030,'Windows 10 Pro' UNION ALL
SELECT 'PRODUCT_PROFESSIONAL_E',0x00000045,'Not supported' UNION ALL
SELECT 'PRODUCT_PROFESSIONAL_N',0x00000031,'Windows 10 Pro N' UNION ALL
SELECT 'PRODUCT_PROFESSIONAL_WMC',0x00000067,'Professional with Media Center' UNION ALL
SELECT 'PRODUCT_SB_SOLUTION_SERVER',0x00000032,'Windows Small Business Server 2011 Essentials' UNION ALL
SELECT 'PRODUCT_SB_SOLUTION_SERVER_EM',0x00000036,'Server For SB Solutions EM' UNION ALL
SELECT 'PRODUCT_SERVER_FOR_SB_SOLUTIONS',0x00000033,'Server For SB Solutions' UNION ALL
SELECT 'PRODUCT_SERVER_FOR_SB_SOLUTIONS_EM',0x00000037,'Server For SB Solutions EM' UNION ALL
SELECT 'PRODUCT_SERVER_FOR_SMALLBUSINESS',0x00000018,'Windows Server 2008 for Windows Essential Server Solutions' UNION ALL
SELECT 'PRODUCT_SERVER_FOR_SMALLBUSINESS_V',0x00000023,'Windows Server 2008 without Hyper-V for Windows Essential Server Solutions' UNION ALL
SELECT 'PRODUCT_SERVER_FOUNDATION',0x00000021,'Server Foundation' UNION ALL
SELECT 'PRODUCT_SMALLBUSINESS_SERVER',0x00000009,'Windows Small Business Server' UNION ALL
SELECT 'PRODUCT_SMALLBUSINESS_SERVER_PREMIUM',0x00000019,'Small Business Server Premium' UNION ALL
SELECT 'PRODUCT_SMALLBUSINESS_SERVER_PREMIUM_CORE',0x0000003F,'Small Business Server Premium (core installation)' UNION ALL
SELECT 'PRODUCT_SOLUTION_EMBEDDEDSERVER',0x00000038,'Windows MultiPoint Server' UNION ALL
SELECT 'PRODUCT_STANDARD_EVALUATION_SERVER',0x0000004F,'Server Standard (evaluation installation)' UNION ALL
SELECT 'PRODUCT_STANDARD_SERVER',0x00000007,'Server Standard (full installation. For Server Core installations of Windows Server 2012 and later, use the method, Determining whether Server Core is running.)' UNION ALL
SELECT 'PRODUCT_STANDARD_SERVER_CORE',0x0000000D,'Server Standard (core installation, Windows Server 2008 R2 and earlier)' UNION ALL
SELECT 'PRODUCT_STANDARD_SERVER_CORE_V',0x00000028,'Server Standard without Hyper-V (core installation)' UNION ALL
SELECT 'PRODUCT_STANDARD_SERVER_V',0x00000024,'Server Standard without Hyper-V' UNION ALL
SELECT 'PRODUCT_STANDARD_SERVER_SOLUTIONS',0x00000034,'Server Solutions Premium' UNION ALL
SELECT 'PRODUCT_STANDARD_SERVER_SOLUTIONS_CORE',0x00000035,'Server Solutions Premium (core installation)' UNION ALL
SELECT 'PRODUCT_STARTER',0x0000000B,'Starter' UNION ALL
SELECT 'PRODUCT_STARTER_E',0x00000042,'Not supported' UNION ALL
SELECT 'PRODUCT_STARTER_N',0x0000002F,'Starter N' UNION ALL
SELECT 'PRODUCT_STORAGE_ENTERPRISE_SERVER',0x00000017,'Storage Server Enterprise' UNION ALL
SELECT 'PRODUCT_STORAGE_ENTERPRISE_SERVER_CORE',0x0000002E,'Storage Server Enterprise (core installation)' UNION ALL
SELECT 'PRODUCT_STORAGE_EXPRESS_SERVER',0x00000014,'Storage Server Express' UNION ALL
SELECT 'PRODUCT_STORAGE_EXPRESS_SERVER_CORE',0x0000002B,'Storage Server Express (core installation)' UNION ALL
SELECT 'PRODUCT_STORAGE_STANDARD_EVALUATION_SERVER',0x00000060,'Storage Server Standard (evaluation installation)' UNION ALL
SELECT 'PRODUCT_STORAGE_STANDARD_SERVER',0x00000015,'Storage Server Standard' UNION ALL
SELECT 'PRODUCT_STORAGE_STANDARD_SERVER_CORE',0x0000002C,'Storage Server Standard (core installation)' UNION ALL
SELECT 'PRODUCT_STORAGE_WORKGROUP_EVALUATION_SERVER',0x0000005F,'Storage Server Workgroup (evaluation installation)' UNION ALL
SELECT 'PRODUCT_STORAGE_WORKGROUP_SERVER',0x00000016,'Storage Server Workgroup' UNION ALL
SELECT 'PRODUCT_STORAGE_WORKGROUP_SERVER_CORE',0x0000002D,'Storage Server Workgroup (core installation)' UNION ALL
SELECT 'PRODUCT_ULTIMATE',0x00000001,'Ultimate' UNION ALL
SELECT 'PRODUCT_ULTIMATE_E',0x00000047,'Not supported' UNION ALL
SELECT 'PRODUCT_ULTIMATE_N',0x0000001C,'Ultimate N' UNION ALL
SELECT 'PRODUCT_UNDEFINED',0x00000000,'An unknown product' UNION ALL
SELECT 'PRODUCT_WEB_SERVER',0x00000011,'Web Server (full installation)' UNION ALL
SELECT 'PRODUCT_WEB_SERVER_CORE',0x0000001D,'Web Server (core installation)'
)
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'WindowsOperatingSystem',MyCTE.&#91;OSName] FROM sys.dm_os_windows_info osinfo
INNER JOIN MyCTE ON osinfo.&#91;windows_sku] = MyCTE.&#91;HexValue]
--INSERT INTO #Results(ResultsItem,ResultsValue)
--SELECT 'WindowsOperatingSystem', CASE
-- WHEN CHARINDEX (' 11.0',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2019'
-- WHEN CHARINDEX (' 10.0',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2016'
-- WHEN CHARINDEX ('NT 6.3',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2013'
-- WHEN CHARINDEX ('Standard 6.3',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2012R2'
-- WHEN CHARINDEX ('Standard 6.2',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2012'
-- WHEN CHARINDEX ('NT 6.2',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2012'
-- WHEN CHARINDEX ('NT 6.1',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2008R2'
-- WHEN CHARINDEX ('NT 6.0',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2008'
-- WHEN CHARINDEX ('NT 5.2',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2003R2'
-- WHEN CHARINDEX ('NT 5.1',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2003'
-- WHEN CHARINDEX ('NT 5.0',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server 2000'
-- WHEN CHARINDEX ('NT 4.0',CONVERT(varchar(256),@@VERSION)) > 0 THEN 'Windows Server NT 4.0'
-- ELSE 'Not Detected?'
-- END + ' -- ' + CONVERT(VARCHAR(20),CONVERT(MONEY,s.PhysicalMemoryMB),1) + ' MB,'
--+ CASE WHEN s.VirtualMachine='' THEN 'Physical Machine ' ELSE s.VirtualMachine + ' Machine: ' END
--+ CONVERT(VARCHAR(3),s.cpu_count) + ' Core /'
--+ CONVERT(VARCHAR(3),s.physicialCPUSockets) + ' Sockets' AS WindowsOperatingSystem
-- FROM #CPUData s
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'Memory', s.PhysicalMemoryMB FROM #CPUData s ;
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'CPUCoreCounts', CONVERT(VARCHAR(3),s.cpu_count) + ' Core /'
+ CONVERT(VARCHAR(3),s.physicialCPUSockets) + ' Sockets' AS CPUCoreCounts
FROM #CPUData s
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'DrivesAndFreeSpace',s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT '; ' + DriveLetter + ':\' + GBFree
FROM &#91;#xpdrives]
ORDER BY DriveLetter
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,2,'')
) s
;WITH Delimiters(sDelimiter,sInputList)
AS
(
SELECT CHAR(10) AS sDelimiter,
REPLACE(CONVERT(VARCHAR(8000),@@VERSION),CHAR(9),' ') AS sInputList
),
E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1), -- 10 or 10E01 rows
E02(N) AS (SELECT 1 FROM E01 a, E01 b), -- 100 or 10E02 rows
E04(N) AS (SELECT 1 FROM E02 a, E02 b), -- 10,000 or 10E04 rows
E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000 or 10E08 rows
E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need
Tally(number) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E16)
,
FormattedVersion(item)
AS
( SELECT
SUBSTRING(sDelimiter + sInputList + sDelimiter, &#91;Tally].&#91;number] + LEN(sDelimiter),
CHARINDEX(sDelimiter, sDelimiter + sInputList + sDelimiter, &#91;Tally].&#91;number] + LEN(sDelimiter)) - &#91;Tally].&#91;number] -LEN(sDelimiter))AS item
--select *
FROM Tally CROSS APPLY Delimiters
WHERE &#91;Tally].&#91;number] <= LEN(sDelimiter + sInputList + sDelimiter) - LEN(sDelimiter)
AND SUBSTRING(sDelimiter + sInputList + sDelimiter, &#91;Tally].&#91;number], LEN(sDelimiter)) = sDelimiter
)
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'SQLServerVersion',
ISNULL(CASE ISNULL(PARSENAME(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4),'')
+ '.'
+ ISNULL(Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3),'')
WHEN '.8' THEN 'SQL 2000'
WHEN '9.00' THEN 'SQL 2005'
WHEN '10.0' THEN 'SQL 2008'
WHEN '10.50' THEN 'SQL 2008R2'
WHEN '11.0' THEN 'SQL 2012'
WHEN '12.0' THEN 'SQL 2014'
WHEN '13.0' THEN 'SQL 2016'
WHEN '14.0' THEN 'SQL 2017'
WHEN '15.0' THEN 'SQL 2019'
WHEN '16.0' THEN 'SQL 2022'
WHEN '17.0' THEN 'SQL 2025'
ELSE ISNULL(PARSENAME(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4),'')
+ '.'
+ ISNULL(Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3),'')
END,'??') + '::' +item
FROM FormattedVersion
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'SQL Edition',CONVERT(VARCHAR(128), Serverproperty('Edition')) AS Edition
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'TCPIP Port',MAX(local_tcp_port) FROM sys.dm_exec_connections WHERE net_transport='TCP'
--DBRecap
;WITH AllDatabases
AS
(
SELECT &#91;t].&#91;database_id],&#91;t].&#91;name] AS TheDatabase,&#91;t].&#91;recovery_model_desc],&#91;t].&#91;user_access_desc],&#91;t].&#91;state_desc],t.&#91;is_read_only] FROM &#91;master].&#91;sys].&#91;databases] t WHERE &#91;t].&#91;name] NOT IN('master','tempdb','model','msdb')
),
myCTE
AS
(
SELECT
&#91;AllDatabases].&#91;TheDatabase],
&#91;AllDatabases].&#91;recovery_model_desc],
&#91;AllDatabases].&#91;user_access_desc],
&#91;AllDatabases].&#91;state_desc],
CONVERT(INT,&#91;AllDatabases].&#91;is_read_only]) AS &#91;is_read_only],
&#91;statz].&#91;last_user_seek],
&#91;statz].&#91;last_user_scan],
&#91;statz].&#91;last_user_lookup],
&#91;statz].&#91;last_user_update]
FROM AllDatabases
LEFT OUTER JOIN &#91;sys].&#91;dm_db_index_usage_stats] statz
ON &#91;AllDatabases].&#91;database_id] = &#91;statz].&#91;database_id]
),NearlyDone
AS
(
SELECT
ServerRestartedDate = (SELECT CREATE_DATE FROM sys.databases where name='tempdb'),
&#91;x].&#91;TheDatabase],
MAX(&#91;x].&#91;recovery_model_desc]) AS recovery_model_desc,
MAX(&#91;x].&#91;user_access_desc]) AS user_access_desc,
MAX(&#91;x].&#91;state_desc]) AS state_desc,
MAX(&#91;x].&#91;is_read_only]) AS is_read_only,
MAX(&#91;x].&#91;last_read]) AS last_read,
MAX(&#91;x].&#91;last_write]) AS last_write
FROM
(
SELECT &#91;myCTE].&#91;TheDatabase],&#91;myCTE].&#91;recovery_model_desc],&#91;myCTE].is_read_only,&#91;myCTE].&#91;user_access_desc],&#91;myCTE].&#91;state_desc],&#91;myCTE].&#91;last_user_seek] AS last_read, NULL AS last_write FROM myCTE
UNION ALL
SELECT &#91;myCTE].&#91;TheDatabase],NULL,NULL,NULL,NULL,&#91;myCTE].&#91;last_user_scan], NULL FROM myCTE
UNION ALL
SELECT &#91;myCTE].&#91;TheDatabase],NULL,NULL,NULL,NULL,&#91;myCTE].&#91;last_user_lookup], NULL FROM myCTE
UNION ALL
SELECT &#91;myCTE].&#91;TheDatabase],NULL,NULL,NULL,NULL,NULL, &#91;myCTE].&#91;last_user_update] FROM myCTE
) AS x
GROUP BY TheDatabase
)
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'Databases',
ISNULL('TotalDBs = '
+ CONVERT(VARCHAR(5),COUNT(*))
+ ', OffLine='
+ CONVERT(VARCHAR(5),SUM(CASE WHEN NearlyDone.state_desc <> 'ONLINE' THEN 1 ELSE 0 END))
+ ', ReadOnly='
+ CONVERT(VARCHAR(5),SUM(CASE WHEN NearlyDone.&#91;is_read_only] = 1 THEN 1 ELSE 0 END))
+', Unused='
+ CONVERT(VARCHAR(5),SUM(CASE WHEN NearlyDone.&#91;is_read_only] = 0 AND NearlyDone.&#91;last_read] IS NULL AND NearlyDone.&#91;last_write] IS NULL THEN 1 ELSE 0 END))
+ ' Since '
+ CONVERT(VARCHAR(40),MAX(NearlyDone.ServerRestartedDate),120),'No User Databases')
FROM NearlyDone
WHERE &#91;TheDatabase] NOT IN('master','model','tempdb','msdb')
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'Database Size', 'Databases Are ' + CONVERT(VARCHAR(10),CONVERT(decimal(18,2),SUM(size*8.0) / (1024.0*1024.0))) + 'GB in size' FROM sysaltfiles WHERE dbid > 4
DECLARE @SQLMemory INT;
DECLARE @PhysicalMemoryMB int;
SELECT @SQLMemory = CONVERT(int,value) FROM sys.&#91;configurations] WHERE name = 'max server memory (MB)'
SELECT @PhysicalMemoryMB = s.PhysicalMemoryMB FROM #CPUData s ;
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'Memory','SQL MaxMemory: ' + CONVERT(VARCHAR,@SQLMemory / 1024) + ' GB | TotalMemory: ' + CONVERT(VARCHAR,@PhysicalMemoryMB / 1024 )+ ' GB'
IF EXISTS(SELECT * FROM sys.all_objects WHERE name = 'dm_os_buffer_pool_extension_configuration')
EXECUTE('INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT ''BufferPoolExtensions'', CONVERT(VARCHAR(30),ISNULL((cnfgz.current_size_in_kb / (1024*1024)),0)) + '' GB''
FROM sys.dm_os_buffer_pool_extension_configuration cnfgz
GROUP BY cnfgz.current_size_in_kb')
ELSE INSERT INTO #Results(ResultsItem,ResultsValue) SELECT 'BufferPoolExtensions','Buffer Pool Not an option due to SQL Version.'
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT 'SQLLogins',
ISNULL('TotalSQLLogins = ' + CONVERT(VARCHAR(5),COUNT(1))
+ ', Disabled = '
+ CONVERT(VARCHAR(5),SUM (CASE WHEN prnz.is_disabled =1 THEN 1 ELSE 0 END))
+ ', PasswordReset90DayViolation='
+ CONVERT(VARCHAR(5),ISNULL(SUM (CASE WHEN prnz.is_policy_checked=0 AND LOGINPROPERTY(prnz.name, 'PasswordLastSetTime') < DATEADD(dd,-91,GETDATE()) THEN 1 ELSE 0 END),0)),'No SQL Logins')
FROM sys.sql_logins prnz WHERE prnz.type_desc='SQL_Login'
AND prnz.name NOT LIKE '##%'
AND prnz.principal_id > 1
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT CONVERT(VARCHAR(128), name),CONVERT(VARCHAR(128), name) + ' = ' + CONVERT(VARCHAR(30),value) FROM sys.configurations WHERE name IN ('remote admin connections','optimize for ad hoc workloads','backup compression default','affinity mask','cost threshold for parallelism','max degree of parallelism')
ORDER BY name
INSERT INTO #Results(ResultsItem,ResultsValue)
SELECT servicename,service_account FROM sys.dm_server_services
SELECT * FROM #Results
Author: ChatGPT
### **SQL Server System Health & Configuration Summary**
This script gathers **critical system-level information** about the SQL Server instance, **hardware, performance, security, and database state**. It provides a **consolidated diagnostic view** by extracting details on **server identity, CPU, memory, disk space, configurations, and logins**.
---
### **Key Information in the Output:**
#### **1. Server Identity & Configuration**
- Extracts **SQL Server name, CNAME (if extended properties exist), edition, and version**.
- Determines **whether the server is a physical machine or a virtual instance**.
#### **2. Hardware & System Resources**
- Captures **CPU socket count, core count, hyper-threading ratio, and total memory available**.
- Identifies **whether the server is running on a virtualized platform (e.g., VMware, Hyper-V)**.
- Detects **free disk space on all drives**.
#### **3. Operating System Details**
- Retrieves the **Windows Server version** and **edition** using system tables.
- Helps determine **whether the OS is supported and up to date**.
#### **4. SQL Server Performance & Memory Configuration**
- Extracts the **configured SQL Server max memory setting** and **physical memory available**.
- Flags **memory misconfigurations that could lead to performance bottlenecks**.
- Captures **buffer pool extension details (if enabled) for hybrid memory optimization**.
#### **5. Database Summary & Usage Analysis**
- Lists **all databases with their recovery models, user access settings, and state (online, offline, read-only, etc.)**.
- Detects **unused databases by checking the last read/write timestamps**.
- Estimates **total database size and free space available**.
#### **6. Security & Login Analysis**
- Identifies **total SQL Server logins, disabled logins, and accounts violating password reset policies**.
- Flags **potential security risks like unused or unmonitored logins**.
#### **7. SQL Server Configuration Best Practices Check**
- Extracts values for **critical SQL Server settings**, including:
- **Max Degree of Parallelism (MAXDOP)**
- **Cost Threshold for Parallelism**
- **Backup Compression Default**
- **Remote Admin Connections**
- **Affinity Masking (CPU Binding)**
#### **8. SQL Agent & Service Accounts**
- Lists **SQL Server services and their associated service accounts**.
- Helps detect **service account misconfigurations or security risks**.
---
### **Advantages of Running This Script**
??? **Provides a one-stop summary of SQL Server health and configuration**.
??? **Helps diagnose memory misconfigurations and performance bottlenecks**.
??? **Identifies database states, unused databases, and read/write activity**.
??? **Detects potential security risks in SQL Server logins and configurations**.
??? **Gathers system-level insights, including OS version, CPU, and available storage**.
Would you like **help interpreting findings, optimizing configurations, or addressing security risks** from your results? ????
Author: user
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
--#################################################################################################
-- Database currently uses Partitioning?
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#partitionInfo]') IS NOT NULL
DROP TABLE &#91;#partitionInfo]
CREATE TABLE &#91;#partitionInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL)
EXECUTE sp_MSforeachdb '
INSERT INTO &#91;#partitionInfo](&#91;DatabaseName],&#91;PropertyCount])
select ''?'' AS DatabaseName ,count(*)As PropertyCount
from sys.partitions
where partition_number > 1 '
--#################################################################################################
-- Replication Published Items
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#PublisherInfo]') IS NOT NULL
DROP TABLE &#91;#PublisherInfo]
CREATE TABLE &#91;#PublisherInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#PublisherInfo](&#91;DatabaseName],&#91;PropertyCount])
select name AS DatabaseName ,CASE WHEN is_published = 1 THEN 1 ELSE 0 END As PropertyCount
from sys.databases
EXECUTE sp_MSforeachdb '
IF EXISTS(SELECT * FROM &#91;?].sys.all_objects WHERE schema_id = 4 AND name = ''sysarticles'')
EXECUTE(''UPDATE &#91;#PublisherInfo] SET &#91;DetailCount] = COUNT(*) FROM &#91;?].sys.articles WHERE &#91;DatabaseName] = ''''?'''';'') '
--#################################################################################################
-- Replication Subscription Items
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#SubscriberInfo]') IS NOT NULL
DROP TABLE &#91;#SubscriberInfo]
CREATE TABLE &#91;#SubscriberInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#SubscriberInfo](&#91;DatabaseName],&#91;PropertyCount])
select name AS DatabaseName ,CASE WHEN is_subscribed = 1 THEN 1 ELSE 0 END As PropertyCount
from sys.databases
EXECUTE sp_MSforeachdb
'
UPDATE &#91;#SubscriberInfo] SET &#91;DetailCount] = (SELECT COUNT(*) FROM &#91;?].sys.tables where is_replicated = 1)
WHERE &#91;DatabaseName] = ''?''
'
--#################################################################################################
--Log Shipping-Primary
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#LogShippingPrimaryInfo]') IS NOT NULL
DROP TABLE &#91;#LogShippingPrimaryInfo]
CREATE TABLE &#91;#LogShippingPrimaryInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;LogShippingPrimaryCount] INT NULL)
INSERT INTO &#91;#LogShippingPrimaryInfo](&#91;DatabaseName],&#91;LogShippingPrimaryCount])
SELECT primary_database, 1 AS LogShippingPrimaryCount FROM &#91;msdb].&#91;dbo].&#91;log_shipping_primary_databases]
--#################################################################################################
--Log Shipping-Secondary
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#LogShippingSecondaryInfo]') IS NOT NULL
DROP TABLE &#91;#LogShippingSecondaryInfo]
CREATE TABLE &#91;#LogShippingSecondaryInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#LogShippingSecondaryInfo](&#91;DatabaseName],&#91;PropertyCount])
select name AS DatabaseName ,CASE WHEN is_subscribed = 1 THEN 1 ELSE 0 END As PropertyCount
from sys.databases
EXECUTE sp_MSforeachdb
'
UPDATE &#91;#LogShippingSecondaryInfo] SET &#91;DetailCount] = (SELECT COUNT(*) FROM &#91;?].sys.tables where is_replicated = 1)
WHERE &#91;DatabaseName] = ''?''
'
--#################################################################################################
-- Change Data Capture
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#ChangeDataCapture]') IS NOT NULL
DROP TABLE &#91;#ChangeDataCapture]
CREATE TABLE &#91;#ChangeDataCapture] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#ChangeDataCapture](&#91;DatabaseName],&#91;PropertyCount])
select name AS DatabaseName ,CASE WHEN is_cdc_enabled = 1 THEN 1 ELSE 0 END As PropertyCount
from sys.databases
EXECUTE sp_MSforeachdb
'
UPDATE &#91;#ChangeDataCapture] SET &#91;DetailCount] = (SELECT COUNT(*) FROM &#91;?].sys.tables where is_tracked_by_cdc = 1)
WHERE &#91;DatabaseName] = ''?''
'
--#################################################################################################
-- Change Tracking
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#ChangeTracking]') IS NOT NULL
DROP TABLE &#91;#ChangeTracking]
CREATE TABLE &#91;#ChangeTracking] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
EXECUTE sp_MSforeachdb '
INSERT INTO &#91;#ChangeTracking](&#91;DatabaseName],&#91;PropertyCount])
select ''?'' AS DAtabaseName,Count(*) from &#91;?].sys.change_tracking_databases'
EXECUTE sp_MSforeachdb
'
UPDATE &#91;#ChangeTracking] SET &#91;DetailCount] = (SELECT COUNT(*) FROM &#91;?].sys.change_tracking_tables)
WHERE &#91;DatabaseName] = ''?''
'
--#################################################################################################
-- Always Encrypted
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#AlwaysEncrypted]') IS NOT NULL
DROP TABLE &#91;#AlwaysEncrypted]
CREATE TABLE &#91;#AlwaysEncrypted] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
EXECUTE sp_MSforeachdb '
INSERT INTO &#91;#AlwaysEncrypted](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
select ''?'' AS DAtabaseName,Count(*),Count(*) from &#91;?].sys.columns WHERE &#91;encryption_type] IS NOT NULL'
--#################################################################################################
-- Dynamic Data Masking
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#DynamicDataMasking]') IS NOT NULL
DROP TABLE &#91;#DynamicDataMasking]
CREATE TABLE &#91;#DynamicDataMasking] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
EXECUTE sp_MSforeachdb '
INSERT INTO &#91;#DynamicDataMasking](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
select ''?'' AS DAtabaseName,Count(*),Count(*) from &#91;?].sys.&#91;masked_columns]'
--#################################################################################################
-- Transparent Data Encryption
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#TransparentDataEncryption]') IS NOT NULL
DROP TABLE &#91;#TransparentDataEncryption]
CREATE TABLE &#91;#TransparentDataEncryption] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#TransparentDataEncryption](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
select name AS DAtabaseName,CASE WHEN is_encrypted = 1 THEN 1 ELSE 0 END,CASE WHEN is_encrypted = 1 THEN 1 ELSE 0 END from sys.databases
--#################################################################################################
-- Custom User Defined DataTypes
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#CustomUserDataType]') IS NOT NULL
DROP TABLE &#91;#CustomUserDataType]
CREATE TABLE &#91;#CustomUserDataType] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#CustomUserDataType](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
execute sp_MSforeachdb '
select ''?'' AS DatabaseName,0, COUNT(*) from &#91;?].sys.types WHERE is_user_defined = 1'
UPDATE &#91;#CustomUserDataType] SET &#91;PropertyCount] = 1 WHERE &#91;DetailCount] > 0 --select * from &#91;#CustomUserDataType]
DELETE FROM &#91;#CustomUserDataType] WHERE DatabaseName like 'ReportServer%' OR DatabaseName IN('SSISDB','master','model','msdb','tempdb')
--#################################################################################################
-- Is Always Availability
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#AAGInfo]') IS NOT NULL
DROP TABLE &#91;#AAGInfo]
CREATE TABLE &#91;#AAGInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#AAGInfo](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
SELECT d.name ,0,COUNT(hars.replica_id) FROM sys.databases d LEFT JOIN sys.dm_hadr_availability_replica_states hars ON d.replica_id = hars.replica_id GROUP BY d.name
--#################################################################################################
-- Is fci
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#fci]') IS NOT NULL
DROP TABLE &#91;#fci]
CREATE TABLE &#91;#fci] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#fci](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
SELECT 'master',0,0 WHERE NOT EXISTS( SELECT * FROM &#91;sys].&#91;dm_os_cluster_nodes]) UNION ALL
SELECT NodeName, 1,count(*) FROM &#91;sys].&#91;dm_os_cluster_nodes] group by NodeName
--#################################################################################################
-- Memory Optimized Tables
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#memoptimizedInfo]') IS NOT NULL
DROP TABLE &#91;#memoptimizedInfo]
CREATE TABLE &#91;#memoptimizedInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#memoptimizedInfo](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
EXECUTE sp_MSforeachdb '
INSERT INTO &#91;#memoptimizedInfo](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
SELECT ''?'',COUNT(*),COUNT(*) FROM &#91;?].sys.dm_db_xtp_table_memory_stats'
--#################################################################################################
-- Service Broker
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#servicebrokerInfo]') IS NOT NULL
DROP TABLE &#91;#servicebrokerInfo]
CREATE TABLE &#91;#servicebrokerInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#servicebrokerInfo](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
select name,is_broker_enabled,is_broker_enabled from sys.databases WHERE database_id > 4
--#################################################################################################
-- synonyms
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#synonyminfo]') IS NOT NULL
DROP TABLE &#91;#synonyminfo]
CREATE TABLE &#91;#synonyminfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
EXECUTE sp_MSforeachdb '
INSERT INTO &#91;#synonyminfo](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
select ''?'',count(*),count(*) from &#91;?].sys.synonyms'
--#################################################################################################
-- sequences
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#sequenceInfo]') IS NOT NULL
DROP TABLE &#91;#sequenceInfo]
CREATE TABLE &#91;#sequenceInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
EXECUTE sp_MSforeachdb '
INSERT INTO &#91;#sequenceInfo](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
select ''?'',count(*),count(*) from &#91;?].sys.sequences'
--#################################################################################################
-- SQL Policy'
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#policyInfo]') IS NOT NULL
DROP TABLE &#91;#policyInfo]
CREATE TABLE &#91;#policyInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#policyInfo](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
SELECT MIN(ISNULL(name,'None')),count(*),count(*)
FROM msdb.dbo.syspolicy_policies AS Pol
INNER JOIN msdb.dbo.syspolicy_policy_execution_history AS PolHist
ON &#91;PolHist].&#91;policy_id] = &#91;Pol].&#91;policy_id]
--#################################################################################################
-- SQL audit'
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#auditInfo]') IS NOT NULL
DROP TABLE &#91;#auditInfo]
CREATE TABLE &#91;#auditInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#auditInfo](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
SELECT MIN(ISNULL(name,'None')),count(*),count(*) FROM sys.server_audits
--#################################################################################################
--Untrusted Constraints'
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#untrustedInfo]') IS NOT NULL
DROP TABLE &#91;#untrustedInfo]
CREATE TABLE &#91;#untrustedInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
EXECUTE sp_MSforeachdb '
INSERT INTO &#91;#untrustedInfo](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
SELECT ''?'' + ''~FK'',count(*),count(*)
FROM &#91;?].sys.foreign_keys i
INNER JOIN &#91;?].sys.objects o
ON i.parent_object_id = o.object_id
INNER JOIN &#91;?].sys.schemas s
ON o.schema_id = s.schema_id
INNER JOIN &#91;?].sys.foreign_key_columns colz ON i.parent_object_id = colz.parent_object_id AND i.object_id = colz.constraint_object_id
WHERE i.is_not_trusted = 1
AND i.is_not_for_replication = 0
UNION ALL
SELECT ''?'' + ''~CHK'',count(*),count(*)
FROM &#91;?].sys.check_constraints i
INNER JOIN &#91;?].sys.objects o
ON i.parent_object_id = o.object_id
INNER JOIN &#91;?].sys.schemas s
ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1
AND i.is_not_for_replication = 0
AND i.is_disabled = 0;
'
--#################################################################################################
--failed logins'
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#failedlogins]') IS NOT NULL
DROP TABLE &#91;#failedlogins]
CREATE TABLE &#91;#failedlogins] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
declare @TraceIDToReview int
declare @path varchar(255)
SET @TraceIDToReview = 1 --this is the trace you want to review!
SELECT @path = path from sys.traces WHERE id = @TraceIDToReview
INSERT INTO &#91;#failedlogins](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
SELECT @@servername,COUNT(*),COUNT(*)
FROM ::fn_trace_gettable(@path, default) T
LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
LEFT OUTER JOIN sys.trace_subclass_values V
ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value
WHERE 1=1
AND StartTime >= DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
AND TE.name = 'Audit Login Failed'
AND V.subclass_name = 'Nonpooled'
--AND LoginName LIKE '%tkcsowner%'
--AND TextData IS NOT NULL
--#################################################################################################
-- Server Triggers'
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#servertriggers]') IS NOT NULL
DROP TABLE &#91;#servertriggers]
CREATE TABLE &#91;#servertriggers] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#servertriggers](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
SELECT MIN(ISNULL(name,'None')),count(*),count(*) FROM sys.server_triggers
--#################################################################################################
-- Database Triggers'
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#databasetriggers]') IS NOT NULL
DROP TABLE &#91;#databasetriggers]
CREATE TABLE &#91;#databasetriggers] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
EXECUTE sp_MSforeachdb '
INSERT INTO &#91;#databasetriggers](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
SELECT ''?'' AS DatabaseName,count(*),count(*) FROM &#91;?].sys.triggers WHERE parent_class = 0'
--#################################################################################################
-- all the queries
--#################################################################################################
--Failover Cluster Instance
SELECT 'Failover Cluster Instance' AS ResultsItem,'No Server currently uses Failover Cluster Instance' AS ResultsValue FROM &#91;#fci] HAVING SUM(&#91;PropertyCount]) = 0
UNION ALL
SELECT 'Failover Cluster Instance' ,'Server ' + s.Colzs + ' use Failover Cluster Instance'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
FROM &#91;#fci]
WHERE &#91;PropertyCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Always Availability
SELECT 'Always Availability','No Server currently uses Always Availability' FROM &#91;#AAGInfo] HAVING SUM(&#91;PropertyCount]) = 0
UNION ALL
SELECT 'Always Availability','Server ' + s.Colzs + ' use Always Availability'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
FROM &#91;#AAGInfo]
WHERE &#91;PropertyCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--SQL Policy'
SELECT 'SQL Policy', 'No Server currently uses SQL Policy' FROM &#91;#policyInfo] HAVING SUM(&#91;PropertyCount]) = 0
UNION ALL
SELECT 'SQL Policy','Server ' + s.Colzs + ' use SQL Policy'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
FROM &#91;#policyInfo]
WHERE &#91;PropertyCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--SQL audit'
SELECT 'SQL audit','No Server currently uses SQL audit' FROM &#91;#auditInfo] HAVING SUM(&#91;PropertyCount]) = 0
UNION ALL
SELECT 'SQL audit','Server ' + s.Colzs + ' use SQL audit'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
FROM &#91;#auditInfo]
WHERE &#91;PropertyCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--failed logins'
SELECT 'failed logins','No Server currently has failed logins' FROM &#91;#failedlogins] HAVING SUM(&#91;PropertyCount]) = 0
UNION ALL
SELECT 'failed logins','Server ' + s.Colzs + ' has failed logins'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) ) + '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' failed logins.)'
FROM &#91;#failedlogins]
WHERE &#91;PropertyCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--server triggers'
SELECT 'Server Triggers','No Server currently has Server Triggers' FROM &#91;#servertriggers] HAVING SUM(&#91;PropertyCount]) = 0
UNION ALL
SELECT 'Server Triggers','Server ' + s.Colzs + ' has Server Triggers'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) ) + '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' triggers.)'
FROM &#91;#servertriggers]
WHERE &#91;PropertyCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Partitioning
SELECT 'Partitioning','No database currently uses Partitioning' FROM &#91;#partitionInfo] HAVING SUM(&#91;PropertyCount]) = 0
UNION ALL
SELECT 'Partitioning','Databases ' + s.Colzs + ' use partitioning'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
FROM &#91;#partitionInfo]
WHERE &#91;PropertyCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Replication-Publisher
SELECT 'Replication-Publisher','No database currently uses Replication-Publisher Articles ' FROM &#91;#PublisherInfo] HAVING SUM(&#91;PropertyCount]) = 0 AND SUM(&#91;DetailCount]) = 0
UNION ALL
SELECT 'Replication-Publisher','Databases ' + s.Colzs + ' use Replication-Publisher Articles'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
+ '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' Articles)'
FROM &#91;#PublisherInfo]
WHERE &#91;PropertyCount] > 0 OR &#91;DetailCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Replication-Subscriber
SELECT 'Replication-Subscriber','No database currently use Replication-Subscriber Articles ' FROM &#91;#SubscriberInfo] HAVING SUM(&#91;PropertyCount]) = 0 AND SUM(&#91;DetailCount]) = 0
UNION ALL
SELECT 'Replication-Subscriber','Databases ' + s.Colzs + ' use Replication-Subscriber Articles'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
+ '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' Articles)'
FROM &#91;#SubscriberInfo]
WHERE &#91;PropertyCount] > 0 OR &#91;DetailCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Log Shipping-Primary
SELECT 'Log Shipping-Primary','No database currently uses Log Shipping-Primary' WHERE NOT EXISTS(SELECT * FROM &#91;#LogShippingPrimaryInfo])
UNION ALL
SELECT 'Log Shipping-Primary','Databases ' + s.Colzs + ' use Log Shipping-Primary'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(&#91;DatabaseName]) )
FROM &#91;#LogShippingPrimaryInfo]
WHERE &#91;LogShippingPrimaryCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Log Shipping-Secondary
SELECT 'Log Shipping-Secondary','No database currently uses Log Shipping-Secondary' FROM &#91;#LogShippingSecondaryInfo] HAVING SUM(&#91;PropertyCount]) = 0 AND SUM(&#91;DetailCount]) = 0
UNION ALL
SELECT 'Log Shipping-Secondary','Databases ' + s.Colzs + ' use Log Shipping-Secondary'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
+ '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' Articles)'
FROM &#91;#LogShippingSecondaryInfo]
WHERE &#91;PropertyCount] > 0 OR &#91;DetailCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--database triggers'
SELECT 'Database Triggers','No Database currently has Database Triggers' FROM &#91;#databasetriggers] HAVING SUM(&#91;PropertyCount]) = 0
UNION ALL
SELECT 'Database Triggers','Database ' + s.Colzs + ' has Database Triggers'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) ) + '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' triggers.)'
FROM &#91;#databasetriggers]
WHERE &#91;PropertyCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Change Data Capture
SELECT 'Change Data Capture','No database currently use Change Data Capture CDC Tables ' FROM &#91;#ChangeDataCapture] HAVING SUM(&#91;PropertyCount]) = 0 AND SUM(&#91;DetailCount]) = 0
UNION ALL
SELECT 'Change Data Capture','Databases ' + s.Colzs + ' use Change Data Capture CDC Tables'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
+ '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' CDC Tables)'
FROM &#91;#ChangeDataCapture]
WHERE &#91;PropertyCount] > 0 OR &#91;DetailCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Change Tracking
SELECT 'Change Tracking','No database currently use Change Tracking ' FROM &#91;#ChangeTracking] HAVING SUM(&#91;PropertyCount]) = 0 AND SUM(&#91;DetailCount]) = 0
UNION ALL
SELECT 'Change Tracking','Databases ' + s.Colzs + ' use Change Tracking'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
+ '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' Tables)'
FROM &#91;#ChangeTracking]
WHERE &#91;PropertyCount] > 0 OR &#91;DetailCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Always Encrypted
SELECT 'Always Encrypted','No database currently use Always Encrypted ' FROM &#91;#AlwaysEncrypted] HAVING SUM(&#91;PropertyCount]) = 0 AND SUM(&#91;DetailCount]) = 0
UNION ALL
SELECT 'Always Encrypted','Databases ' + s.Colzs + ' use Always Encrypted'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
+ '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' Tables)'
FROM &#91;#AlwaysEncrypted]
WHERE &#91;PropertyCount] > 0 OR &#91;DetailCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Dynamic Data Masking
SELECT 'Dynamic Data Masking','No database currently use Dynamic Data Masking ' FROM &#91;#DynamicDataMasking] HAVING SUM(&#91;PropertyCount]) = 0 AND SUM(&#91;DetailCount]) = 0
UNION ALL
SELECT 'Dynamic Data Masking','Databases ' + s.Colzs + ' use Dynamic Data Masking'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
+ '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' Tables)'
FROM &#91;#DynamicDataMasking]
WHERE &#91;PropertyCount] > 0 OR &#91;DetailCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Transparent Data Encryption
SELECT 'Transparent Data Encryption','No database currently use Transparent Data Encryption ' FROM &#91;#TransparentDataEncryption] HAVING SUM(&#91;PropertyCount]) = 0 AND SUM(&#91;DetailCount]) = 0
UNION ALL
SELECT 'Transparent Data Encryption','Databases ' + s.Colzs + ' use Transparent Data Encryption'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
-- + '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' Tables)'
FROM &#91;#TransparentDataEncryption]
WHERE &#91;PropertyCount] > 0 OR &#91;DetailCount] > 0
ORDER BY DatabaseName
Author: ChatGPT
### **SQL Server Feature Utilization & Security Audit**
This script **performs an extensive audit** of **SQL Server features, security, and configurations** across **all databases** in the instance. It uses **dynamic queries and system views** to detect **enabled features, security risks, and best practices**.
---
## **???? Key Features Audited**
### **1?????? Partitioning**
- Identifies databases using **table partitioning**.
- Helps analyze **data distribution and partitioning strategies**.
### **2?????? Replication (Publisher & Subscriber)**
- Detects **databases configured as replication publishers or subscribers**.
- Provides details on the **number of replicated articles**.
### **3?????? Log Shipping (Primary & Secondary)**
- Identifies **databases using log shipping**.
- Helps track **log shipping setup across primary and secondary instances**.
### **4?????? Change Data Capture (CDC)**
- Lists **databases with Change Data Capture (CDC) enabled**.
- Reports **tracked tables** under CDC.
### **5?????? Change Tracking**
- Identifies **databases that have Change Tracking enabled**.
- Reports **tables currently being tracked**.
### **6?????? Always Encrypted**
- Detects **databases with Always Encrypted columns**.
- Useful for **data protection and compliance** audits.
### **7?????? Dynamic Data Masking**
- Lists databases using **Dynamic Data Masking (DDM)**.
- Helps identify **sensitive data exposure risks**.
### **8?????? Transparent Data Encryption (TDE)**
- Identifies **databases encrypted with Transparent Data Encryption (TDE)**.
- Essential for **data security compliance**.
### **9?????? Custom User-Defined Data Types**
- Detects databases using **custom user-defined types (UDTs)**.
- Ensures **schema consistency and dependency tracking**.
### **???? Availability Group (AlwaysOn)**
- Checks if databases are **part of an AlwaysOn Availability Group**.
- Reports **availability replicas**.
### **1??????1?????? SQL Server Failover Cluster (FCI)**
- Identifies if the instance is running as a **Failover Cluster Instance (FCI)**.
### **1??????2?????? Memory-Optimized Tables**
- Detects **In-Memory OLTP tables**.
- Helps optimize **performance for high-transaction databases**.
### **1??????3?????? SQL Server Service Broker**
- Reports **databases where Service Broker is enabled**.
- Useful for **asynchronous messaging and workload distribution**.
### **1??????4?????? Synonyms & Sequences**
- Detects **databases using Synonyms or Sequences**.
- Helps manage **cross-database references**.
### **1??????5?????? Policy-Based Management**
- Identifies **servers using SQL Server Policies**.
### **1??????6?????? SQL Audit**
- Checks if **SQL Server Audit is enabled**.
- Reports **audited actions and security compliance**.
### **1??????7?????? Untrusted Constraints**
- Lists **databases with untrusted foreign key and check constraints**.
- Essential for **data integrity validation**.
### **1??????8?????? Failed Logins**
- Detects **recent failed login attempts** from SQL Server logs.
- Useful for **security monitoring and intrusion detection**.
### **1??????9?????? Server & Database Triggers**
- Reports **databases and servers with active DML triggers**.
- Helps **track unintended triggers impacting performance**.
---
## **???? Benefits of Running This Script**
??? **Security & Compliance** ??? Identify **audit policies, encryption, and access issues**.
??? **Performance Optimization** ??? Detect **partitioning, memory-optimized tables, and Service Broker usage**.
??? **Disaster Recovery & High Availability** ??? Review **log shipping, AlwaysOn AG, and replication status**.
??? **Schema & Data Management** ??? Track **synonyms, sequences, user-defined types, and change tracking**.
??? **Risk Mitigation** ??? Detect **untrusted constraints, failed logins, and outdated security policies**.
---
Would you like **help interpreting results, optimizing security, or fixing performance bottlenecks**? ????
Author: user
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
--#################################################################################################
-- Database currently uses Partitioning?
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#partitionInfo]') IS NOT NULL
DROP TABLE &#91;#partitionInfo]
CREATE TABLE &#91;#partitionInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL)
EXECUTE sp_MSforeachdb '
INSERT INTO &#91;#partitionInfo](&#91;DatabaseName],&#91;PropertyCount])
select ''?'' AS DatabaseName ,count(*)As PropertyCount
from sys.partitions
where partition_number > 1 '
--#################################################################################################
-- Replication Published Items
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#PublisherInfo]') IS NOT NULL
DROP TABLE &#91;#PublisherInfo]
CREATE TABLE &#91;#PublisherInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#PublisherInfo](&#91;DatabaseName],&#91;PropertyCount])
select name AS DatabaseName ,CASE WHEN is_published = 1 THEN 1 ELSE 0 END As PropertyCount
from sys.databases
EXECUTE sp_MSforeachdb '
IF EXISTS(SELECT * FROM &#91;?].sys.all_objects WHERE schema_id = 4 AND name = ''sysarticles'')
EXECUTE(''UPDATE &#91;#PublisherInfo] SET &#91;DetailCount] = COUNT(*) FROM &#91;?].sys.articles WHERE &#91;DatabaseName] = ''''?'''';'') '
--#################################################################################################
-- Replication Subscription Items
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#SubscriberInfo]') IS NOT NULL
DROP TABLE &#91;#SubscriberInfo]
CREATE TABLE &#91;#SubscriberInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#SubscriberInfo](&#91;DatabaseName],&#91;PropertyCount])
select name AS DatabaseName ,CASE WHEN is_subscribed = 1 THEN 1 ELSE 0 END As PropertyCount
from sys.databases
EXECUTE sp_MSforeachdb
'
UPDATE &#91;#SubscriberInfo] SET &#91;DetailCount] = (SELECT COUNT(*) FROM &#91;?].sys.tables where is_replicated = 1)
WHERE &#91;DatabaseName] = ''?''
'
--#################################################################################################
--Log Shipping-Primary
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#LogShippingPrimaryInfo]') IS NOT NULL
DROP TABLE &#91;#LogShippingPrimaryInfo]
CREATE TABLE &#91;#LogShippingPrimaryInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;LogShippingPrimaryCount] INT NULL)
INSERT INTO &#91;#LogShippingPrimaryInfo](&#91;DatabaseName],&#91;LogShippingPrimaryCount])
SELECT primary_database, 1 AS LogShippingPrimaryCount FROM &#91;msdb].&#91;dbo].&#91;log_shipping_primary_databases]
--#################################################################################################
--Log Shipping-Secondary
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#LogShippingSecondaryInfo]') IS NOT NULL
DROP TABLE &#91;#LogShippingSecondaryInfo]
CREATE TABLE &#91;#LogShippingSecondaryInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#LogShippingSecondaryInfo](&#91;DatabaseName],&#91;PropertyCount])
select name AS DatabaseName ,CASE WHEN is_subscribed = 1 THEN 1 ELSE 0 END As PropertyCount
from sys.databases
EXECUTE sp_MSforeachdb
'
UPDATE &#91;#LogShippingSecondaryInfo] SET &#91;DetailCount] = (SELECT COUNT(*) FROM &#91;?].sys.tables where is_replicated = 1)
WHERE &#91;DatabaseName] = ''?''
'
--#################################################################################################
-- Change Data Capture
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#ChangeDataCapture]') IS NOT NULL
DROP TABLE &#91;#ChangeDataCapture]
CREATE TABLE &#91;#ChangeDataCapture] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#ChangeDataCapture](&#91;DatabaseName],&#91;PropertyCount])
select name AS DatabaseName ,CASE WHEN is_cdc_enabled = 1 THEN 1 ELSE 0 END As PropertyCount
from sys.databases
EXECUTE sp_MSforeachdb
'
UPDATE &#91;#ChangeDataCapture] SET &#91;DetailCount] = (SELECT COUNT(*) FROM &#91;?].sys.tables where is_tracked_by_cdc = 1)
WHERE &#91;DatabaseName] = ''?''
'
--#################################################################################################
-- Change Tracking
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#ChangeTracking]') IS NOT NULL
DROP TABLE &#91;#ChangeTracking]
CREATE TABLE &#91;#ChangeTracking] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
EXECUTE sp_MSforeachdb '
INSERT INTO &#91;#ChangeTracking](&#91;DatabaseName],&#91;PropertyCount])
select ''?'' AS DAtabaseName,Count(*) from &#91;?].sys.change_tracking_databases'
EXECUTE sp_MSforeachdb
'
UPDATE &#91;#ChangeTracking] SET &#91;DetailCount] = (SELECT COUNT(*) FROM &#91;?].sys.change_tracking_tables)
WHERE &#91;DatabaseName] = ''?''
'
--#################################################################################################
-- Always Encrypted
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#AlwaysEncrypted]') IS NOT NULL
DROP TABLE &#91;#AlwaysEncrypted]
CREATE TABLE &#91;#AlwaysEncrypted] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
EXECUTE sp_MSforeachdb '
INSERT INTO &#91;#AlwaysEncrypted](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
select ''?'' AS DAtabaseName,Count(*),Count(*) from &#91;?].sys.columns WHERE &#91;encryption_type] IS NOT NULL'
--#################################################################################################
-- Dynamic Data Masking
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#DynamicDataMasking]') IS NOT NULL
DROP TABLE &#91;#DynamicDataMasking]
CREATE TABLE &#91;#DynamicDataMasking] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
EXECUTE sp_MSforeachdb '
INSERT INTO &#91;#DynamicDataMasking](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
select ''?'' AS DAtabaseName,Count(*),Count(*) from &#91;?].sys.&#91;masked_columns]'
--#################################################################################################
-- Transparent Data Encryption
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#TransparentDataEncryption]') IS NOT NULL
DROP TABLE &#91;#TransparentDataEncryption]
CREATE TABLE &#91;#TransparentDataEncryption] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#TransparentDataEncryption](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
select name AS DAtabaseName,CASE WHEN is_encrypted = 1 THEN 1 ELSE 0 END,CASE WHEN is_encrypted = 1 THEN 1 ELSE 0 END from sys.databases
--#################################################################################################
-- Custom User Defined DataTypes
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#CustomUserDataType]') IS NOT NULL
DROP TABLE &#91;#CustomUserDataType]
CREATE TABLE &#91;#CustomUserDataType] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#CustomUserDataType](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
execute sp_MSforeachdb '
select ''?'' AS DatabaseName,0, COUNT(*) from &#91;?].sys.types WHERE is_user_defined = 1'
UPDATE &#91;#CustomUserDataType] SET &#91;PropertyCount] = 1 WHERE &#91;DetailCount] > 0 --select * from &#91;#CustomUserDataType]
DELETE FROM &#91;#CustomUserDataType] WHERE DatabaseName like 'ReportServer%' OR DatabaseName IN('SSISDB','master','model','msdb','tempdb')
--#################################################################################################
-- Is Always Availability
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#AAGInfo]') IS NOT NULL
DROP TABLE &#91;#AAGInfo]
CREATE TABLE &#91;#AAGInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#AAGInfo](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
SELECT d.name ,0,COUNT(hars.replica_id) FROM sys.databases d LEFT JOIN sys.dm_hadr_availability_replica_states hars ON d.replica_id = hars.replica_id GROUP BY d.name
--#################################################################################################
-- Is fci
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#fci]') IS NOT NULL
DROP TABLE &#91;#fci]
CREATE TABLE &#91;#fci] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#fci](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
SELECT 'master',0,0 WHERE NOT EXISTS( SELECT * FROM &#91;sys].&#91;dm_os_cluster_nodes]) UNION ALL
SELECT NodeName, 1,count(*) FROM &#91;sys].&#91;dm_os_cluster_nodes] group by NodeName
--#################################################################################################
-- Memory Optimized Tables
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#memoptimizedInfo]') IS NOT NULL
DROP TABLE &#91;#memoptimizedInfo]
CREATE TABLE &#91;#memoptimizedInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#memoptimizedInfo](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
EXECUTE sp_MSforeachdb '
INSERT INTO &#91;#memoptimizedInfo](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
SELECT ''?'',COUNT(*),COUNT(*) FROM &#91;?].sys.dm_db_xtp_table_memory_stats'
--#################################################################################################
-- Service Broker
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#servicebrokerInfo]') IS NOT NULL
DROP TABLE &#91;#servicebrokerInfo]
CREATE TABLE &#91;#servicebrokerInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#servicebrokerInfo](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
select name,is_broker_enabled,is_broker_enabled from sys.databases WHERE database_id > 4
--#################################################################################################
-- synonyms
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#synonyminfo]') IS NOT NULL
DROP TABLE &#91;#synonyminfo]
CREATE TABLE &#91;#synonyminfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
EXECUTE sp_MSforeachdb '
INSERT INTO &#91;#synonyminfo](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
select ''?'',count(*),count(*) from &#91;?].sys.synonyms'
--#################################################################################################
-- sequences
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#sequenceInfo]') IS NOT NULL
DROP TABLE &#91;#sequenceInfo]
CREATE TABLE &#91;#sequenceInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
EXECUTE sp_MSforeachdb '
INSERT INTO &#91;#sequenceInfo](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
select ''?'',count(*),count(*) from &#91;?].sys.sequences'
--#################################################################################################
-- SQL Policy'
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#policyInfo]') IS NOT NULL
DROP TABLE &#91;#policyInfo]
CREATE TABLE &#91;#policyInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#policyInfo](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
SELECT MIN(ISNULL(name,'None')),count(*),count(*)
FROM msdb.dbo.syspolicy_policies AS Pol
INNER JOIN msdb.dbo.syspolicy_policy_execution_history AS PolHist
ON &#91;PolHist].&#91;policy_id] = &#91;Pol].&#91;policy_id]
--#################################################################################################
-- SQL audit'
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#auditInfo]') IS NOT NULL
DROP TABLE &#91;#auditInfo]
CREATE TABLE &#91;#auditInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#auditInfo](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
SELECT MIN(ISNULL(name,'None')),count(*),count(*) FROM sys.server_audits
--#################################################################################################
--Untrusted Constraints'
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#untrustedInfo]') IS NOT NULL
DROP TABLE &#91;#untrustedInfo]
CREATE TABLE &#91;#untrustedInfo] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
EXECUTE sp_MSforeachdb '
INSERT INTO &#91;#untrustedInfo](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
SELECT ''?'' + ''~FK'',count(*),count(*)
FROM &#91;?].sys.foreign_keys i
INNER JOIN &#91;?].sys.objects o
ON i.parent_object_id = o.object_id
INNER JOIN &#91;?].sys.schemas s
ON o.schema_id = s.schema_id
INNER JOIN &#91;?].sys.foreign_key_columns colz ON i.parent_object_id = colz.parent_object_id AND i.object_id = colz.constraint_object_id
WHERE i.is_not_trusted = 1
AND i.is_not_for_replication = 0
UNION ALL
SELECT ''?'' + ''~CHK'',count(*),count(*)
FROM &#91;?].sys.check_constraints i
INNER JOIN &#91;?].sys.objects o
ON i.parent_object_id = o.object_id
INNER JOIN &#91;?].sys.schemas s
ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1
AND i.is_not_for_replication = 0
AND i.is_disabled = 0;
'
--#################################################################################################
--failed logins'
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#failedlogins]') IS NOT NULL
DROP TABLE &#91;#failedlogins]
CREATE TABLE &#91;#failedlogins] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
declare @TraceIDToReview int
declare @path varchar(255)
SET @TraceIDToReview = 1 --this is the trace you want to review!
SELECT @path = path from sys.traces WHERE id = @TraceIDToReview
INSERT INTO &#91;#failedlogins](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
SELECT @@servername,COUNT(*),COUNT(*)
FROM ::fn_trace_gettable(@path, default) T
LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
LEFT OUTER JOIN sys.trace_subclass_values V
ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value
WHERE 1=1
AND StartTime >= DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
AND TE.name = 'Audit Login Failed'
AND V.subclass_name = 'Nonpooled'
--AND LoginName LIKE '%tkcsowner%'
--AND TextData IS NOT NULL
--#################################################################################################
-- Server Triggers'
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#servertriggers]') IS NOT NULL
DROP TABLE &#91;#servertriggers]
CREATE TABLE &#91;#servertriggers] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
INSERT INTO &#91;#servertriggers](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
SELECT MIN(ISNULL(name,'None')),count(*),count(*) FROM sys.server_triggers
--#################################################################################################
-- Database Triggers'
--#################################################################################################
IF OBJECT_ID('tempdb..&#91;#databasetriggers]') IS NOT NULL
DROP TABLE &#91;#databasetriggers]
CREATE TABLE &#91;#databasetriggers] (
&#91;DatabaseName] NVARCHAR(128) NULL,
&#91;PropertyCount] INT NULL,
&#91;DetailCount] INT NULL DEFAULT 0 )
EXECUTE sp_MSforeachdb '
INSERT INTO &#91;#databasetriggers](&#91;DatabaseName],&#91;PropertyCount],&#91;DetailCount])
SELECT ''?'' AS DatabaseName,count(*),count(*) FROM &#91;?].sys.triggers WHERE parent_class = 0'
--#################################################################################################
-- all the queries
--#################################################################################################
--Failover Cluster Instance
SELECT 'Failover Cluster Instance' AS ResultsItem,'No Server currently uses Failover Cluster Instance' AS ResultsValue FROM &#91;#fci] HAVING SUM(&#91;PropertyCount]) = 0
UNION ALL
SELECT 'Failover Cluster Instance' ,'Server ' + s.Colzs + ' use Failover Cluster Instance'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
FROM &#91;#fci]
WHERE &#91;PropertyCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Always Availability
SELECT 'Always Availability','No Server currently uses Always Availability' FROM &#91;#AAGInfo] HAVING SUM(&#91;PropertyCount]) = 0
UNION ALL
SELECT 'Always Availability','Server ' + s.Colzs + ' use Always Availability'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
FROM &#91;#AAGInfo]
WHERE &#91;PropertyCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--SQL Policy'
SELECT 'SQL Policy', 'No Server currently uses SQL Policy' FROM &#91;#policyInfo] HAVING SUM(&#91;PropertyCount]) = 0
UNION ALL
SELECT 'SQL Policy','Server ' + s.Colzs + ' use SQL Policy'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
FROM &#91;#policyInfo]
WHERE &#91;PropertyCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--SQL audit'
SELECT 'SQL audit','No Server currently uses SQL audit' FROM &#91;#auditInfo] HAVING SUM(&#91;PropertyCount]) = 0
UNION ALL
SELECT 'SQL audit','Server ' + s.Colzs + ' use SQL audit'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
FROM &#91;#auditInfo]
WHERE &#91;PropertyCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--failed logins'
SELECT 'failed logins','No Server currently has failed logins' FROM &#91;#failedlogins] HAVING SUM(&#91;PropertyCount]) = 0
UNION ALL
SELECT 'failed logins','Server ' + s.Colzs + ' has failed logins'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) ) + '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' failed logins.)'
FROM &#91;#failedlogins]
WHERE &#91;PropertyCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--server triggers'
SELECT 'Server Triggers','No Server currently has Server Triggers' FROM &#91;#servertriggers] HAVING SUM(&#91;PropertyCount]) = 0
UNION ALL
SELECT 'Server Triggers','Server ' + s.Colzs + ' has Server Triggers'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) ) + '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' triggers.)'
FROM &#91;#servertriggers]
WHERE &#91;PropertyCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Partitioning
SELECT 'Partitioning','No database currently uses Partitioning' FROM &#91;#partitionInfo] HAVING SUM(&#91;PropertyCount]) = 0
UNION ALL
SELECT 'Partitioning','Databases ' + s.Colzs + ' use partitioning'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
FROM &#91;#partitionInfo]
WHERE &#91;PropertyCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Replication-Publisher
SELECT 'Replication-Publisher','No database currently uses Replication-Publisher Articles ' FROM &#91;#PublisherInfo] HAVING SUM(&#91;PropertyCount]) = 0 AND SUM(&#91;DetailCount]) = 0
UNION ALL
SELECT 'Replication-Publisher','Databases ' + s.Colzs + ' use Replication-Publisher Articles'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
+ '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' Articles)'
FROM &#91;#PublisherInfo]
WHERE &#91;PropertyCount] > 0 OR &#91;DetailCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Replication-Subscriber
SELECT 'Replication-Subscriber','No database currently use Replication-Subscriber Articles ' FROM &#91;#SubscriberInfo] HAVING SUM(&#91;PropertyCount]) = 0 AND SUM(&#91;DetailCount]) = 0
UNION ALL
SELECT 'Replication-Subscriber','Databases ' + s.Colzs + ' use Replication-Subscriber Articles'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
+ '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' Articles)'
FROM &#91;#SubscriberInfo]
WHERE &#91;PropertyCount] > 0 OR &#91;DetailCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Log Shipping-Primary
SELECT 'Log Shipping-Primary','No database currently uses Log Shipping-Primary' WHERE NOT EXISTS(SELECT * FROM &#91;#LogShippingPrimaryInfo])
UNION ALL
SELECT 'Log Shipping-Primary','Databases ' + s.Colzs + ' use Log Shipping-Primary'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(&#91;DatabaseName]) )
FROM &#91;#LogShippingPrimaryInfo]
WHERE &#91;LogShippingPrimaryCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Log Shipping-Secondary
SELECT 'Log Shipping-Secondary','No database currently uses Log Shipping-Secondary' FROM &#91;#LogShippingSecondaryInfo] HAVING SUM(&#91;PropertyCount]) = 0 AND SUM(&#91;DetailCount]) = 0
UNION ALL
SELECT 'Log Shipping-Secondary','Databases ' + s.Colzs + ' use Log Shipping-Secondary'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
+ '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' Articles)'
FROM &#91;#LogShippingSecondaryInfo]
WHERE &#91;PropertyCount] > 0 OR &#91;DetailCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--database triggers'
SELECT 'Database Triggers','No Database currently has Database Triggers' FROM &#91;#databasetriggers] HAVING SUM(&#91;PropertyCount]) = 0
UNION ALL
SELECT 'Database Triggers','Database ' + s.Colzs + ' has Database Triggers'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) ) + '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' triggers.)'
FROM &#91;#databasetriggers]
WHERE &#91;PropertyCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Change Data Capture
SELECT 'Change Data Capture','No database currently use Change Data Capture CDC Tables ' FROM &#91;#ChangeDataCapture] HAVING SUM(&#91;PropertyCount]) = 0 AND SUM(&#91;DetailCount]) = 0
UNION ALL
SELECT 'Change Data Capture','Databases ' + s.Colzs + ' use Change Data Capture CDC Tables'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
+ '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' CDC Tables)'
FROM &#91;#ChangeDataCapture]
WHERE &#91;PropertyCount] > 0 OR &#91;DetailCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Change Tracking
SELECT 'Change Tracking','No database currently use Change Tracking ' FROM &#91;#ChangeTracking] HAVING SUM(&#91;PropertyCount]) = 0 AND SUM(&#91;DetailCount]) = 0
UNION ALL
SELECT 'Change Tracking','Databases ' + s.Colzs + ' use Change Tracking'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
+ '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' Tables)'
FROM &#91;#ChangeTracking]
WHERE &#91;PropertyCount] > 0 OR &#91;DetailCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Always Encrypted
SELECT 'Always Encrypted','No database currently use Always Encrypted ' FROM &#91;#AlwaysEncrypted] HAVING SUM(&#91;PropertyCount]) = 0 AND SUM(&#91;DetailCount]) = 0
UNION ALL
SELECT 'Always Encrypted','Databases ' + s.Colzs + ' use Always Encrypted'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
+ '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' Tables)'
FROM &#91;#AlwaysEncrypted]
WHERE &#91;PropertyCount] > 0 OR &#91;DetailCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Dynamic Data Masking
SELECT 'Dynamic Data Masking','No database currently use Dynamic Data Masking ' FROM &#91;#DynamicDataMasking] HAVING SUM(&#91;PropertyCount]) = 0 AND SUM(&#91;DetailCount]) = 0
UNION ALL
SELECT 'Dynamic Data Masking','Databases ' + s.Colzs + ' use Dynamic Data Masking'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
+ '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' Tables)'
FROM &#91;#DynamicDataMasking]
WHERE &#91;PropertyCount] > 0 OR &#91;DetailCount] > 0
ORDER BY DatabaseName
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
WHERE s.Colzs IS NOT NULL
UNION ALL
--Transparent Data Encryption
SELECT 'Transparent Data Encryption','No database currently use Transparent Data Encryption ' FROM &#91;#TransparentDataEncryption] HAVING SUM(&#91;PropertyCount]) = 0 AND SUM(&#91;DetailCount]) = 0
UNION ALL
SELECT 'Transparent Data Encryption','Databases ' + s.Colzs + ' use Transparent Data Encryption'
FROM(SELECT
Colzs = STUFF((SELECT ',' + CONVERT(varchar(128),quotename(DatabaseName) )
-- + '(' + CONVERT(VARCHAR(30),&#91;DetailCount]) + ' Tables)'
FROM &#91;#TransparentDataEncryption]
WHERE &#91;PropertyCount] > 0 OR &#91;DetailCount] > 0
ORDER BY DatabaseName
Author: ChatGPT
It looks like you're running an **extensive SQL Server audit script** that gathers information about various **server-wide and database-level features** such as:
- **Partitioning**
- **Replication (Publisher/Subscriber)**
- **Log Shipping (Primary/Secondary)**
- **Change Data Capture (CDC)**
- **Change Tracking**
- **Always Encrypted**
- **Dynamic Data Masking**
- **Transparent Data Encryption (TDE)**
- **Custom User-Defined Data Types**
- **Always On Availability Groups (AAG)**
- **Failover Cluster Instances (FCI)**
- **Memory-Optimized Tables**
- **Service Broker**
- **Synonyms and Sequences**
- **SQL Policies and Audits**
- **Untrusted Constraints**
- **Failed Logins**
- **Server & Database Triggers**
---
### **???? What Would You Like to Do Next?**
1. **Analyze the Results?** ??? If you've already run this script, I can help interpret the results and recommend actions.
2. **Optimize the Script?** ??? If you need improvements in efficiency, I can refactor it (e.g., reducing `sp_MSforeachdb` usage).
3. **Troubleshoot Issues?** ??? If you encountered any errors while running this script, let me know the error messages.
4. **Customize for Your Environment?** ??? If you want additional checks (e.g., database growth trends, index fragmentation, etc.), I can modify the script accordingly.
Let me know how you???d like to proceed! ????
Author: user
SELECT
@@SERVERNAME AS Server_Name, name,
create_date AS SQL_Server_Install_Date,
'The earliest Date these two accounts were created is the SQL Server Installation Date' AS Notes
FROM sys.server_principals WITH (NOLOCK)
WHERE name = N'NT AUTHORITY\SYSTEM'
OR name = N'NT AUTHORITY\NETWORK SERVICE'
Author: ChatGPT
Your query retrieves the **SQL Server installation date** by identifying the earliest creation date of the built-in accounts **`NT AUTHORITY\SYSTEM`** and **`NT AUTHORITY\NETWORK SERVICE`**.
### **???? Explanation:**
- `@@SERVERNAME` ??? Returns the **name of the SQL Server instance**.
- `sys.server_principals` ??? Contains information about logins at the server level.
- `create_date` ??? Indicates when a login was created. The built-in accounts are created **when SQL Server is installed**, making them a good indicator of the installation date.
- **`NOLOCK` hint** ??? Avoids blocking but may return uncommitted data.
### **???? Potential Enhancements:**
1. **Ensure Only One Row Is Returned:**
```sql
SELECT TOP 1
@@SERVERNAME AS Server_Name,
MIN(create_date) AS SQL_Server_Install_Date,
'Earliest creation date of system accounts = SQL Server Installation Date' AS Notes
FROM sys.server_principals WITH (NOLOCK)
WHERE name IN (N'NT AUTHORITY\SYSTEM', N'NT AUTHORITY\NETWORK SERVICE')
GROUP BY @@SERVERNAME;
```
- This ensures only **one row** is returned, with the **earliest `create_date`**.
2. **Alternative Method:**
If you suspect that the built-in accounts were recreated (e.g., domain policy changes), you can use:
```sql
SELECT
@@SERVERNAME AS Server_Name,
create_date AS SQL_Server_Install_Date,
'Instance creation date (may differ from full SQL installation date)' AS Notes
FROM sys.databases
WHERE name = 'tempdb';
```
- The `tempdb` database is **recreated each restart**, but its `create_date` shows the SQL **instance creation date**.
---
### **???? Next Steps:**
- **Are you looking to confirm the SQL Server installation date?** ???
- **Do you need a more reliable method to determine the original installation date?** ????
- **Want to cross-check against Windows installation logs?** ????
Let me know if you need refinements! ????
Author: user
DECLARE @ServerName VARCHAR(128)
DECLARE @ApplicationName VARCHAR(128)
DECLARE @AppEmailGroup VARCHAR(128)
DECLARE @ServerLayer VARCHAR(128)
--#################################################################################################
-- Database Specific Contact Groups if they exist
--#################################################################################################
SELECT @ApplicationName = CONVERT(VARCHAR(4000),prpz.value) FROM sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'ApplicationName'
SELECT @AppEmailGroup = CONVERT(VARCHAR(4000),prpz.value) FROM sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'AppEmailGroup'
SELECT @ServerName = CONVERT(VARCHAR(128),prpz.value) FROM sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'Cname'
SELECT @ServerLayer = CONVERT(VARCHAR(128),prpz.value) FROM sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'ServerLayer'
-- SELECT @ServerName AS CName,@ServerLayer AS ServerLayer,@ApplicationName AS ApplicationName,@AppEmailGroup AS AppEmailGroup
--#################################################################################################
-- Server Level Contact Groups if Database Level are Blank
--#################################################################################################
IF @ApplicationName IS NULL
SELECT @ApplicationName = CONVERT(VARCHAR(4000),prpz.value) FROM &#91;master].sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'ApplicationName'
IF @AppEmailGroup IS NULL
SELECT @AppEmailGroup = CONVERT(VARCHAR(4000),prpz.value) FROM &#91;master].sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'AppEmailGroup'
IF @ServerName IS NULL
SELECT @ServerName = CONVERT(VARCHAR(128),prpz.value) FROM &#91;master].sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'Cname'
IF @ServerLayer IS NULL
SELECT @ServerLayer = CONVERT(VARCHAR(128),prpz.value) FROM &#91;master].sys.&#91;extended_properties] prpz WHERE prpz.Class=0
AND prpz.name = 'ServerLayer'
SELECT @ServerName = ISNULL(@ServerName,CONVERT(VARCHAR(128),@@SERVERNAME))
SELECT @ServerName AS CName,@ServerLayer AS ServerLayer,@ApplicationName AS ApplicationName,@AppEmailGroup AS AppEmailGroup
Author: ChatGPT
Your script retrieves **server-level and database-specific metadata** stored as **Extended Properties** in SQL Server. It attempts to extract values for the following properties:
1. **ApplicationName** ??? Name of the application associated with the database/server.
2. **AppEmailGroup** ??? Contact email group for the application.
3. **Cname (ServerName)** ??? Custom canonical name for the server.
4. **ServerLayer** ??? Server classification (e.g., Prod, Dev, QA).
---
### **???? How It Works**
- First, it tries to fetch **database-specific** values from the **current database**.
- If a property is `NULL`, it falls back to checking the **&#91;master] database** for a global server-wide value.
- If **`Cname`** is still `NULL`, it defaults to `@@SERVERNAME`.
---
### **???? Potential Enhancements**
1. **Optimize Property Retrieval:**
Instead of running multiple queries, **use a single query per level** and assign values in one step:
```sql
-- Fetch Database-Level Extended Properties
DECLARE @ServerName VARCHAR(128)
DECLARE @ApplicationName VARCHAR(128)
DECLARE @AppEmailGroup VARCHAR(128)
DECLARE @ServerLayer VARCHAR(128)
SELECT
@ApplicationName = MAX(CASE WHEN prpz.name = 'ApplicationName' THEN CONVERT(VARCHAR(4000), prpz.value) END),
@AppEmailGroup = MAX(CASE WHEN prpz.name = 'AppEmailGroup' THEN CONVERT(VARCHAR(4000), prpz.value) END),
@ServerName = MAX(CASE WHEN prpz.name = 'Cname' THEN CONVERT(VARCHAR(128), prpz.value) END),
@ServerLayer = MAX(CASE WHEN prpz.name = 'ServerLayer' THEN CONVERT(VARCHAR(128), prpz.value) END)
FROM sys.extended_properties prpz
WHERE prpz.Class = 0;
-- Fetch Master-Level Extended Properties (if missing)
SELECT
@ApplicationName = COALESCE(@ApplicationName, MAX(CASE WHEN prpz.name = 'ApplicationName' THEN CONVERT(VARCHAR(4000), prpz.value) END)),
@AppEmailGroup = COALESCE(@AppEmailGroup, MAX(CASE WHEN prpz.name = 'AppEmailGroup' THEN CONVERT(VARCHAR(4000), prpz.value) END)),
@ServerName = COALESCE(@ServerName, MAX(CASE WHEN prpz.name = 'Cname' THEN CONVERT(VARCHAR(128), prpz.value) END)),
@ServerLayer = COALESCE(@ServerLayer, MAX(CASE WHEN prpz.name = 'ServerLayer' THEN CONVERT(VARCHAR(128), prpz.value) END))
FROM &#91;master].sys.extended_properties prpz
WHERE prpz.Class = 0;
-- Default to @@SERVERNAME if @ServerName is still NULL
SELECT @ServerName = ISNULL(@ServerName, CONVERT(VARCHAR(128), @@SERVERNAME));
-- Display Results
SELECT
@ServerName AS CName,
@ServerLayer AS ServerLayer,
@ApplicationName AS ApplicationName,
@AppEmailGroup AS AppEmailGroup;
```
**??? Benefits:**
- Reduces multiple `SELECT` statements into **fewer queries**.
- Uses `COALESCE()` instead of multiple `IF NULL` checks.
- Ensures **only one scan** of `sys.extended_properties`.
---
### **???? Next Steps**
- **Need to check if properties exist before querying?** ????
- **Want to store the results in a logging/audit table?** ????
- **Need to extract additional metadata?** ????
Let me know what refinements you need! ????
Author: user
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
IF OBJECT_ID('tempdb.&#91;dbo].&#91;#Traces]') IS NOT NULL
DROP TABLE &#91;dbo].&#91;#Traces]
CREATE TABLE #Traces(&#91;TraceFlag] int,&#91;TraceStatus] int,&#91;IsGlobal] int,&#91;IsSession] INT)
INSERT INTO #Traces(&#91;TraceFlag],&#91;TraceStatus],&#91;IsGlobal],&#91;IsSession])
EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')
;WITH MyCTE(&#91;TraceFlag],&#91;Descrip])
AS
(
SELECT 101,'Function: Verbose Merge Replication logging output for troubleshooting Merger repl performance' UNION ALL
SELECT 102,'Function: Verbose Merge Replication logging to msmerge_history table for troubleshooting Merger repl performance' UNION ALL
SELECT 105,'Function: Join more than 16 tables in SQL server 6.5' UNION ALL
SELECT 106,'Function: This enables you to see the messages that are sent to and from the Publisher, if you are using Web Synchronization' UNION ALL
SELECT 107,'Function: Alter input rules for decimal numbers' UNION ALL
SELECT 110,'Function: SQL 6.5 - Turns off ANSI SELECT characteristics. KB 152032' UNION ALL
SELECT 139,'Forces correct conversion semantics in the scope of DBCC check commands like DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKCONSTRAINTS, when analyzing the improved precision and conversion logic introduced with compatibility level 130 for specific data types, on a database that has a lower compatibility level. For more information, see SQL Server and Azure SQL Database improvements in handling some data types and uncommon operations. Applies to: SQL Server 2016 (13.x) RTM CU 3, SQL Server 2016 (13.x) Service Pack 1 and later versions. Warning: Trace Flag 139 isn''t meant to be enabled continuously in a production environment, and should be used for the sole purpose of performing database validation checks described in SQL Server and Azure SQL Database improvements in handling some data types and uncommon operations. It should be immediately disabled after validation checks are completed. Scope: Global only. ' UNION ALL
SELECT 168,'Function: Bugfix in ORDER BY' UNION ALL
SELECT 174,'Function: Increases the SQL Server Database Engine plan cache bucket count from 40,009 to 160,001 on 64-bit systems. For more information, see this Microsoft Support article.' UNION ALL
SELECT 176,'Addresses errors when rebuilding partitions online for tables that contain a computed partitioning column. For more information, see KB3213683 and KB4541096. Scope: Global or session. ' UNION ALL
SELECT 204,'Function: SQL 6.5 - Backward compatibility switch that enables non-ansi standard behavior. E.g. previously SQL server ignored trailing blanks in the like statement and allowed queries that contained aggregated functions to have items in the group by clause that were not in the SELECT list.' UNION ALL
SELECT 205,'Function: Log usage of AutoStat/Auto Update Statistics' UNION ALL
SELECT 206,'Function: SQL 6.5 - Provides backward compatibility for the set user statement. KB 160732' UNION ALL
SELECT 208,'Function: SET QUOTED IDENTIFIER ON.' UNION ALL
SELECT 210,'Function: SQL 9 - Error when you run a query against a view in SQL Server 2005: "An error occurred while executing batch". KB 945892' UNION ALL
SELECT 212,'Function: SQL 9 - Query may run much slower when compared to SQL 8 when you use a cursor to run the query. KB 951184' UNION ALL
SELECT 237,'Function: Tells SQL Server to use correlated sub-queries in Non-ANSI standard backward compatibility mode.' UNION ALL
SELECT 242,'Function: Provides backward compatibility for correlated subqueries where non-ANSI-standard results are desired.' UNION ALL
SELECT 243,'Function: 243 Provides backward compatibility for nullability behavior. When set, SQL Server has the same nullability violation behavior as that of a ver 4.2: - Processing of the entire batch is terminated if the nullability error (inserting NULL into a NOT NULL field) can be detected at compile time. - Processing of offending row is skipped, but the command continues if the nullability violation is detected at run time. Behavior of SQL Server is now more consistent because nullability checks are made at run time and a nullability violation results in the command terminating and the batch or transaction process continuing.' UNION ALL
SELECT 244,'Function: Disables checking for allowed interim constraint violations. By default, SQL Server checks for and allows interim constraint violations. An interim constraint violation is caused by a change that removes the violation such that the constraint is met, all within a single statement and transaction. SQL Server checks for interim constraint violations for self-referencing DELETE statements, INSERT, and multi-row UPDATE statements. This checking requires more work tables. With this trace flag you can disallow interim constraint violations, thus requiring fewer work tables.' UNION ALL
SELECT 246,'Function: Derived or NULL columns must be explicitly named in a SELECT ...INTO or create view statement when not done they raise an error. This flag avoids that.' UNION ALL
SELECT 253,'Function: Prevent adhoc query plans from staying in cache (SQL 2005)' UNION ALL
SELECT 257,'Function: Will invoke a print algorithm on the XML output before returning it to make the XML result more readable.' UNION ALL
SELECT 260,'Function: Prints Extended stord proc DLL versioning info' UNION ALL
SELECT 262,'Function: SQL 7 - Trailing spaces are no longer truncated from literal strings in CASE statements. KB 891116' UNION ALL
SELECT 272,'Function: Grenerates a log record per identity increment. Can be users to convert SQL 2012 back to old style Indetity behaviour' UNION ALL
SELECT 302,'Function: Output Index SELECT ion info' UNION ALL
SELECT 310,'Function: Outputs info about actual join order' UNION ALL
SELECT 320,'Function: Disables join-order heuristics used in ANSI joins. To see join-order heuristics use flag 310. SQL Server uses join-order heuristics to reduce the no'' of permutations when using the best join order.' UNION ALL
SELECT 323,'Function: Outputs detailed info about updates' UNION ALL
SELECT 325,'Function: Prints information about the cost of using a non-clustered index or a sort to process an ORDER BY clause.' UNION ALL
SELECT 326,'Function: Prints information about estimated & actual costs of sorts. Instructs server to use arithmetic averaging when calculating density instead of a geometric weighted average when updating statistics. Useful for building better stats when an index has skew on the leading column. Use only for updating the stats of a table/index with known skewed data.' UNION ALL
SELECT 330,'Function: Enables full output when using the SET SHOWPLAN_ALL option, which gives detailed information about joins.' UNION ALL
SELECT 342,'Function: Disables the costing of pseudo-merge joins, thus significantly reducing time spent on the parse for certain types of large, multi-table joins. One can also use SET FORCEPLAN ON to disable the costing of pseudo-merge joins because the query is forced to use the order specified in the FROM clause.' UNION ALL
SELECT 345,'Function: Changes join order SELECT ion logic in SQL Server 6.5' UNION ALL
SELECT 445,'Function: Prints ???compile issued??? message in the errorlog for each compiled statement, when used together with 3605' UNION ALL
SELECT 460,'Replaces data truncation message ID 8152 with message ID 2628. For more information, see KB4468101. Starting with SQL Server 2019 (15.x), to accomplish this at the database level, see the VERBOSE_TRUNCATION_WARNINGS option in ALTER DATABASE SCOPED CONFIGURATION. Applies to: SQL Server 2016 (13.x) Service Pack 2 CU 6, SQL Server 2017 (14.x) CU 12, and later versions. Note: Starting with database compatibility level 150, message ID 2628 is the default and this trace flag has no effect. For database compatibility level 140 or lower, message ID 2628 remains an opt-in error message that requires Trace Flag 460 to be enabled, and this database scoped configuration has no effect. Scope: Global or session. ' UNION ALL
SELECT 506,'Function: Enforces SQL-92 standards regarding null values for comparisons between variables and parameters. Any comparison of variables and parameters that contain a NULL always results in a NULL.' UNION ALL
SELECT 610,'Function: Minimally logged inserts to indexed tables' UNION ALL
SELECT 611,'Function: SQL 9 - When turned on, each lock escalation is recorded in the error log along with the SQL Server handle number.' UNION ALL
SELECT 634,'Disables the background columnstore compression task. SQL Server periodically runs the Tuple Mover background task that compresses columnstore index rowgroups with uncompressed data, one such rowgroup at a time. Columnstore compression improves query performance but also consumes system resources. You can control the timing of columnstore compression manually, by disabling the background compression task with Trace Flag 634, and then explicitly invoking ALTER INDEX...REORGANIZE or ALTER INDEX...REBUILD at the time of your choice. Scope: Global only. ' UNION ALL
SELECT 652,'Function: Disable page pre-fetching scans' UNION ALL
SELECT 653,'Function: Disables read ahead for the current connection.' UNION ALL
SELECT 661,'Function: Disable the ghost record removal process' UNION ALL
SELECT 662,'Function: Prints detailed information about the work done by the ghost cleanup task when it runs next. Use TF 3605 to see the output in the errorlog' UNION ALL
SELECT 692,'Disables fast inserts while bulk loading data into heap or clustered index. Starting with SQL Server 2016 (13.x), fast inserts are enabled by default, using minimal logging when database is in simple or bulk logged recovery model to optimize insert performance for records inserted into new pages. With fast inserts, each bulk load batch acquires new extents bypassing the allocation lookup for existing extent with available free space to optimize insert performance. With fast inserts, bulk loads with small batch sizes can lead to increased unused space consumed by objects hence it''s recommended to use large batchsize for each batch to fill the extent completely. If increasing batchsize isn''t feasible, this trace flag can help reduce unused space reserved at the expense of performance. Applies to: SQL Server 2016 (13.x) and later versions. Scope: Global or session. ' UNION ALL
SELECT 698,'Function: SQL 9 - Performance of INSERT operations against a table with an identity column may be slow when compared to SQL 8. KB 940545' UNION ALL
SELECT 699,'Function: Turn off transaction logging for the entire SQL dataserver.' UNION ALL
SELECT 715,'Enables table lock for bulk load operations into a heap with no nonclustered indexes. When this trace flag is enabled, bulk load operations acquire bulk update (BU) locks when bulk copying data into a table. Bulk update (BU) locks allow multiple threads to bulk load data concurrently into the same table, while preventing other processes that aren''t bulk loading data from accessing the table. The behavior is similar to when the user explicitly specifies TABLOCK hint while performing bulk load, or when the sp_tableoption table lock on bulk load is enabled for a given table. However, when this trace flag is enabled, this behavior becomes default without any query or database changes. Scope: Global or session. ' UNION ALL
SELECT 806,'Function: Turn on Page Audit functionality, to verify page validity' UNION ALL
SELECT 809,'Function: SQL 8 - Limits the amount of Lazy Write activity.' UNION ALL
SELECT 815,'Function: SQL 8/9 - Enables latch enforcement. SQL Server 8 (with service pack 4) and SQL Server 9 can perform latch enforcement for data pages found in the buffer pool cache. Latch enforcement changes the virtual memory protection state while database page status changes from "clean" to "dirty" ("dirty" means modified through INSERT, UPDATE or DELETE operation). If an attempt is made to modify a data page while latch enforcement is set, it causes an exception and creates a mini-dump in SQL Server installation''s LOG directory. Microsoft support can examine the contents of such mini-dump to determine the cause of the exception. In order to modify the data page the connection must first acquire a modification latch. Once the data modification latch is acquired the page protection is changed to read-write. Once the modification latch is released the page protection changes back to read-only.' UNION ALL
SELECT 818,'Function: Turn on ringbuffer to store info about IO write operations. Used to troubleshoot IO problems' UNION ALL
SELECT 830,'Function: Disable diagnostics for stalled and stuck I/O operations' UNION ALL
SELECT 834,'Function: Large Page Allocations' UNION ALL
SELECT 835,'Function: SQL 9 / 10 - For 64 bit SQL Server. This turns off Lock pages in memory.' UNION ALL
SELECT 836,'Function: Use the max server memory option for the buffer pool' UNION ALL
SELECT 845,'Function: Enable Lock pages in Memory on Standard Edition' UNION ALL
SELECT 876,'Uses large-page allocations for columnstore. Note: Unlike Trace Flag 834, using Trace Flag 876 doesn''t preallocate SQLOS memory at instance startup, and unused memory can be released. Applies to: SQL Server 2019 (15.x) and later versions. Note: Ensure that you thoroughly test this option, before rolling it into a production environment. Scope: Global only. ' UNION ALL
SELECT 888,'Resolves performance degradation on PMEM devices with Hybrid Buffer Pool enabled in SQL Server when PMEM devices run low on memory. For more information, see KB4548103. Applies to: SQL Server 2019 (15.x) CU 4 and later versions. Scope: Global only. ' UNION ALL
SELECT 890,'Suppress long buffer pool scan complete messages (error 898) in the error log. For more information on buffer pool scan and the message that is logged in the error log, see Operations that trigger a buffer pool scan may run slowly on large-memory computers. Applies to: SQL Server 2017 (14.x) and later versions. Scope: Global only. ' UNION ALL
SELECT 898,'Disables the Direct Write behavior of the hybrid buffer pool for troubleshooting or debugging purposes. For more information, see KB5022375. Applies to: SQL Server 2022 (16.x) CU 1 and later versions. Scope: Global only. ' UNION ALL
SELECT 902,'Function: Bypass Upgrade Scripts' UNION ALL
SELECT 1106,'Function: SQL 9 - Used space in tempdb increases continuously when you run a query that creates internal objects in tempdb. KB 947204.' UNION ALL
SELECT 1117,'Function: Simultaneous Autogrowth in Multiple-file database' UNION ALL
SELECT 1118,'Function: Force Uniform Extent Allocation' UNION ALL
SELECT 1119,'Function: Turns of mixed extent allocation (Similar to 1118?)' UNION ALL
SELECT 1124,'Function: Unknown. Has been reportedly found turned on in some SQL Server instances running Dynamics AX. Also rumored to be invalid in public builds of SQL Server' UNION ALL
SELECT 1140,'Function: Fix for growing tempdb in special cases' UNION ALL
SELECT 1180,'Function: SQL 7 - Forces allocation to use free pages for text or image data and maintain efficiency of storage. 1197 applies only in the case of SQL 7 - SP3. Helpful in case when DBCC SHRINKFILE and SHRINKDATABASE commands may not work because of sparsely populated text, ntext, or image columns' UNION ALL
SELECT 1197,'Function: ' UNION ALL
SELECT 1200,'Function: Prints detailed lock information' UNION ALL
SELECT 1202,'Function: Insert blocked lock requests into syslocks.' UNION ALL
SELECT 1204,'Function: Returns info about deadlocks' UNION ALL
SELECT 1205,'Function: More detailed information about the command being executed at the time of a deadlock. Documented in SQL 7 BOL.' UNION ALL
SELECT 1206,'Function: Used to complement flag 1204 by displaying other locks held by deadlock parties.' UNION ALL
SELECT 1211,'Function: Disables Lock escalation caused by mem pressure' UNION ALL
SELECT 1216,'Function: SQL 7 - Disables Health reporting. Lock monitor when detects a (worker thread) resource level blocking scenario. If a SPID that owns a lock is currently queued to the scheduler, because all the assigned worker threads have been created and all the assigned worker threads are in an un-resolvable wait state, the following error message is written to the SQL Server error log: Error 1223: Process ID %d:%d cannot acquire lock "%s" on resource %s because a potential deadlock exists on Scheduler %d for the resource. Process ID %d:% d holds a lock "%h" on this resource.' UNION ALL
SELECT 1222,'Function: Returns Deadlock info in XML format' UNION ALL
SELECT 1224,'Function: Disables lock escalation based on number of locks' UNION ALL
SELECT 1229,'Disables all lock partitioning regardless of the number of CPUs. By default, SQL Server enables lock partitioning when a server has 16 or more CPUs, to improve the scalability characteristics of larger systems. For more information on lock partitioning, see the Transaction Locking and Row Versioning Guide. Warning: Trace Flag 1229 can cause spinlock contention and poor performance. Scope: Global only. ' UNION ALL
SELECT 1236,'Function: Fixes performance problem in scenarios with high lock activity in SQL 2012 and SQL 2014' UNION ALL
SELECT 1237,'Allows the ALTER PARTITION FUNCTION statement to honor the current user-defined session deadlock priority instead of being the likely deadlock victim by default. For more information, see KB4025261. Note: Starting with SQL Server 2017 (14.x) and database compatibility level 140 this is the default behavior, and Trace Flag 1237 has no effect. Scope: Global or session or query (QUERYTRACEON). ' UNION ALL
SELECT 1260,'Disable scheduler monitor dumps. Scope: Global only. ' UNION ALL
SELECT 1261,'Function: SQL 8 - Disables Health reporting. Lock monitor when detects a (worker thread) resource level blocking scenario. If a SPID that owns a lock is currently queued to the scheduler, because all the assigned worker threads have been created and all the assigned worker threads are in an un-resolvable wait state, the following error message is written to the SQL Server error log: Error 1229: Process ID XXX owns resources that are blocking processes on scheduler XXX.' UNION ALL
SELECT 1264,'Function: Collect process names in non-yielding scenario memory dumps' UNION ALL
SELECT 1400,'Function: Enables creation of database mirroring endpoint, which is required for setting up and using database mirroring. Scope - Startup' UNION ALL
SELECT 1448,'Function: Alters replication logreader functionality' UNION ALL
SELECT 1462,'Function: Disable Mirroring Log compression' UNION ALL
SELECT 1603,'Function: Use standard disk I/O (i.e. turn off asynchronous I/O).' UNION ALL
SELECT 1609,'Function: Turns on the unpacking and checking of remote procedure call (RPC) information in Open Data Services. Used only when applications depend on the old behavior.' UNION ALL
SELECT 1610,'Function: Boot the SQL dataserver with TCP_NODELAY enabled.' UNION ALL
SELECT 1611,'Function: If possible, pin shared memory -- check errorlog for success/failure.' UNION ALL
SELECT 1613,'Function: Set affinity of the SQL data server engine''s onto particular CPUs -- usually pins engine 0 to processor 0, engine 1 to processor 1.' UNION ALL
SELECT 1704,'Function: Prints information when a temporary table is created or dropped' UNION ALL
SELECT 1717,'Function: MSShipped bit will be set automatically at Create time when creating stored procedures' UNION ALL
SELECT 1800,'Enables SQL Server optimization when disks of different sector sizes are used for primary and secondary replica log files, in SQL Server Always On and Log Shipping environments. This trace flag is only required to be enabled on SQL Server instances with transaction log file residing on disk with sector size of 512 bytes. It''s not required to be enabled on disk with 4-KB sector sizes. For more information, see KB3009974. Applies to: SQL Server 2012 (11.x) Service Pack 1 CU 13, SQL Server 2012 (11.x) Service Pack 2 CU 3, SQL Server 2014 (12.x) RTM CU 5, and later versions. Scope: Global only. ' UNION ALL
SELECT 1802,'Function: SQL 9 - After detaching a database that resides on network-attached storage, you cannot reattach the SQL Server database. Scope STARTUP. KB 922804' UNION ALL
SELECT 1806,'Function: Disable Instant File Initialization' UNION ALL
SELECT 1807,'Function: Enable option to have database files on SMB share for SQL Server 2008 and 2008R2' UNION ALL
SELECT 1819,'Allows backup to URL to use a proxy server when accessing Azure block blobs. In addition to this trace flag, you must set the WinHTTP proxy configuration on the server with the netsh.exe utility on Windows Vista, Windows Server 2008, and later versions. Scope: Global or session or query (QUERYTRACEON). ' UNION ALL
SELECT 1903,'Function: SQL 8 - When you capture a SQL Profiler trace in a file and then you try to import the trace files into tables by using the fn_trace_gettable function no rows may be returned. KB 911678' UNION ALL
SELECT 1905,'Function: ' UNION ALL
SELECT 2301,'Function: Enable advanced decision support optimizations' UNION ALL
SELECT 2312,'Function: Forces the query optimizer to use the SQL Server 2014 version of the cardinality estimator when creating the query plan when running SQL Server 2014 with database compatibility level 110' UNION ALL
SELECT 2328,'Function: SQL 9+ - Makes cardinality estimates upon resulting SELECT ivity. The reasoning for this is that one or more of the constants may be statement parameters, which would change from one execution of the statement to the next.' UNION ALL
SELECT 2330,'Function: Stops the collection of statistics for sys.db_index_usage_stats.' UNION ALL
SELECT 2335,'Function: Generates Query Plans optimized for less memory' UNION ALL
SELECT 2338,'Causes SQL Server to use a narrow plan when executing an UPDATE statement to update indexes in a table. When you do an UPDATE against a clustered index column, SQL Server updates not only the clustered index itself, but also all the nonclustered indexes because the nonclustered indexes contain the cluster key. A common way that the update occurs is to update the clustered index, and then all nonclustered indexes at the same time. SQL Server would update one row, then move to next row until all is complete. This is called narrow plan update or also called Per-Row Update. In some cases, the Database Engine can choose to do a wide plan update. This trace flag forces a narrow plan update. Scope: Global or session or query (QUERYTRACEON). ' UNION ALL
SELECT 2340,'Function: Disable specific SORT optimization in Query Plan' UNION ALL
SELECT 2371,'Function: Change threshold for auto update stats' UNION ALL
SELECT 2372,'Function: Displays memory utilization during the optimization process' UNION ALL
SELECT 2373,'Function: Displays memory utilization during the optimization process' UNION ALL
SELECT 2382,'Function: SQL 8 - Statistics collected for system tables.' UNION ALL
SELECT 2388,'Function: Change DBCC SHOW_STATISTICS output to show stats history and lead key type such as known ascending keys' UNION ALL
SELECT 2389,'Function: Enable auto-quick-statistics update for known ascending keys' UNION ALL
SELECT 2390,'Function: Enable auto-quick-statistics update for all columns' UNION ALL
SELECT 2422,'Enables the SQL Server Database Engine to abort a request when the maximum time set by Resource Governor REQUEST_MAX_CPU_TIME_SEC configuration is exceeded. For more information, see KB4038419. Applies to: SQL Server 2016 (13.x) Service Pack 2, SQL Server 2017 (14.x) CU 3, and later versions. Scope: Global only. ' UNION ALL
SELECT 2430,'Function: Fixes performance problem when using large numbers of locks' UNION ALL
SELECT 2440,'Function: SQL 10 - Parallel query execution strategy on partitioned tables. SQL 9 used single thread per partition parallel query execution strategy. In SQL 10, multiple threads can be allocated to a single partition by turning on this flag.' UNION ALL
SELECT 2446,'Causes SQL Server to generate a Showplan XML fragment with the ParameterRuntimeValue when using the lightweight query execution statistics profiling infrastructure or executing the sys.dm_exec_query_statistics_xml DMV while troubleshooting long running queries. Applies to: SQL Server 2017 (14.x) CU 31, SQL Server 2019 (15.x) CU 19, and SQL Server 2022 (16.x) and later versions. Warning: Trace Flag 2446 isn''t meant to be enabled continuously in a production environment, but only for time-limited troubleshooting purposes. Using this trace flag introduces additional and possibly significant CPU and memory overhead as we create a Showplan XML fragment with runtime parameter information, whether the sys.dm_exec_query_statistics_xml DMV is called or not. Note: Starting with SQL Server 2022 (16.x), to accomplish this at the database level see the FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION option in ALTER DATABASE SCOPED CONFIGURATION. Scope: Global only. ' UNION ALL
SELECT 2451,'Enables the equivalent of the last actual execution plan in sys.dm_exec_query_plan_stats. Applies to: SQL Server 2019 (15.x) and later versions. Note: Starting with SQL Server 2019 (15.x) to accomplish this at the database level, see the LAST_QUERY_PLAN_STATS option in ALTER DATABASE SCOPED CONFIGURATION. Scope: Global only. ' UNION ALL
SELECT 2453,'Function: Allow a table variable to trigger recompile when enough number of rows are changed with may allow the query optimizer to choose a more efficient plan.' UNION ALL
SELECT 2467,'Enables an alternate parallel worker thread allocation policy, based on which node has the least allocated threads. For more information, see Parallel Query Processing. Refer to Configure the max worker threads (server configuration option) for information on configuring the max worker threads server option. Note: Query degree of parallelism (DOP) has to fit into a single node for this alternate policy to be used, or the default thread allocation policy is used instead. Using this trace flag, it isn''t recommended to execute queries specifying a DOP over the number of schedulers in a single node, as this could interfere with queries specifying a DOP below or equal to the number of schedulers in a single node. Note: Ensure that you thoroughly test this option, before rolling it into a production environment. Scope: Global only. ' UNION ALL
SELECT 2469,'Enables alternate exchange for INSERT INTO ... SELECT into a partitioned columnstore index. For more information, see KB3204769. Scope: Global or session or query (QUERYTRACEON). ' UNION ALL
SELECT 2470,'Function: Fixes performance problem when using AFTER triggers on partitioned tables' UNION ALL
SELECT 2505,'Function: SQL 7 - Prevents DBCC TRACEON 208, SPID 10 errors from appearing in the error log. KB 243352' UNION ALL
SELECT 2508,'Function: Disables parallel non-clustered index checking for DBCC CHECKTABLE.' UNION ALL
SELECT 2509,'Function: Used with DBCC CHECKTABLE to see the total count of forward records in a table' UNION ALL
SELECT 2514,'Function: Verbose Merge Replication logging to msmerge_history table for troubleshooting Merger repl performance' UNION ALL
SELECT 2520,'Function: Force DBCC HELP to return syntax of undocumented DBCC statements. If 2520 is not turned on, DBCC HELP will refuse to give you the syntax stating: "No help available for DBCC statement ''undocumented statement''".' UNION ALL
SELECT 2521,'Function: SQL 7 SP2 - Facilitates capturing a Sqlservr.exe user-mode crash dump for postmortem analysis.' UNION ALL
SELECT 2528,'Function: Disables parallellism in CHECKDB etc.' UNION ALL
SELECT 2529,'Function: Displays memory usage for DBCC commands when used with TF 3604.' UNION ALL
SELECT 2537,'Function: Allows you to see inactive records in transactionlog using fn_dblog' UNION ALL
SELECT 2540,'Function: Unknown, but related to controlling the contents of a memorydump' UNION ALL
SELECT 2541,'Function: Unknown, but related to controlling the contents of a memorydump' UNION ALL
SELECT 2542,'Function: Unknown, but related to controlling the contents of a memorydump' UNION ALL
SELECT 2543,'Function: Unknown, but related to controlling the contents of a memorydump' UNION ALL
SELECT 2544,'Function: Produces a full memory dump' UNION ALL
SELECT 2545,'Function: Unknown, but related to controlling the contents of a memorydump' UNION ALL
SELECT 2546,'Function: Dumps all threads for SQL Server in the dump file' UNION ALL
SELECT 2547,'Function: Unknown, but related to controlling the contents of a memorydump' UNION ALL
SELECT 2548,'Function: Shrink will run faster with this trace flag if there are LOB pages that need conversion and/or compaction, because that actions will be skipped.' UNION ALL
SELECT 2549,'Function: Faster CHECKDB' UNION ALL
SELECT 2550,'Function: Unknown, but related to controlling the contents of a memorydump' UNION ALL
SELECT 2551,'Function: Produces a filtered memory dump' UNION ALL
SELECT 2552,'Function: Unknown, but related to controlling the contents of a memorydump' UNION ALL
SELECT 2553,'Function: Unknown, but related to controlling the contents of a memorydump' UNION ALL
SELECT 2554,'Function: Unknown, but related to controlling the contents of a memorydump' UNION ALL
SELECT 2555,'Function: Unknown, but related to controlling the contents of a memorydump' UNION ALL
SELECT 2556,'Function: Unknown, but related to controlling the contents of a memorydump' UNION ALL
SELECT 2557,'Function: Unknown, but related to controlling the contents of a memorydump' UNION ALL
SELECT 2558,'Function: Unknown, but related to controlling the contents of a memorydump' UNION ALL
SELECT 2559,'Function: Unknown, but related to controlling the contents of a memorydump' UNION ALL
SELECT 2562,'Function: Faster CHECKDB' UNION ALL
SELECT 2566,'Function: SQL 9 - DBCC CHECKDB takes longer to run on an x64-based computer compared to a 32-bit computer. Kb - 945770' UNION ALL
SELECT 2588,'Function: Get more information about undocumented DBCC commands' UNION ALL
SELECT 2592,'Enables symbol resolution on stack dumps when the Debugging Tools for Windows are installed. For example, using Trace Flag 3656 requires that Trace Flag 2592 is enabled. Warning: This is a debugging trace flag and not meant for production environment use. Applies to: SQL Server 2019 (15.x) and later versions. Scope: Global and session. ' UNION ALL
SELECT 2610,'Enables memory dump compression and faster dump generation with SQLDumper and via DBCC STACKDUMP. For more information, see Use the Sqldumper.exe utility to generate a dump file in SQL Server. Applies to: SQL Server 2022 (16.x) CU 8, SQL Server 2019 (15.x) CU 23, and later versions. Scope: Global and session. ' UNION ALL
SELECT 2616,'Enables the stack signature feature to make Sqldumper.exe generate a single dump per unique stack signature per hour, which avoids potential dump flooding problems when the same issue repeats frequently within one hour. When this trace flag is enabled, the format of the dump file will be changed from SQLDump<xxxx>.mdmp or SQLDmpr<xxxx>.mdmp to SQLDmpr<xxxx>.P<xxxxx.xxxxxxxx>.T<xxxxxxxxxxxxxx>.{<xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx>}.dmp (for example, SQLDmpr0024.P26900.66D498FA.T20240117034050.{eec59a9e-d615-4ac4-a46a-f650fee23787}.dmp). Applies to: SQL Server 2022 (16.x) CU 12 and later versions. Scope: Global only. ' UNION ALL
SELECT 2701,'Function: SQL 6.5 - Sets the @@ERROR system function to 50000 for RAISERROR messages with severity levels of 10 or less. When disabled, sets the @@ERROR system function to 0 for RAISERROR messages with severity levels of 10 or less.' UNION ALL
SELECT 2861,'Function: Keep zero cost plans in cache' UNION ALL
SELECT 3001,'Function: Stops sending backup entries into MSDB.' UNION ALL
SELECT 3004,'Function: Returns more info about Instant File Initialization' UNION ALL
SELECT 3005,'Function: Redirects output from 3004 to the error log.' UNION ALL
SELECT 3014,'Function: Returns more info about backups to the errorlog' UNION ALL
SELECT 3023,'Function: Enable the CHECKSUM option if backup utilities do not expose the option' UNION ALL
SELECT 3028,'Function: ' UNION ALL
SELECT 3031,'Function: SQL 9 - Will turn the NO_LOG and TRUNCATE_ONLY options into checkpoints in all recovery modes.' UNION ALL
SELECT 3042,'Function: Alters backup compression functionality' UNION ALL
SELECT 3051,'Enables SQL Server Backup to URL logging for page blobs in Azure Storage only. Logging writes to a specific error log file. For more information, see SQL Server Backup to URL Best Practices and Troubleshooting. Scope: Global only. ' UNION ALL
SELECT 3101,'Function: Fix performance problems
Author: ChatGPT
Your SQL script checks for active trace flags using `DBCC TRACESTATUS (-1)`, then joins the result with a predefined list of known trace flags and their descriptions.
---
### **???? Issues & Improvements**
1. **Avoid Direct Dynamic Execution (`EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')`)**
- Instead, use:
```sql
INSERT INTO #Traces(&#91;TraceFlag], &#91;TraceStatus], &#91;IsGlobal], &#91;IsSession])
DBCC TRACESTATUS (-1) WITH NO_INFOMSGS;
```
- This prevents security concerns related to dynamic execution.
2. **Ensure `tempdb` Cleanup is Effective**
- Modify this part:
```sql
IF OBJECT_ID('tempdb..#Traces') IS NOT NULL DROP TABLE #Traces;
```
- The `&#91;dbo]` schema is unnecessary in `tempdb`.
3. **Use `LEFT JOIN` Instead of `WITH CTE` for Trace Flag Lookup**
- Instead of:
```sql
;WITH MyCTE(TraceFlag, Descrip) AS (SELECT ... )
```
- Use:
```sql
CREATE TABLE #TraceDescriptions (TraceFlag INT PRIMARY KEY, Descrip NVARCHAR(4000));
INSERT INTO #TraceDescriptions VALUES (101, 'Verbose Merge Replication logging ...');
-- Add more trace flags here...
SELECT T.TraceFlag, T.TraceStatus, T.IsGlobal, T.IsSession, D.Descrip
FROM #Traces T
LEFT JOIN #TraceDescriptions D ON T.TraceFlag = D.TraceFlag;
```
4. **Optimize Performance**
- If running on **high-load production**, consider:
```sql
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
```
5. **Consider Output to `sysadmin` or Logging Table**
- If **monitoring** trace flags, periodically **store results in a table**:
```sql
INSERT INTO DBA_Monitoring.TraceFlagHistory
SELECT GETDATE(), @@SERVERNAME, TraceFlag, TraceStatus, Descrip
FROM #Traces T
LEFT JOIN #TraceDescriptions D ON T.TraceFlag = D.TraceFlag;
```
---
### **???? Enhanced Query**
```sql
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
IF OBJECT_ID('tempdb..#Traces') IS NOT NULL DROP TABLE #Traces;
CREATE TABLE #Traces (&#91;TraceFlag] INT, &#91;TraceStatus] INT, &#91;IsGlobal] INT, &#91;IsSession] INT);
-- Collect active trace flags
INSERT INTO #Traces (&#91;TraceFlag], &#91;TraceStatus], &#91;IsGlobal], &#91;IsSession])
DBCC TRACESTATUS(-1) WITH NO_INFOMSGS;
-- Create Trace Flag Descriptions Table
IF OBJECT_ID('tempdb..#TraceDescriptions') IS NOT NULL DROP TABLE #TraceDescriptions;
CREATE TABLE #TraceDescriptions (TraceFlag INT PRIMARY KEY, Descrip NVARCHAR(4000));
INSERT INTO #TraceDescriptions VALUES
(101, 'Verbose Merge Replication logging output for troubleshooting'),
(102, 'Verbose Merge Replication logging to msmerge_history table'),
(105, 'Allows joining more than 16 tables in SQL Server 6.5'),
(460, 'Replaces data truncation message ID 8152 with message ID 2628'),
(1117, 'Simultaneous autogrowth in multiple-file database'),
(1118, 'Force uniform extent allocation'),
(2371, 'Change threshold for auto update stats'),
(2548, 'Shrink will run faster, skipping LOB page conversion');
-- Get Trace Flag Info
SELECT
T.TraceFlag,
T.TraceStatus,
T.IsGlobal,
T.IsSession,
COALESCE(D.Descrip, 'Unknown Trace Flag') AS Description
FROM #Traces T
LEFT JOIN #TraceDescriptions D ON T.TraceFlag = D.TraceFlag;
```
---
### **???? Next Steps**
- **Want to log trace flag changes over time?**
- Store results in a DBA monitoring table.
- **Need specific trace flags analyzed?**
- I can **suggest best practices** based on your SQL version.
- **Want alerts if a certain trace flag is missing?**
- Set up an **SQL Agent job** to check periodically.
Let me know how you'd like to refine this! ????
Author: user
SELECT * FROM sys.configurations
WHERE name IN(
'max server memory (MB)',
'fill factor (%)',
'cost threshold for parallelism',
'max degree of parallelism',
'optimize for ad hoc workloads'
)
Author: user
SELECT * FROM sys.configurations
WHERE name IN(
'max server memory (MB)',
'fill factor (%)',
'cost threshold for parallelism',
'max degree of parallelism',
'optimize for ad hoc workloads'
)
Author: ChatGPT
The query you provided retrieves SQL Server configuration settings related to performance tuning. Here???s what each of these settings controls:
1. **max server memory (MB)** ??? Specifies the maximum amount of memory that SQL Server can allocate for its buffer pool. Adjusting this helps prevent SQL Server from consuming all available memory on the server.
2. **fill factor (%)** ??? Determines how much space to leave free in index pages during creation or rebuilding. A lower fill factor reduces page splits but increases storage space usage.
3. **cost threshold for parallelism** ??? Defines the estimated query cost threshold before SQL Server considers parallel execution plans.
4. **max degree of parallelism** ??? Controls the maximum number of CPU cores SQL Server can use for parallel query execution.
5. **optimize for ad hoc workloads** ??? Reduces memory pressure by storing only a plan stub instead of a full execution plan for single-use queries.
Would you like guidance on how to adjust these settings for optimal performance based on your workload?
Author: user
DECLARE @Layer VARCHAR(30); SET @Layer = '&#91;CMSServerLayer]'
;WITH ServerCompat
AS
(
SELECT 0 AS SortOrder,
CASE Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)
+ '.'
+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
WHEN '8.00' THEN 'SQL2000'
WHEN '9.00' THEN 'SQL2005'
WHEN '10.0' THEN 'SQL2008'
WHEN '10.50' THEN 'SQL2008R2'
WHEN '11.0' THEN 'SQL2012'
WHEN '12.0' THEN 'SQL2014'
WHEN '13.0' THEN 'SQL2016'
WHEN '14.0' THEN 'SQL2017'
WHEN '15.0' THEN 'SQL2019'
WHEN '16.0' THEN 'SQL2022'
WHEN '17.0' THEN 'SQL2025'
ELSE Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)
+ '.'
+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
END AS ServerCompatibilityLevel,
CASE Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)
+ '.'
+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
WHEN '8.00' THEN '80'
WHEN '9.00' THEN '90'
WHEN '10.0' THEN '100'
WHEN '10.50' THEN '100'
WHEN '11.0' THEN '110'
WHEN '12.0' THEN '120'
WHEN '13.0' THEN '130'
WHEN '14.0' THEN '140'
WHEN '15.0' THEN '150'
WHEN '16.0' THEN '160'
WHEN '17.0' THEN '170'
ELSE Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)
+ '.'
+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
END AS compatibility_level ,'SERVER' AS ServerVersion
FROM sys.databases WHERE name='master'
)
,dbCompat
AS
( SELECT
CASE dbz.compatibility_level
WHEN 170 THEN 'SQL2025'
WHEN 160 THEN 'SQL2022'
WHEN 150 THEN 'SQL2019'
WHEN 140 THEN 'SQL2017'
WHEN 130 THEN 'SQL2016'
WHEN 120 THEN 'SQL2014'
WHEN 110 THEN 'SQL2012'
WHEN 105 THEN 'SQL2008R2'
WHEN 100 THEN 'SQL2008'
WHEN 90 THEN 'SQL2005'
WHEN 80 THEN 'SQL2000'
END AS DatabaseCompatibilityLevel,dbz.compatibility_level,dbz.name AS DatabaseName
FROM sys.databases dbz where dbz.source_database_id is null
)
SELECT
@Layer AS ServerLayer,
CONVERT(VARCHAR(128),@@SERVERNAME) AS ServerName,
svr.&#91;ServerCompatibilityLevel],
&#91;dbz].&#91;DatabaseCompatibilityLevel],
&#91;dbz].&#91;DatabaseName],
&#91;dbz].&#91;compatibility_level],
svr.&#91;compatibility_level] AS ShouldBeVersion,
CASE
WHEN dbz.DatabaseCompatibilityLevel <> svr.&#91;ServerCompatibilityLevel]
THEN 'ALTER DATABASE ' + QUOTENAME(&#91;dbz].&#91;DatabaseName]) + ' SET COMPATIBILITY_LEVEL = ' + CONVERT(VARCHAR(30),svr.&#91;compatibility_level]) + ';'
ELSE ' -- Database Compatibility for ' + QUOTENAME(&#91;dbz].&#91;DatabaseName]) + ' is correct.'
END AS cmd
FROM dbCompat dbz
CROSS JOIN ServerCompat svr
--WHERE dbz.DatabaseCompatibilityLevel <> svr.&#91;ServerCompatibilityLevel]
--AND svr.&#91;compatibility_level] <> &#91;dbz].&#91;compatibility_level]
ORDER BY dbz.compatibility_level,dbz.DatabaseName
Author: ChatGPT
Your script retrieves the **SQL Server version** and **database compatibility levels**, and generates SQL statements to adjust database compatibility where necessary.
### **How it Works**
1. **ServerCompat CTE**:
- Determines the **SQL Server version** based on `SERVERPROPERTY('productversion')`.
- Extracts the **major and minor version numbers** and maps them to a friendly version name (e.g., `SQL2019` for version `15.0`).
- Sets the **default compatibility level** expected for the SQL Server version.
2. **dbCompat CTE**:
- Retrieves all databases except **snapshot databases** (`source_database_id IS NULL`).
- Maps each database's `compatibility_level` to its corresponding SQL Server version.
3. **Final Query**:
- Joins `dbCompat` (database-level compatibility) with `ServerCompat` (server-level compatibility).
- Compares **database compatibility level** against the **expected compatibility level** of the server.
- **Generates `ALTER DATABASE` commands** where the compatibility level is outdated.
---
### **Example Output**
| ServerLayer | ServerName | ServerCompatibilityLevel | DatabaseCompatibilityLevel | DatabaseName | compatibility_level | ShouldBeVersion | cmd |
|----------------|-------------|--------------------------|----------------------------|--------------|---------------------|----------------|------------------------------------------------------|
| &#91;CMSServerLayer] | MyServer\SQL2019 | SQL2019 | SQL2016 | MyDatabase | 130 | 150 | `ALTER DATABASE &#91;MyDatabase] SET COMPATIBILITY_LEVEL = 150;` |
| &#91;CMSServerLayer] | MyServer\SQL2019 | SQL2019 | SQL2019 | ReportDB | 150 | 150 | `-- Database Compatibility for &#91;ReportDB] is correct.` |
---
### **Enhancements and Fixes**
??? **Handles future versions**: Includes SQL Server 2025 (`17.0` and `170`).
??? **Ensures no snapshot databases** are included.
??? **Generates the `ALTER DATABASE` command only when necessary**.
??? **Allows filtering databases that need updates** (commented `WHERE` clause).
Would you like further optimizations or additional checks? ????
Author: user
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
IF OBJECT_ID('tempdb.&#91;dbo].&#91;#CPUDataMem]') IS NOT NULL
DROP TABLE &#91;dbo].&#91;#CPUDataMem]
CREATE TABLE #CPUDataMem (cpu_count int, hyperthread_ratio int,physicialCPUSockets int,PhysicalMemoryMB money,MaxSQLMemoryMB money,VirtualMachine varchar(50),VirtualMachineType varchar(50))
--SQL2014 and above, column is different
--select Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4) + '.' + Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
IF CONVERT(DECIMAL(5,2),PARSENAME(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)
+ '.'
+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
)>=11.0
BEGIN
INSERT INTO #CPUDataMem(&#91;cpu_count],&#91;hyperthread_ratio],&#91;physicialCPUSockets],&#91;PhysicalMemoryMB],&#91;VirtualMachine],&#91;VirtualMachineType])
EXEC(
'SELECT
s.cpu_count ,
s.hyperthread_ratio ,
s.cpu_count / s.hyperthread_ratio As NumberofCores,
s.physical_memory_kb/1000.0 As MemoryinMb,
s.virtual_machine_type_desc,
CASE WHEN s.virtual_machine_type > 0 THEN ''Virtual'' ELSE '''' END As IsVirtual--1 = hypervisor, 2 = other type of virtual
FROM sys.dm_os_sys_info s');
END
ELSE
BEGIN
INSERT INTO #CPUDataMem(&#91;cpu_count],&#91;hyperthread_ratio],&#91;physicialCPUSockets],&#91;PhysicalMemoryMB],&#91;VirtualMachine],&#91;VirtualMachineType])
EXEC(
'SELECT
s.cpu_count ,
s.hyperthread_ratio ,
s.cpu_count / s.hyperthread_ratio As NumberofCores,
s.physical_memory_in_bytes/1048576.0 As MemoryinMb,
'''' AS virtual_machine_type_desc,
'''' As IsVirtual
FROM sys.dm_os_sys_info s');
END
UPDATE #CPUDataMem SET &#91;MaxSQLMemoryMB] = (SELECT CONVERT(MONEY,&#91;value]) FROM sys.&#91;configurations] WHERE &#91;name] = 'max server memory (MB)')
;WITH T1
AS
(
select
CONVERT(VARCHAR(128),@@SERVERNAME) AS ServerName,
ISNULL(db_name(database_id),'Total') as &#91;DatabaseName],
CONVERT(numeric(19,2),count(page_id)/128.0) as Mb
FROM sys.dm_os_buffer_descriptors with (nolock)
where database_id !=32767
group by database_id
with ROLLUP
)
SELECT * ,
CASE WHEN &#91;DatabaseName] = 'Total' THEN &#91;PhysicalMemoryMB] - Mb ELSE NULL END AS TotalFree,
CONVERT(DECIMAL(19,2),(Mb / &#91;PhysicalMemoryMB]) * 100) AS TotalServerPercentUsed,
CONVERT(DECIMAL(19,2),(Mb / &#91;MaxSQLMemoryMB]) * 100) AS AssignedSQLPercentUsed
FROM &#91;T1]
CROSS APPLY (SELECT &#91;PhysicalMemoryMB],&#91;MaxSQLMemoryMB] FROM &#91;#CPUDataMem]) mem
ORDER BY CASE WHEN &#91;DatabaseName] = 'Total' THEN 1 ELSE 2 END,Mb DESC
Author: ChatGPT
### **Breakdown of Your Script**
This script gathers CPU, memory, and SQL Server memory usage statistics. It provides an overview of system resources, SQL Server memory allocation, and buffer pool consumption for each database.
---
### **Steps Explained**
1. **Create Temporary Table** `#CPUDataMem`:
- Stores **CPU and memory information** such as:
- Number of CPUs (`cpu_count`)
- Hyperthreading ratio (`hyperthread_ratio`)
- Physical CPU sockets (`physicialCPUSockets`)
- Physical memory (`PhysicalMemoryMB`)
- Max SQL memory setting (`MaxSQLMemoryMB`)
- Virtualization information (`VirtualMachine` and `VirtualMachineType`)
2. **Insert Data into `#CPUDataMem`**:
- Uses **`sys.dm_os_sys_info`**:
- If SQL Server **2012 (11.0) or higher**, retrieves:
- `cpu_count`
- `hyperthread_ratio`
- `NumberofCores`
- `PhysicalMemoryMB`
- `virtual_machine_type_desc`
- Determines if the server is virtual (`IsVirtual`)
- For **older versions**, retrieves:
- The same CPU metrics but without virtualization details.
3. **Update `MaxSQLMemoryMB` in `#CPUDataMem`**:
- Extracts the **`max server memory (MB)`** setting from `sys.configurations`.
4. **Calculate SQL Server Memory Usage**:
- Uses **`sys.dm_os_buffer_descriptors`** to calculate memory usage by database.
- Groups results using `ROLLUP`:
- Each **database's memory usage in MB**.
- The **total buffer pool size** (`Total` row).
5. **Compute Additional Metrics**:
- `TotalFree` ??? **Remaining physical memory after SQL Server usage**.
- `TotalServerPercentUsed` ??? **Percentage of total system memory used by SQL Server**.
- `AssignedSQLPercentUsed` ??? **Percentage of max SQL memory (`max server memory (MB)`) used**.
---
### **Example Output**
| ServerName | DatabaseName | Mb (Memory Used) | TotalFree | TotalServerPercentUsed | AssignedSQLPercentUsed |
|------------|-------------|------------------|-----------|------------------------|------------------------|
| MyServer | master | 150 MB | NULL | 5.50% | 12.50% |
| MyServer | tempdb | 500 MB | NULL | 18.33% | 41.67% |
| MyServer | ReportDB | 1200 MB | NULL | 44.00% | 100.00% |
| MyServer | Total | 2700 MB | 1300 MB | 99.99% | 100.00% |
---
### **Enhancements & Fixes**
??? **Ensures compatibility for older SQL versions**.
??? **Handles virtualization detection (SQL 2012+ only)**.
??? **Uses `ROLLUP` for a total row, making summary calculations easier**.
??? **Cross applies system memory to buffer pool stats for accurate comparison**.
??? **Orders results to show total first, then databases sorted by usage**.
This script is **great for performance monitoring and memory diagnostics** in SQL Server! ???? Would you like any modifications?
Author: user
lets modify teh expected output again. Lets try to describe what the purpose of the script is, and it's advantages, without mentioning details like the structure about temp tables, etc
Author: ChatGPT
### **Purpose of the Script**
This script provides a comprehensive analysis of **CPU and memory usage in SQL Server**, helping to assess system resource consumption and SQL Server's memory allocation efficiency. It retrieves real-time data on **CPU cores, hyperthreading, total and available memory, and SQL Server???s memory usage**, offering insights into how efficiently resources are utilized.
### **Advantages & Benefits**
??? **Quick Performance Assessment** ??? Helps identify potential memory pressure or CPU constraints affecting SQL Server performance.
??? **Database-Level Memory Usage** ??? Shows how much buffer pool memory each database consumes, allowing for informed resource allocation.
??? **Virtualization Awareness** ??? Detects whether SQL Server is running on a virtualized environment, which can impact performance tuning decisions.
??? **Comparison of Allocated vs. Used Memory** ??? Highlights the difference between configured SQL Server memory limits (`max server memory`) and actual consumption.
??? **Optimized for Large Environments** ??? Uses minimal impact queries with `READ UNCOMMITTED` isolation, ensuring it runs efficiently even in busy production systems.
??? **Helps Capacity Planning** ??? Assists in evaluating whether memory or CPU upgrades are necessary based on current usage trends.
This script is a **valuable diagnostic tool** for **DBAs and performance engineers**, providing critical insights into system health and SQL Server resource utilization at a glance. ????
Author: user
CREATE TABLE #FileSize
(dbName NVARCHAR(128),
FileName NVARCHAR(128),
type_desc NVARCHAR(128),
CurrentSizeMB DECIMAL(10,2),
FreeSpaceMB DECIMAL(10,2)
);
INSERT INTO #FileSize(dbName, FileName, type_desc, CurrentSizeMB, FreeSpaceMB)
exec sp_msforeachdb
'use &#91;?];
SELECT DB_NAME() AS DbName,
name AS FileName,
type_desc,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);';
SELECT
dbName As DatabaseName,
SUM(CurrentSizeMB) AS CurrentSizeMB,
SUM(CurrentSizeMB) - SUM(FreeSpaceMB) As UsedSpaceMB,
SUM(FreeSpaceMB) AS FreeSpaceMB
FROM #FileSize
WHERE dbName NOT IN ('distribution', 'master', 'model', 'msdb','tempdb')
GROUP BY dbName
ORDER BY dbName
Author: ChatGPT
### **Purpose of the Script**
This script provides an **overview of database storage usage in SQL Server**, helping administrators monitor file sizes, free space, and used space for each database. It gathers information on both **data files (MDF/NDF) and log files (LDF)**, making it a valuable tool for **capacity planning and storage management**.
### **Advantages & Benefits**
??? **Comprehensive Storage Overview** ??? Summarizes total, used, and free space for all user databases.
??? **Efficient Free Space Analysis** ??? Helps identify databases with excessive free space, indicating potential for shrinking or reallocation.
??? **Proactive Capacity Planning** ??? Assists DBAs in forecasting when additional disk space may be required.
??? **Excludes System Databases** ??? Filters out `master`, `model`, `msdb`, and `tempdb`, focusing on user databases.
??? **Low Overhead Execution** ??? Uses `sp_MSforeachdb` to iterate over databases efficiently.
??? **Supports Both Data and Log Files** ??? Captures storage details for both data (`MDF/NDF`) and transaction log (`LDF`) files.
This script is ideal for **database storage monitoring** and **ensuring optimal disk space utilization** in SQL Server environments. ????
Author: user
USE master;
DBCC TRACEON(3604)
IF OBJECT_ID('tempdb.&#91;dbo].&#91;#dbccPageResults]') IS NOT NULL
DROP TABLE &#91;dbo].&#91;#dbccPageResults]
CREATE TABLE &#91;dbo].&#91;#dbccPageResults] (
&#91;ServerName] VARCHAR(128) NULL,
&#91;DatabaseName] VARCHAR(128) NULL,
&#91;CreatedDescription] VARCHAR(128) NULL,
&#91;CreatedVersion] VARCHAR(128) NULL,
&#91;CurrentVersion] VARCHAR(128) NULL,
&#91;CurrentCompat] VARCHAR(128) NULL,
&#91;ParentObject] NVARCHAR(255) NULL,
&#91;Object] NVARCHAR(255) NULL,
&#91;Field] NVARCHAR(255) NULL,
&#91;VALUE] NVARCHAR(255) NULL)
EXECUTE sp_msForEachDB '
INSERT INTO #dbccPageResults (&#91;ParentObject], &#91;Object], &#91;Field], &#91;VALUE])
EXECUTE(''DBCC PAGE(''''?'''',1,9,3) WITH TABLERESULTS'')
DELETE FROM #dbccPageResults WHERE field NOT IN(''dbi_version'',''dbi_createVersion'')
UPDATE #dbccPageResults SET ServerName = @@ServerName,DatabaseName = ''?'' WHERE DatabaseName IS NULL
'
UPDATE MyTarget
SET MyTarget.CreatedVersion = MyLookup.dbi_createVersion,
MyTarget.CurrentVersion = MyLookup.dbi_version,
MyTarget.&#91;CreatedDescription] = CASE MyLookup.dbi_createVersion
WHEN 408 THEN 'SQL 6.5'
WHEN 515 THEN 'SQL 7.0'
WHEN 539 THEN 'SQL 2000'
WHEN 611 THEN 'SQL 2005'
WHEN 612 THEN 'SQL 2005'
WHEN 655 THEN 'SQL 2008'
WHEN 660 THEN 'SQL 2008R2'
WHEN 661 THEN 'SQL 2008R2'
WHEN 684 THEN 'SQL 2012'
WHEN 706 THEN 'SQL 2012'
WHEN 782 THEN 'SQL 2014'
WHEN 836 THEN 'SQL 2016'
WHEN 852 THEN 'SQL 2016'
WHEN 862 THEN 'SQL 2016'
WHEN 868 THEN 'SQL 2017'
WHEN 869 THEN 'SQL 2017'
WHEN 895 THEN 'SQL 2019'
WHEN 896 THEN 'SQL 2019'
WHEN 897 THEN 'SQL 2019'
WHEN 902 THEN 'SQL 2019'
WHEN 904 THEN 'SQL 2019'
WHEN 927 THEN 'SQL 2022'
WHEN 950 THEN 'SQL 2022'
WHEN 957 THEN 'SQL 2022'
ELSE 'SQL 20??'
END
FROM #dbccPageResults MyTarget
INNER JOIN (
SELECT
DatabaseName,
MAX(CASE WHEN Field = 'dbi_createVersion' THEN VALUE ELSE NULL END) AS dbi_createVersion,
MAX(CASE WHEN Field = 'dbi_version' THEN VALUE ELSE NULL END) AS dbi_version
FROM #dbccPageResults MyTarget
GROUP BY MyTarget.DatabaseName) MyLookup
ON MyTarget.DatabaseName = MyLookup.DatabaseName
UPDATE MyTarget SET MyTarget.CurrentCompat = MySource.compatibility_level
--SELECT MyTarget.ServerName,MySource.compatibility_level
FROM #dbccPageResults MyTarget
INNER JOIN sys.databases MySource On MyTarget.DatabaseName = MySource.Name
SELECT * FROM #dbccPageResults
Author: ChatGPT
### **Purpose of the Script**
This script is designed to **retrieve and analyze SQL Server database versioning details** by extracting internal database metadata using **DBCC PAGE**. It helps database administrators determine **the original SQL Server version in which a database was created** and its **current version and compatibility level**.
### **Key Benefits & Use Cases**
??? **Detect Database Creation Version** ??? Determines the SQL Server version where each database was originally created, useful for upgrades and migrations.
??? **Identify Current Database Version** ??? Verifies if the database has been upgraded to a newer SQL Server version.
??? **Check Compatibility Levels** ??? Ensures that databases are running at an optimal compatibility level for performance and feature support.
??? **Supports SQL Server Upgrade Assessments** ??? Helps in evaluating databases before or after an **in-place upgrade** or **migration**.
??? **Automated Multi-Database Execution** ??? Uses `sp_msForEachDB` to iterate through all databases, collecting information efficiently.
This script is **particularly useful** for SQL Server **version audits**, **troubleshooting compatibility issues**, and **preparing databases for upgrade planning**. ????
Author: user
WITH ServerData
AS
(
SELECT
SQLVersionNum = (SELECT CONVERT(INT,SUBSTRING(CONVERT(VARCHAR,SERVERPROPERTY('ProductVersion')),1,2))),
ISNULL(CASE ISNULL(PARSENAME(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4),'')
+ '.'
+ ISNULL(Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3),'')
WHEN '.8' THEN 'SQL 2000'
WHEN '9.00' THEN 'SQL 2005'
WHEN '10.0' THEN 'SQL 2008'
WHEN '10.50' THEN 'SQL 2008R2'
WHEN '11.0' THEN 'SQL 2012'
WHEN '12.0' THEN 'SQL 2014'
WHEN '13.0' THEN 'SQL 2016'
WHEN '14.0' THEN 'SQL 2017'
WHEN '15.0' THEN 'SQL 2019'
WHEN '16.0' THEN 'SQL 2022'
WHEN '17.0' THEN 'SQL 2025'
ELSE ISNULL(PARSENAME(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4),'')
+ '.'
+ ISNULL(Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3),'')
END,'??') AS SQLVersion,
NumaNodes = (SELECT COUNT(DISTINCT memory_node_id) FROM sys.dm_os_memory_clerks WHERE memory_node_id!=64),
NumCPUs = (SELECT COUNT(scheduler_id) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'),
CurMaxDop = (SELECT value_in_use from sys.configurations where name ='max degree of parallelism')
)
SELECT
CONVERT(VARCHAR(128),@@SERVERNAME) AS ServerName,
&#91;ServerData].SQLVersion,
CASE WHEN &#91;ServerData].SQLVersionNum >=13 THEN 'Note: SQL2016 and above supports database_scoped MAXDOP in addition to Server MaxDop' ELSE '' END AS Notes,
CASE
WHEN NumaNodes = 1 AND NumCPUs < 8
THEN 'Only One Numa Node, Less than 8 Cores = Recommendation isTotal Number of Cores '
WHEN NumaNodes = 1 AND NumCPUs >= 8
THEN 'Only One Numa Node, More than 8 Cores = 8, Recommendation for Maxdop is 8'
WHEN NumCPUs / NumaNodes < 8
THEN 'multiple Numa Nodes, Less than 8 Cores = 8, Recommendation for Maxdop NumCPUs / NumaNodes'
ELSE 'multiple Numa Nodes, NumCPUs / NumaNodes > 8,Recommendation for Maxdop recommendation is 8'
END AS RecommendedMaxDopReason,
CASE
WHEN NumaNodes = 1 AND NumCPUs < 8
THEN NumCPUs
WHEN NumaNodes = 1 AND NumCPUs >= 8
THEN 8
WHEN NumCPUs / NumaNodes < 8
THEN NumCPUs / NumaNodes
ELSE 8
END AS RecommendedMaxDop,&#91;ServerData].&#91;CurMaxDop],&#91;ServerData].&#91;NumaNodes],&#91;ServerData].&#91;NumCPUs],&#91;ServerData].NumCPUs / &#91;ServerData].NumaNodes AS &#91;NumCPUs / NumaNodes]
FROM &#91;ServerData]
Author: ChatGPT
### **Purpose of the Script**
This script is designed to **assess and recommend the optimal MAXDOP (Maximum Degree of Parallelism) setting** for a SQL Server instance based on its **NUMA node configuration and CPU count**. The MAXDOP setting is crucial for **query performance tuning** as it controls the number of CPU cores used for parallel query execution.
### **Key Benefits & Use Cases**
??? **Determines SQL Server Version** ??? Identifies the SQL Server version (2000 - 2025) running on the instance.
??? **Detects NUMA Node and CPU Configuration** ??? Analyzes the server's CPU topology and NUMA architecture.
??? **Provides MAXDOP Recommendations** ??? Based on **Microsoft best practices**, suggests the ideal MAXDOP value to optimize query performance.
??? **Highlights Database-Scoped MAXDOP** ??? Notes that **SQL Server 2016 and later** allow per-database MAXDOP settings in addition to the server-level configuration.
??? **Improves Query Execution Efficiency** ??? Helps reduce **excessive parallelism** (causing CPU pressure) or **insufficient parallelism** (causing slow queries).
??? **Quickly Audits Current MAXDOP Setting** ??? Compares the **current MAXDOP configuration** (`sys.configurations`) with the recommended setting.
### **How It Works**
???? **Evaluates NUMA Nodes & CPU Cores**
???? **Generates a Recommendation Based on CPU & NUMA Setup**
???? **Provides Justification for MAXDOP Adjustment**
???? **Displays Current MAXDOP Setting for Reference**
This script is **particularly useful** for **SQL Server performance tuning** and **post-upgrade configuration validation**. ????
Author: user
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
IF OBJECT_ID('tempdb..&#91;#SubtreeCost]') IS NOT NULL
DROP TABLE &#91;#SubtreeCost];
DECLARE @Value int;
SELECT @Value = CONVERT(int, &#91;value]) FROM &#91;sys].&#91;configurations] WHERE &#91;name] = 'cost threshold for parallelism';
;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
DB_NAME(dbid) AS DatabaseName,
ISNULL(OBJECT_SCHEMA_NAME(objectid,dbid) + '.' + OBJECT_NAME(objectid,dbid),'Adhoc') AS FQObjectName,
usecounts,
TRY_CAST(&#91;n].&#91;value]('(@StatementSubTreeCost)&#91;1]', 'nvarchar(30)') AS DECIMAL(18,2)) AS &#91;StatementSubtreeCost]
INTO &#91;#SubtreeCost]
FROM &#91;sys].&#91;dm_exec_cached_plans] AS &#91;cp]
CROSS APPLY &#91;sys].&#91;dm_exec_query_plan](&#91;cp].&#91;plan_handle]) AS &#91;qp]
CROSS APPLY &#91;query_plan].&#91;nodes]('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS &#91;qn](&#91;n])
WHERE &#91;n].&#91;query]('.').&#91;exist]('//RelOp&#91;@PhysicalOp="Parallelism"]') = 1;
SELECT *
FROM #SubtreeCost
ORDER BY StatementSubtreeCost DESC
;WITH avgz
AS
(
SELECT AVG(&#91;StatementSubtreeCost]) AS &#91;AverageSubtreeCost],COUNT(*) AS &#91;StatementCount],@Value AS &#91;CurrentCostThreshold]
FROM &#91;#SubtreeCost]
)
,medianz
AS
(
SELECT
((SELECT TOP 1 &#91;StatementSubtreeCost]
FROM
(
SELECT TOP 50 PERCENT &#91;StatementSubtreeCost]
FROM &#91;#SubtreeCost]
ORDER BY &#91;StatementSubtreeCost] ASC
) AS &#91;A]
ORDER BY &#91;StatementSubtreeCost] DESC
)
+
(SELECT TOP 1 &#91;StatementSubtreeCost]
FROM
(
SELECT TOP 50 PERCENT &#91;StatementSubtreeCost]
FROM &#91;#SubtreeCost]
ORDER BY &#91;StatementSubtreeCost] DESC
) AS &#91;A]
ORDER BY &#91;StatementSubtreeCost] ASC))
/2 AS &#91;MEDIAN],
@Value AS &#91;CurrentCostThreshold]
)
,modez
AS
(
SELECT TOP 1 &#91;StatementSubtreeCost] AS &#91;MODE],COUNT(*) AS &#91;StatementCount],@Value AS &#91;CurrentCostThreshold]
FROM &#91;#SubtreeCost]
GROUP BY &#91;StatementSubtreeCost]
ORDER BY COUNT(1) DESC
)
SELECT&#91;avgz].&#91;AverageSubtreeCost],&#91;avgz].&#91;StatementCount],&#91;medianz].&#91;MEDIAN],&#91;modez].&#91;MODE]
FROM &#91;avgz]
CROSS JOIN &#91;medianz]
CROSS JOIN &#91;modez]
Author: ChatGPT
### **Purpose of the Script**
This script is designed to **analyze the cost threshold for parallelism in SQL Server** and provide **insights into whether the current setting is optimal**. The **cost threshold for parallelism** determines **when a query should use parallel execution** based on its estimated subtree cost.
### **Key Benefits & Use Cases**
??? **Detects Queries Using Parallel Execution** ??? Identifies statements that have triggered **parallelism (Parallelism PhysicalOp)** in execution plans.
??? **Calculates Important Cost Metrics** ??? Computes **average, median, and mode** for statement subtree costs.
??? **Compares Against Current Cost Threshold** ??? Evaluates whether **SQL Server???s current cost threshold** is too high or too low.
??? **Supports Performance Tuning** ??? Helps in deciding if the **cost threshold for parallelism** should be adjusted for **better CPU efficiency**.
??? **Works Across All Databases** ??? Scans execution plans for **all cached queries** across the SQL Server instance.
### **How It Works**
???? **Extracts Query Execution Plans** ??? Retrieves cached execution plans using `sys.dm_exec_query_plan()`.
???? **Finds Queries with Parallelism** ??? Filters plans that contain **parallel execution (`Parallelism` PhysicalOp)**.
???? **Calculates Key Statistics**:
- **Average Subtree Cost** ??? Determines the **mean cost** of queries that used parallelism.
- **Median Subtree Cost** ??? Finds the **middle value** to eliminate extreme outliers.
- **Mode Subtree Cost** ??? Identifies the **most frequently occurring subtree cost**.
???? **Compares to Current Threshold** ??? Helps determine if the **cost threshold for parallelism** is **set too high or too low** based on real-world workload data.
### **Why This Matters?**
- If the **median and mode subtree costs** are **significantly lower** than the **current threshold**, it **may be too high**, preventing parallelism from being used effectively.
- If the **average is much higher**, it suggests that **parallelism is already well-optimized** or could be **fine-tuned** further.
- Adjusting this setting correctly **reduces CPU contention** and **improves query performance**, especially for mixed OLTP/OLAP workloads.
This script is an **essential tool for SQL Server performance tuning**, particularly for **balancing parallel query execution with CPU efficiency**. ????
Author: ChatGPT
The query:
```sql
SELECT * FROM sys.configurations WHERE name = 'fill factor (%)';
```
### **Purpose**
This retrieves the current **Fill Factor (%)** configuration setting from **SQL Server???s system-wide configurations**.
### **What is Fill Factor (%)?**
- **Fill Factor** determines **how much space SQL Server leaves free** when creating or rebuilding **index pages**.
- It is **expressed as a percentage** (0-100), where:
- **100% (or 0%)** = Index pages are **completely filled** (default).
- **Less than 100%** = Leaves some **free space** for future inserts, reducing page splits.
### **Why This Matters?**
- **OLTP Systems (Heavy Inserts/Updates)**: A lower fill factor (e.g., **70-90%**) can **reduce fragmentation** and **improve performance**.
- **Read-Heavy Systems (DWH, Reporting)**: A higher fill factor (e.g., **95-100%**) may improve **query performance** by **minimizing page reads**.
### **Interpreting the Results**
The result will include:
- **Configuration Name** (`fill factor (%)`)
- **Configured Value**: The **current setting**.
- **Running Value** (`value_in_use`): What is actually in effect.
- **Is Dynamic?**: Indicates if the change takes effect immediately.
- **Requires Restart?**: Some changes require a restart.
### **Example Output**
| configuration_id | name | minimum | maximum | value | value_in_use | description |
|-----------------|----------------|---------|---------|-------|-------------|-------------|
| 1535 | fill factor (%) | 0 | 100 | 90 | 90 | Default index fill factor |
If `value_in_use` ??? `value`, a restart might be required for the change to take effect.
### **How to Change Fill Factor**
To update it globally:
```sql
EXEC sp_configure 'fill factor (%)', 80;
RECONFIGURE;
```
???? **Best Practice:** Only change this **after testing** on your workload, as it affects all indexes **created or rebuilt** going forward.
Author: user
DECLARE @Layer VARCHAR(30);
SET @Layer = '&#91;CMSServerLayer]';
IF OBJECT_ID('tempdb..&#91;#Results]') IS NOT NULL
DROP TABLE &#91;#Results]
IF OBJECT_ID('tempdb..&#91;#HighAvailability]') IS NOT NULL
DROP TABLE &#91;#HighAvailability]
CREATE TABLE &#91;dbo].&#91;#HighAvailability] (
&#91;ServerName] VARCHAR(128) NULL,
&#91;MachineName] VARCHAR(128) NULL,
&#91;DatabaseName] VARCHAR(128) NULL DEFAULT (''),
&#91;ClusterStatus] VARCHAR(128) NULL DEFAULT (''),
&#91;MirroredStatus] VARCHAR(128) NULL DEFAULT (''),
&#91;AAGGroupName] VARCHAR(128) NULL DEFAULT (''),
&#91;AAGStatus] VARCHAR(128) NULL DEFAULT (''),
&#91;ReplicationStatus] VARCHAR(128) NULL DEFAULT (''),
&#91;LogShippingStatus] VARCHAR(128) NULL DEFAULT (''),
&#91;IsSharePointDB] VARCHAR(128) NULL DEFAULT (''))
--#################################################################################################
;WITH MyServerSettings
AS
(
SELECT ISNULL(CONVERT(INT,SERVERPROPERTY('IsClustered')),0) AS IsClustered
),
MyClusterNodes
AS
(
SELECT
CONVERT(VARCHAR(128),@@SERVERNAME) AS ServerName,
CONVERT(VARCHAR(128),SERVERPROPERTY('ComputerNamePhysicalNetBIOS')) AS &#91;MachineName]
UNION
SELECT CONVERT(VARCHAR(128),@@SERVERNAME) AS ServerName,CONVERT(VARCHAR(128),NodeName) AS Comment FROM sys.dm_os_cluster_nodes
)
INSERT INTO #HighAvailability(&#91;ServerName],&#91;MachineName],&#91;ClusterStatus])
SELECT
MyClusterNodes.ServerName,
MyClusterNodes.MachineName,
CASE
WHEN MyServerSettings.IsClustered = 0
THEN 'Not Clustered'
ELSE 'Clustered'
END AS &#91;ClusterStatus]
FROM MyServerSettings
CROSS JOIN MyClusterNodes;
INSERT INTO #HighAvailability(&#91;ServerName],&#91;MachineName],&#91;DatabaseName],&#91;MirroredStatus],&#91;ReplicationStatus])
SELECT
CONVERT(VARCHAR(128),@@SERVERNAME) AS ServerName,
CONVERT(VARCHAR(128),SERVERPROPERTY('ComputerNamePhysicalNetBIOS')) AS &#91;MachineName],
A.name AS DatabaseName,
CASE
WHEN B.mirroring_state IS NULL THEN '' --'Mirroring not configured'
ELSE 'Mirroring configured'
END AS &#91;MirroredStatus],
CASE WHEN A.is_published = 1 THEN 'published ' ELSE '' END
+ CASE WHEN A.is_subscribed = 1 THEN 'subscribed ' ELSE '' END
+ CASE WHEN A.is_merge_published = 1 THEN 'merge published ' ELSE '' END
+ CASE WHEN A.is_distributor = 1 THEN 'distributor ' ELSE '' END
FROM
sys.databases A
LEFT JOIN sys.database_mirroring B
ON A.database_id=B.database_id
WHERE A.database_id > 4
BEGIN TRY
EXECUTE(
'
UPDATE MyTarget
SET &#91;AAGGroupName] = AvailabilityGroupName,
&#91;AAGStatus] = ''islocal=''
+ CONVERT(VARCHAR,is_local)
+ '',is_primary_replica=''
+ CONVERT(VARCHAR,is_primary_replica)
FROM #HighAvailability MyTarget
INNER JOIN (
SELECT dbz.name AS DatabaseName,
dbstates.database_id,
dbstates.is_local,
dbstates.is_primary_replica,
AG.name AS AvailabilityGroupName
FROM sys.databases dbz
LEFT JOIN master.sys.dm_hadr_database_replica_states AS dbstates
ON dbz.database_id = dbstates.database_id
LEFT JOIN master.sys.availability_groups AS AG ON AG.group_id = dbstates.group_id
) hadr
ON MyTarget.DatabaseName = hadr.DatabaseName
WHERE hadr.AvailabilityGroupName IS NOT NULL
')
END TRY
BEGIN CATCH
DECLARE @ErrorSeverity INT,
@ErrorNumber INT,
@ErrorMessage NVARCHAR(4000),
@ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
PRINT 'Msg ' + CONVERT(VARCHAR,@ErrorNumber) + ' Level ' + CONVERT(VARCHAR,@ErrorSeverity) + ' State ' + CONVERT(VARCHAR,@ErrorState) + CHAR(13) + CHAR(10) + @ErrorMessage
END CATCH
UPDATE MyTarget
SET &#91;LogShippingStatus] = 'Log Shipping'
FROM #HighAvailability MyTarget
INNER JOIN msdb..log_shipping_primary_databases lg
ON MyTarget.DatabaseName = lg.primary_database
--new section check if isSharepointDB and AAG, which we ignore
EXECUTE sp_MSforeachdb '
UPDATE #HighAvailability
SET &#91;IsSharePointDB] = ''SharePointDatabase''
WHERE DatabaseName = ''?''
AND EXISTS(SELECT 1 FROM &#91;?].sys.tables tabz WHERE tabz.name IN(''Versions'',''Version'',''VersionInfo'',''DatabaseVersion'') )
'
IF OBJECT_ID('tempdb.&#91;dbo].&#91;#databases]') IS NOT NULL
DROP TABLE &#91;dbo].&#91;#databases]
CREATE TABLE &#91;dbo].&#91;#databases] (
&#91;dbname] sysname NOT NULL)
--#################################################################################################
;WITH MyBaseCTE
AS
(
SELECT
CONVERT(VARCHAR(128),@@SERVERNAME) AS ServerName,
CONVERT(VARCHAR(128),DB_NAME(altz.dbid)) AS DatabaseName,
altz.dbid AS DatabaseID,
altz.name AS LogicalFileName,
CASE
WHEN &#91;groupid] = 0
THEN 'Log'
ELSE 'Rows'
END AS FileType,
&#91;altz].&#91;size] AS CurrentSizeKB,
&#91;altz].&#91;size] / (64 * 1000) AS calc,
CASE
WHEN &#91;altz].&#91;size] / (64 * 1000) <= 2
THEN 1
ELSE &#91;altz].&#91;size] / (64 * 1000)
END AS LogGrowthMultiplier,
&#91;altz].&#91;growth] AS CurrentGrowth,
CASE
WHEN (status & (32 * 32768)) <> 0
THEN + 'Percent'
ELSE 'KB'
END AS GrowthType,
CONVERT(VARCHAR(30),&#91;altz].&#91;growth]) + CASE
WHEN (status & (32 * 32768)) <> 0
THEN + '%'
ELSE 'KB'
END AS CurrentGrowthDescrip,
CASE
WHEN &#91;altz].&#91;size] / (64 * 2048) < 1
THEN 1
ELSE &#91;altz].&#91;size] / (64 * 2048)
END * 64 AS NewLogGrowth,
CASE
WHEN &#91;altz].&#91;size] <= 128000 THEN 64 --MB
WHEN &#91;altz].&#91;size] <= 1280000 THEN 128 --MB
WHEN &#91;altz].&#91;size] <= 12800000 THEN 256 -- MB
WHEN &#91;altz].&#91;size] <= 51200000 THEN 512 -- MB
WHEN &#91;altz].&#91;size] > 51200000 THEN 1024 -- MB
ELSE 256 -- MB
END AS NewDataGrowth,
'' AS Filler
FROM sys.sysaltfiles altz
WHERE altz.dbid < 32767
),
--if at default 128KB, New Growth is 64MB, ignore calculation
MyCTE
AS
(
SELECT &#91;MyBaseCTE].&#91;ServerName],
&#91;MyBaseCTE].&#91;DatabaseName],
&#91;MyBaseCTE].&#91;DatabaseID],
&#91;MyBaseCTE].&#91;LogicalFileName],
&#91;MyBaseCTE].&#91;FileType],
&#91;MyBaseCTE].&#91;CurrentSizeKB],
&#91;MyBaseCTE].&#91;calc],
&#91;MyBaseCTE].&#91;LogGrowthMultiplier],
&#91;MyBaseCTE].&#91;CurrentGrowth],
&#91;MyBaseCTE].&#91;GrowthType],
&#91;MyBaseCTE].&#91;CurrentGrowthDescrip],
CASE WHEN &#91;MyBaseCTE].&#91;CurrentGrowthDescrip] IN('128KB','10%') THEN 64 ELSE &#91;MyBaseCTE].&#91;NewLogGrowth] END AS &#91;NewLogGrowth],
CASE WHEN &#91;MyBaseCTE].&#91;CurrentGrowthDescrip] IN('128KB','10%') THEN 64 ELSE &#91;MyBaseCTE].&#91;NewDataGrowth] END AS &#91;NewDataGrowth] ,
&#91;MyBaseCTE].&#91;Filler] FROM MyBaseCTE
)
SELECT @Layer AS ServerLayer,
&#91;MyCTE].&#91;ServerName],
&#91;MyCTE].&#91;DatabaseName],
--&#91;MyCTE].&#91;DatabaseID],
&#91;MyCTE].&#91;LogicalFileName],
&#91;MyCTE].&#91;FileType],
&#91;MyCTE].&#91;CurrentSizeKB],
--&#91;MyCTE].&#91;calc],
--&#91;MyCTE].&#91;LogGrowthMultiplier],
--&#91;MyCTE].&#91;CurrentGrowth],
&#91;MyCTE].&#91;GrowthType],
&#91;MyCTE].&#91;CurrentGrowthDescrip],
CASE
WHEN FileType = 'Log'
THEN &#91;MyCTE].&#91;NewLogGrowth]
ELSE &#91;MyCTE].&#91;NewDataGrowth]
END AS NewGrowth,
--&#91;MyCTE].&#91;Filler]
CASE
--Log
WHEN FileType = 'Log'
THEN 'USE ' + QUOTENAME(DatabaseName) +';' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
+ 'ALTER DATABASE '+ QUOTENAME(DatabaseName)
+ ' MODIFY FILE ( NAME = N''' + LogicalFileName + ''', FILEGROWTH = ' + CONVERT(VARCHAR(30),NewLogGrowth) + 'MB )'+ CHAR(13) + CHAR(10)
--data
ELSE 'USE ' + QUOTENAME(DatabaseName) +';' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
+ 'ALTER DATABASE '+ QUOTENAME(DatabaseName)
+ ' MODIFY FILE ( NAME = N'''
+LogicalFileName + ''', FILEGROWTH = ' + CONVERT(VARCHAR(30),NewDataGrowth) + 'MB )'+ CHAR(13) + CHAR(10)
END AS GrowthCommand
INTO #Results
FROM MyCTE
WHERE (&#91;MyCTE].&#91;GrowthType] = 'Percent')
OR (&#91;MyCTE].&#91;GrowthType] = 'KB' AND &#91;MyCTE].&#91;CurrentGrowth] < 64000)
AND &#91;MyCTE].&#91;CurrentGrowth] < ( CASE
WHEN FileType = 'Log'
THEN &#91;MyCTE].&#91;NewLogGrowth]
ELSE &#91;MyCTE].&#91;NewDataGrowth]
END * 100)
ORDER BY &#91;MyCTE].&#91;DatabaseID],&#91;MyCTE].&#91;FileType] DESC
--comment out, cannot be modified
UPDATE MyTarget
SET &#91;MyTarget].&#91;GrowthCommand] = '-- ' + QUOTENAME(MyTarget.&#91;DatabaseName] COLLATE SQL_Latin1_General_CP1_CI_AS) + ' Read Only Secondary Replica'
FROM &#91;#Results] MyTarget
INNER JOIN &#91;#HighAvailability] MySource
ON MyTarget.&#91;DatabaseName] COLLATE SQL_Latin1_General_CP1_CI_AS = &#91;MySource].&#91;DatabaseName] COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE &#91;MySource].&#91;AAGStatus] COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%is_primary_replica=0';
--comment out, cannot be modified
UPDATE MyTarget
SET &#91;MyTarget].&#91;GrowthCommand] = '-- ' + QUOTENAME(MyTarget.&#91;DatabaseName]) + 'Read Only Database'
FROM &#91;#Results] MyTarget
INNER JOIN sys.databases MySource
ON MyTarget.&#91;DatabaseName] COLLATE SQL_Latin1_General_CP1_CI_AS = &#91;MySource].&#91;name] COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE &#91;MySource].&#91;is_read_only] = 1 ;
UPDATE MyTarget
SET &#91;MyTarget].&#91;GrowthCommand] = '-- ' + QUOTENAME(MyTarget.&#91;DatabaseName]) + ' ' + &#91;MySource].&#91;state_desc] + ' Database'
--SELECT MySource.*
FROM &#91;#Results] MyTarget
INNER JOIN sys.databases MySource
ON MyTarget.&#91;DatabaseName] COLLATE SQL_Latin1_General_CP1_CI_AS = &#91;MySource].&#91;name] COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE &#91;MySource].&#91;state_desc] <> 'ONLINE' ;
SELECT &#91;ServerLayer],
&#91;ServerName],
&#91;DatabaseName],
&#91;LogicalFileName],
&#91;FileType],
&#91;CurrentSizeKB],
&#91;GrowthType],
&#91;CurrentGrowthDescrip],
&#91;NewGrowth],
&#91;GrowthCommand] FROM #Results
Author: ChatGPT
### **Purpose of the Script**
This script is designed to **analyze and optimize database file growth settings** while considering **high availability configurations** such as **clustering, database mirroring, availability groups, replication, log shipping, and SharePoint databases**.
### **Key Benefits & Use Cases**
??? **Identifies Inappropriate File Growth Settings**
- Detects databases using **percentage-based growth** (which can cause excessive growth on large files).
- Identifies files growing in **small increments (e.g., 128KB, 10%)**, which can lead to fragmentation and performance issues.
- Suggests **optimized growth settings** based on database size.
??? **Considers High Availability & Read-Only Restrictions**
- **Availability Groups (AAG)**: Prevents modifications to **secondary replicas**.
- **Read-Only Databases**: Excludes databases that **cannot be modified**.
- **Non-Online Databases**: Skips databases that are in states other than **ONLINE**.
??? **Generates Safe SQL Commands to Modify Growth Settings**
- For **log files**, suggests growth in **MB increments** instead of percentage-based growth.
- For **data files**, recommends **optimized growth values** based on database size.
- **Commented out commands** for databases that **cannot be modified** due to high availability restrictions.
### **How It Works**
???? **Step 1: Collects High Availability Status**
- Determines if the server is **clustered**.
- Retrieves details on **mirroring, availability groups, replication, and log shipping**.
- Identifies **SharePoint databases** (which follow specific growth settings).
???? **Step 2: Analyzes Database Files & Growth Settings**
- Retrieves **file size, current growth setting, and file type (log/data)**.
- Calculates recommended growth settings based on **best practices**:
- **Small Databases** ??? 64MB
- **Medium Databases** ??? 128MB???256MB
- **Large Databases** ??? 512MB???1GB
- Determines **if a file uses percentage-based growth or an inefficient KB setting**.
???? **Step 3: Generates ALTER DATABASE Commands**
- Provides `ALTER DATABASE MODIFY FILE` statements to **correct growth settings**.
- Comments out modifications for **read-only and secondary replica databases**.
- Highlights databases that are in a **non-ONLINE state**.
### **Why This Matters?**
- **Improves Performance**: Prevents **excessive autogrowth events**, reducing file fragmentation.
- **Enhances Stability**: Ensures that **critical HA databases** are not accidentally modified.
- **Supports Best Practices**: Aligns database growth settings with **Microsoft recommendations**.
This script is a **must-have for DBAs managing SQL Server instances** with **high availability configurations**, ensuring **optimized database file growth** without disrupting critical services. ????
Author: user
select * from sys.configurations where name like '%boost%'
Author: ChatGPT
The query:
```sql
SELECT * FROM sys.configurations WHERE name LIKE '%boost%';
```
retrieves configuration settings related to "boost" from SQL Server's system configuration settings.
### **What This Checks**
This will typically return the setting for:
- **"boost SQL Server priority"** (`show_advanced_options` must be enabled to view this setting).
### **Understanding "Boost SQL Server Priority"**
- If **enabled (1)**, SQL Server runs with a higher CPU scheduling priority.
- If **disabled (0)**, SQL Server runs at the default priority.
### **Should You Enable It?**
???? **Not Recommended** in most cases because:
1. It **can starve** other critical processes, including system services.
2. It **may not work as expected** on modern multi-threaded/multi-core CPUs.
3. Microsoft **does not recommend enabling it** unless under specific guidance.
#### **How to Disable It (if needed)**
```sql
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'boost SQL Server priority', 0;
RECONFIGURE;
```
This will set it to **0 (disabled)**, which is the **recommended setting** in most environments.
Let me know if you need further details! ????
Author: user
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT 1 AS &#91;Sortorder],
'Procedures That Reference A Module' AS &#91;Comment],
&#91;sed].&#91;referencing_id],
QUOTENAME(SCHEMA_NAME(&#91;Ref].&#91;schema_id]))
+ '.' + QUOTENAME(&#91;Ref].&#91;name]) AS &#91;RefQualifiedObjectName],
SCHEMA_NAME(&#91;Ref].&#91;schema_id]) AS &#91;RefSchemaName],
&#91;Ref].&#91;name] AS &#91;RefObjectName],
&#91;Ref].&#91;type_desc] AS &#91;RefObjectType],
QUOTENAME(SCHEMA_NAME(&#91;o].&#91;schema_id]))
+ '.' + QUOTENAME(&#91;o].&#91;name]) AS &#91;QualifiedObjectName],
SCHEMA_NAME(&#91;o].&#91;schema_id]) AS &#91;SchemaName],
&#91;o].&#91;name] AS &#91;ObjectName],
&#91;o].&#91;type_desc],
'references' AS &#91;cmment],
&#91;asmbz].&#91;name] AS &#91;AssembyName],
&#91;asmbz].&#91;assembly_id],
&#91;asmbz].&#91;permission_set_desc],
'DROP ' + CASE WHEN &#91;Ref].&#91;type_desc] LIKE'%FUNC%' THEN ' FUNCTION ' ELSE ' PROCEDURE ' END + QUOTENAME(SCHEMA_NAME(&#91;Ref].&#91;schema_id]))
+ '.' + QUOTENAME(&#91;Ref].&#91;name]) AS &#91;DropStatement]
FROM &#91;sys].&#91;assemblies] AS &#91;asmbz]
INNER JOIN &#91;sys].&#91;assembly_modules] AS &#91;am]
ON &#91;am].&#91;assembly_id] = &#91;asmbz].&#91;assembly_id]
INNER JOIN &#91;sys].&#91;objects] AS &#91;o]
ON &#91;o].&#91;object_id] = &#91;am].&#91;object_id]
INNER JOIN &#91;sys].&#91;sql_expression_dependencies] AS &#91;sed]
ON &#91;sed].&#91;referenced_id] = &#91;o].&#91;object_id]
INNER JOIN &#91;sys].&#91;objects] AS &#91;Ref]
ON &#91;Ref].&#91;object_id] = &#91;sed].&#91;referencing_id]
WHERE &#91;asmbz].&#91;assembly_id] > 1
UNION ALL
SELECT 2 AS &#91;SortOrder],
'CLR Modules That Reference An Assembly' AS &#91;Comment],
0 AS &#91;referencing_id],
'' AS &#91;RefQualifiedObjectName],
'' AS &#91;RefSchemaName],
'' AS &#91;RefObjectName],
'' AS &#91;RefObjectType],
QUOTENAME(SCHEMA_NAME(&#91;o].&#91;schema_id]))
+ '.' + QUOTENAME(&#91;o].&#91;name]) AS &#91;QualifiedObjectName],
SCHEMA_NAME(&#91;o].&#91;schema_id]) AS &#91;SchemaName],
&#91;o].&#91;name] AS &#91;ObjectName],
&#91;o].&#91;type_desc],
'references' AS &#91;cmment],
&#91;asmbz].&#91;name] AS &#91;AssembyName],
&#91;asmbz].&#91;assembly_id],
&#91;asmbz].&#91;permission_set_desc],
'DROP ' + CASE WHEN &#91;o].&#91;type_desc] LIKE'%FUNC%' THEN ' FUNCTION ' ELSE ' PROCEDURE ' END + QUOTENAME(SCHEMA_NAME(&#91;o].&#91;schema_id]))
+ '.' + QUOTENAME(&#91;o].&#91;name]) AS &#91;DropStatement]
FROM &#91;sys].&#91;assemblies] AS &#91;asmbz]
INNER JOIN &#91;sys].&#91;assembly_modules] AS &#91;am]
ON &#91;am].&#91;assembly_id] = &#91;asmbz].&#91;assembly_id]
INNER JOIN &#91;sys].&#91;objects] AS &#91;o]
ON &#91;o].&#91;object_id] = &#91;am].&#91;object_id]
WHERE &#91;asmbz].&#91;assembly_id] > 1
UNION ALL
SELECT 3 AS &#91;Sortorder],
'CLR Assembly itself' AS &#91;Comment],
'' AS &#91;referencing_id],
'' AS &#91;RefQualifiedObjectName],
'' AS &#91;RefSchemaName],
'' AS &#91;RefObjectName],
'' AS &#91;RefObjectType],
'' AS &#91;QualifiedObjectName],
'' AS &#91;SchemaName],
'' AS &#91;ObjectName],
'' AS &#91;type_desc],
'' AS &#91;cmment],
&#91;asmbz].&#91;name] AS &#91;AssembyName],
&#91;asmbz].&#91;assembly_id],
&#91;asmbz].&#91;permission_set_desc],
'DROP ASSEMBLY ' + &#91;asmbz].&#91;name] AS &#91;DropStatement]
FROM &#91;sys].&#91;assemblies] AS &#91;asmbz]
WHERE &#91;asmbz].&#91;assembly_id] > 1;
Author: ChatGPT
This script is designed to **identify and manage SQL CLR (Common Language Runtime) assemblies** and their dependencies in a SQL Server database.
### **???? Purpose:**
The query provides insight into:
1. **Stored Procedures & Functions that reference CLR modules**
2. **CLR modules that reference assemblies**
3. **Standalone CLR assemblies**
### **???? Benefits:**
- **Dependency Analysis:** Helps in understanding **which stored procedures and functions rely on CLR modules**.
- **Cleanup & Maintenance:** Generates **DROP statements** for **procedures, functions, and assemblies**, making it easier to **safely remove** or **migrate** CLR-related objects.
- **Security Review:** Identifies **assemblies with elevated permissions** (`UNSAFE`, `EXTERNAL_ACCESS`, `SAFE`).
- **Performance Optimization:** Helps assess the impact of CLR objects and their dependencies.
### **???? How It Works:**
1. **Finds Procedures & Functions that Reference CLR Modules**
- Uses `sys.sql_expression_dependencies` to **trace references**.
2. **Finds CLR Modules that Reference Assemblies**
- Joins `sys.assembly_modules` and `sys.objects` to **list CLR-based objects**.
3. **Lists Standalone CLR Assemblies**
- Directly queries `sys.assemblies` to list all assemblies **without any referencing modules**.
### **???? Sample Output**
| SortOrder | Comment | Schema.ObjectName | ObjectType | AssembyName | DropStatement |
|-----------|----------------------------------------|------------------|------------|-------------|--------------|
| 1 | Procedures That Reference A Module | dbo.MyProc | PROCEDURE | MyAssembly | DROP PROCEDURE dbo.MyProc |
| 2 | CLR Modules That Reference Assembly | dbo.MyCLRFunc | FUNCTION | MyAssembly | DROP FUNCTION dbo.MyCLRFunc |
| 3 | CLR Assembly itself | | | MyAssembly | DROP ASSEMBLY MyAssembly |
### **???? How to Use**
- **Run the query** to check CLR usage.
- **Review dependencies** before dropping.
- **Use the generated DROP statements carefully** to avoid breaking dependencies.
Would you like additional filtering options (e.g., exclude SAFE assemblies)? ????
Author: user
DECLARE @current_tracefilename VARCHAR(500);
DECLARE @0_tracefilename VARCHAR(500);
DECLARE @indx INT;
SELECT @current_tracefilename = path
FROM sys.traces
WHERE is_default = 1;
SET @current_tracefilename = REVERSE(@current_tracefilename);
SELECT @indx = PATINDEX('%\%', @current_tracefilename);
SET @current_tracefilename = REVERSE(@current_tracefilename);
SET @0_tracefilename = LEFT(@current_tracefilename, LEN(@current_tracefilename) - @indx) + '\log.trc';
SELECT DatabaseName,
te.name,
Filename,
CONVERT(DECIMAL(10, 3), Duration / 1000000e0) AS TimeTakenSeconds,
DATENAME(dw,StartTime) + ' ' + CONVERT(VARCHAR(40),StartTime,120) AS StartTime,
DATENAME(dw,EndTime) + ' ' + CONVERT(VARCHAR(40),EndTime,120) AS EndTime,
(IntegerData * 8.0 / 1024) AS 'ChangeInSize MB',
ApplicationName,
HostName,
LoginName
FROM ::fn_trace_gettable(@0_tracefilename, DEFAULT) t
INNER JOIN sys.trace_events AS te ON t.EventClass = te.trace_event_id
WHERE(trace_event_id >= 92
AND trace_event_id <= 95)
ORDER BY t.StartTime;
Author: ChatGPT
### **???? Purpose of the Script:**
This script **retrieves and analyzes SQL Server trace logs** to track **database auto-growth and auto-shrink events**. It extracts **detailed metadata about file size changes, execution duration, and user details**.
---
### **???? Benefits:**
- **Identifies Auto-Growth & Auto-Shrink Events**
Helps detect **frequent database growth/shrink events**, which can **impact performance**.
- **Performance Tuning Insight**
- Identifies **long-running** auto-growth operations.
- Helps tune **database file size settings** to avoid fragmentation.
- **Auditing & Monitoring**
- Tracks **which databases** are frequently growing/shrinking.
- Logs **which application, host, and login** triggered the change.
---
### **???? How It Works:**
1. **Finds the Current SQL Server Trace File**
- Extracts the **default trace file path** from `sys.traces`.
- Constructs the path to `log.trc` (trace log file).
2. **Extracts Relevant Events from the Trace File**
- Filters for **event classes 92-95**, which correspond to:
- **92:** Data file auto-grow
- **93:** Log file auto-grow
- **94:** Data file auto-shrink
- **95:** Log file auto-shrink
3. **Formats & Displays Important Details**
- **Database name**
- **Event type (auto-grow/shrink, data/log file)**
- **File affected**
- **Duration of operation** (in seconds)
- **Change in file size** (MB)
- **Application, Host, and Login responsible for the change**
---
### **???? Sample Output**
| DatabaseName | EventName | Filename | TimeTakenSeconds | StartTime | EndTime | ChangeInSize MB | ApplicationName | HostName | LoginName |
|-------------|----------------|-----------------|------------------|---------------------|---------------------|----------------|----------------|----------|-----------|
| MyDB | Data File Auto-grow | MyDB.mdf | 2.345 | Mon 2024-02-25 12:00:00 | Mon 2024-02-25 12:00:02 | 256.0 MB | .NET App | Server01 | user1 |
| MyDB_Log | Log File Auto-grow | MyDB.ldf | 1.543 | Tue 2024-02-26 14:30:00 | Tue 2024-02-26 14:30:01 | 128.0 MB | SSMS | Server02 | admin |
---
### **???? How to Use:**
- **Run the script** to **identify auto-growth/shrink events**.
- **Investigate frequent growth/shrink occurrences**:
- Consider adjusting **initial size and auto-growth settings**.
- Prevent excessive fragmentation by **using fixed-size growth instead of percentage-based**.
- **Monitor and audit changes**:
- Identify **which applications or users** are triggering auto-growth.
- Optimize disk space allocation.
Would you like to refine it further to track only **specific databases** or **only auto-growth events**? ????
Author: user
;WITH Serverz
AS
(
SELECT
' EXEC dbo.sp_dropserver ''' + &#91;srvz].&#91;name] + ''', ''DropLogins'';' + CHAR(13) + CHAR(10) + '
EXEC master.dbo.sp_addlinkedserver ' + CHAR(13) + CHAR(10)
+ ' @server = N''' + &#91;srvz].&#91;name] + ''',' + CHAR(13) + CHAR(10)
+ ' @srvproduct = N''' + &#91;srvz].&#91;product] + ''',' + CHAR(13) + CHAR(10)
+ ' @datasrc = N''' + ISNULL(&#91;srvz].&#91;data_source],'') + ''',' + CHAR(13) + CHAR(10)
+ CASE WHEN &#91;srvz].&#91;catalog] IS NOT NULL THEN ' @catalog=N''' + &#91;srvz].&#91;catalog] + ''',' + CHAR(13) + CHAR(10) ELSE '' END
+ ' @provider = N''' + &#91;srvz].&#91;provider] + ''';'+ CHAR(13) + CHAR(10)
+ '
EXEC master.dbo.sp_serveroption @server=N''' + &#91;srvz].&#91;name] + ''', @optname=N''collation compatible'', @optvalue=N'''
+ CASE WHEN &#91;srvz].&#91;is_collation_compatible] = 0 THEN 'false' ELSE 'true' END + '''
EXEC master.dbo.sp_serveroption @server=N''' + &#91;srvz].&#91;name] + ''', @optname=N''data access'', @optvalue=N'''
+ CASE WHEN &#91;srvz].&#91;is_data_access_enabled] = 0 THEN 'false' ELSE 'true' END + '''
EXEC master.dbo.sp_serveroption @server=N''' + &#91;srvz].&#91;name] + ''', @optname=N''dist'', @optvalue=N'''
+ CASE WHEN &#91;srvz].&#91;is_distributor] = 0 THEN 'false' ELSE 'true' END + '''
EXEC master.dbo.sp_serveroption @server=N''' + &#91;srvz].&#91;name] + ''', @optname=N''pub'', @optvalue=N'''
+ CASE WHEN &#91;srvz].&#91;is_publisher] = 0 THEN 'false' ELSE 'true' END + '''
EXEC master.dbo.sp_serveroption @server=N''' + &#91;srvz].&#91;name] + ''', @optname=N''rpc'', @optvalue=N'''
+ CASE WHEN &#91;srvz].&#91;is_remote_login_enabled] = 0 THEN 'false' ELSE 'true' END + '''
EXEC master.dbo.sp_serveroption @server=N''' + &#91;srvz].&#91;name] + ''', @optname=N''rpc out'', @optvalue=N'''
+ CASE WHEN &#91;srvz].&#91;is_rpc_out_enabled] = 0 THEN 'false' ELSE 'true' END + '''
EXEC master.dbo.sp_serveroption @server=N''' + &#91;srvz].&#91;name] + ''', @optname=N''sub'', @optvalue=N'''
+ CASE WHEN &#91;srvz].&#91;is_subscriber] = 0 THEN 'false' ELSE 'true' END + '''
EXEC master.dbo.sp_serveroption @server=N''' + &#91;srvz].&#91;name] + ''', @optname=N''connect timeout'', @optvalue=N'''
+ CONVERT(VARCHAR(30),&#91;srvz].&#91;connect_timeout]) + '''
EXEC master.dbo.sp_serveroption @server=N''' + &#91;srvz].&#91;name] + ''', @optname=N''collation name'', @optvalue='
+ CASE WHEN &#91;srvz].&#91;collation_name] IS NULL THEN 'NULL ' ELSE '''' + &#91;srvz].&#91;collation_name] + '''' END + '
EXEC master.dbo.sp_serveroption @server=N''' + &#91;srvz].&#91;name] + ''', @optname=N''lazy schema validation'', @optvalue=N'''
+ CASE WHEN &#91;srvz].&#91;lazy_schema_validation] = 0 THEN 'false' ELSE 'true' END + '''
EXEC master.dbo.sp_serveroption @server=N''' + &#91;srvz].&#91;name] + ''', @optname=N''query timeout'', @optvalue=N'''
+ CONVERT(VARCHAR,&#91;srvz].&#91;query_timeout]) + '''
EXEC master.dbo.sp_serveroption @server=N''' + &#91;srvz].&#91;name] + ''', @optname=N''use remote collation'', @optvalue=N'''
+ CASE WHEN &#91;srvz].&#91;uses_remote_collation] = 0 THEN 'false' ELSE 'true' END + '''
EXEC master.dbo.sp_serveroption @server=N''' + &#91;srvz].&#91;name] + ''', @optname=N''remote proc transaction promotion'', @optvalue=N'''
+ CASE WHEN &#91;srvz].&#91;is_remote_proc_transaction_promotion_enabled] = 0 THEN 'false' ELSE 'true' END + '''
' AS &#91;Linky],
&#91;srvz].*
FROM &#91;sys].&#91;servers] &#91;srvz] WHERE &#91;srvz].&#91;server_id] > 0
)
,Loginz
AS
(
--logins
SELECT &#91;srvz].name,
'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N''' + &#91;srvz].&#91;name] + ''',
@useself = N''' + CASE WHEN &#91;logz].&#91;uses_self_credential] = 0 THEN 'False' ELSE 'True' END + ''',
@locallogin = ' + CASE WHEN &#91;logz].&#91;local_principal_id] = 0 THEN 'NULL' ELSE '''' + SUSER_NAME(&#91;logz].&#91;local_principal_id])+ '''' END + ',
@rmtuser = ' + CASE
WHEN &#91;logz].&#91;remote_name] IS NULL THEN 'NULL, @rmtpassword = NULL; '
ELSE 'N''' + &#91;logz].&#91;remote_name] + ''',@rmtpassword = N''########'';'
END AS &#91;cmd],
&#91;logz].* FROM &#91;sys].&#91;linked_logins] &#91;logz]
INNER JOIN &#91;sys].&#91;servers] &#91;srvz] ON &#91;srvz].&#91;server_id] = &#91;logz].&#91;server_id]
WHERE &#91;srvz].&#91;server_id] > 0
)
SELECT &#91;server_id],1 AS Sortorder,&#91;name],&#91;T1].&#91;data_source],Linky FROM &#91;Serverz] T1
UNION ALL
SELECT &#91;server_id],2 AS Sortorder,&#91;name],'' AS &#91;data_source],cmd FROM &#91;Loginz] T2
UNION ALL
SELECT &#91;server_id],3 AS Sortorder,&#91;name],'' AS &#91;data_source],'EXECUTE sp_tables_ex ' + QUOTENAME(name) AS Linky FROM &#91;Serverz] T1
ORDER BY name,Sortorder
Author: ChatGPT
### **???? Purpose of the Script:**
This script **generates dynamic T-SQL statements to recreate Linked Servers and their associated Logins** in SQL Server. It **extracts** existing linked server definitions from `sys.servers` and `sys.linked_logins`, then **constructs SQL commands** to recreate them.
---
### **???? Benefits:**
- **Easily Backup and Restore Linked Server Configurations**
- Useful for **migrating linked servers** to a new SQL Server instance.
- Ensures **consistency in remote connections** after a disaster recovery event.
- **Automates the Recreation of Linked Servers**
- Avoids **manual configuration errors** by generating a ready-to-execute script.
- **Preserves Security Configurations**
- **Retains authentication mappings** (excluding actual passwords for security).
- Ensures **remote logins are correctly linked**.
- **Includes Diagnostic Queries**
- Generates `EXECUTE sp_tables_ex` commands to **inspect available tables** on the linked servers.
---
### **???? How It Works:**
1. **Retrieves Linked Server Definitions (`sys.servers`)**
- Extracts **linked server names, providers, and options** such as:
- **Collation compatibility**
- **Data access, RPC (remote procedure call) options**
- **Timeout settings**
2. **Generates `sp_addlinkedserver` Statements**
- Constructs the full syntax to **recreate linked servers**.
- Uses **conditional logic** to include optional parameters like `@catalog`.
3. **Retrieves Linked Server Login Mappings (`sys.linked_logins`)**
- Extracts **local-to-remote login mappings**.
- Generates **`sp_addlinkedsrvlogin` commands** (passwords are masked for security).
4. **Includes Execution Statements for Testing**
- Generates **`EXECUTE sp_tables_ex`** to check table availability.
---
### **???? Example Output**
#### **1?????? Script to Drop and Recreate a Linked Server**
```sql
EXEC dbo.sp_dropserver 'RemoteSQL01', 'DropLogins';
EXEC master.dbo.sp_addlinkedserver
@server = N'RemoteSQL01',
@srvproduct = N'SQL Server',
@datasrc = N'192.168.1.100',
@provider = N'SQLNCLI11';
EXEC master.dbo.sp_serveroption @server=N'RemoteSQL01', @optname=N'collation compatible', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=N'RemoteSQL01', @optname=N'data access', @optvalue=N'true';
EXEC master.dbo.sp_serveroption @server=N'RemoteSQL01', @optname=N'rpc out', @optvalue=N'true';
EXEC master.dbo.sp_serveroption @server=N'RemoteSQL01', @optname=N'connect timeout', @optvalue=N'30';
EXEC master.dbo.sp_serveroption @server=N'RemoteSQL01', @optname=N'query timeout', @optvalue=N'600';
```
#### **2?????? Script to Recreate Linked Server Logins**
```sql
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'RemoteSQL01',
@useself = N'False',
@locallogin = 'AdminUser',
@rmtuser = N'RemoteAdmin',
@rmtpassword = N'########'; -- Masked for security
```
#### **3?????? Command to List Available Tables on Linked Server**
```sql
EXECUTE sp_tables_ex 'RemoteSQL01';
```
---
### **???? How to Use:**
1. **Run the script** on an **existing SQL Server instance** to generate the `sp_addlinkedserver` and `sp_addlinkedsrvlogin` statements.
2. **Copy and save the output** as a SQL script for later use.
3. **Run the generated script** on a **new SQL Server** to recreate the linked server setup.
This is particularly useful when **migrating servers, setting up high availability environments, or performing disaster recovery**.
Would you like to **customize** it further, such as filtering for **only specific linked servers**? ????
Author: user
DECLARE @name VARCHAR(128)
SELECT @name = name FROM sys.server_principals WHERE sid = 0x01
--check all databases
select
'Database Not owned by ' + @name + ';' As DB_Issues,
name,
suser_sname(dbz.owner_sid) As TheOwner ,
CASE
WHEN suser_sname(dbz.owner_sid) <> @name
THEN 'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(name) + ' to ' + QUOTENAME(@name) + ';'
ELSE ''
END As cmd
from sys.databases dbz
WHERE suser_sname(dbz.owner_sid) <> @name
AND dbz.name = DB_NAME()
UNION ALL
SELECT 'Change GUID on Service Broker',
name, '',
'ALTER DATABASE ' +QUOTENAME(name) + ' SET NEW_BROKER WITH ROLLBACK IMMEDIATE'
from sys.databases dbz
WHERE dbz.name = DB_NAME()
Author: ChatGPT
### **???? Purpose of the Script**
This script **identifies databases that are not owned by the default system administrator (`sa`)** and **generates commands** to:
1. **Fix incorrect database ownership** by changing it to `sa`.
2. **Reset the Service Broker GUID** to avoid potential message queuing issues.
---
### **???? Benefits**
- **Ensures Best Practices for Database Ownership**
- Prevents unexpected permissions or security risks due to non-standard owners.
- Helps maintain **consistent database administration** across all instances.
- **Resolves Service Broker Issues**
- Avoids **messaging conflicts** when restoring databases.
- Helps in troubleshooting **Service Broker activation failures**.
- **Automated Correction Commands**
- Generates `ALTER AUTHORIZATION` statements to **standardize ownership**.
- Provides `SET NEW_BROKER` commands for **resetting Service Broker**.
---
### **???? How It Works**
1. **Identifies the Default System Administrator**
- Retrieves the name of the account associated with `SID = 0x01` (usually `sa`).
2. **Checks for Non-Compliant Database Owners**
- Compares each database owner with the **expected default owner (`sa`)**.
- If the database is owned by another user, **a corrective `ALTER AUTHORIZATION` command is generated**.
3. **Detects and Generates a Fix for Service Broker GUID Issues**
- Generates `ALTER DATABASE ... SET NEW_BROKER` for databases to **reset Service Broker**.
---
### **???? Example Output**
#### **1?????? Detects Non-Compliant Database Owners**
| DB_Issues | name | TheOwner | cmd |
|-----------|------|---------|-----|
| Database Not owned by `sa` | `AdventureWorks` | `Lowell` | `ALTER AUTHORIZATION ON DATABASE::&#91;AdventureWorks] to &#91;sa];` |
#### **2?????? Detects Service Broker GUID Issues**
| DB_Issues | name | TheOwner | cmd |
|-----------|------|---------|-----|
| Change GUID on Service Broker | `AdventureWorks` | | `ALTER DATABASE &#91;AdventureWorks] SET NEW_BROKER WITH ROLLBACK IMMEDIATE` |
---
### **???? How to Use**
1. **Run the script** to identify databases with incorrect ownership.
2. **Review the generated `ALTER AUTHORIZATION` commands**.
3. **Execute the suggested commands** to standardize database ownership and reset the Service Broker, if needed.
This is **especially useful after database restores, migrations, or server rebuilds** to **ensure consistency**. ????
Would you like to extend this to check **jobs, linked servers, or database mail configurations** as well?
Author: user
SELECT * ,CASE WHEN RW > 1 THEN 'ALTER DATABASE ' + QUOTENAME(MyAlias.DatabaseName) + ' SET NEW_BROKER WITH ROLLBACK IMMEDIATE' ELSE '' END AS DuplicateServiceBrokerFix
FROM (
SELECT ROW_NUMBER()
OVER(PARTITION BY service_broker_guid ORDER BY name) AS RW,
name AS DatabaseName,
service_broker_guid
FROM sys.databases
WHERE &#91;database_id] > 4
) MyAlias
WHERE MyAlias.RW > 1
SELECT DB_NAME(&#91;database_id]) AS DatabaseName,
&#91;database_id] ,
is_broker_enabled ,
CASE WHEN &#91;is_broker_enabled] = 0 THEN 'ALTER DATABASE ' + QUOTENAME(name) + ' SET ENABLE_BROKER;' ELSE '' END AS EnableCommand,
'ALTER DATABASE ' + QUOTENAME(name) + ' SET DISABLE_BROKER;ALTER DATABASE ' + QUOTENAME(name) + ' SET ENABLE_BROKER;' AS ToggleCommand
FROM sys.databases WHERE name= DB_NAME()
select 'All rows in the queue are errors' AS Note,
MAX(CONVERT(VARCHAR(256),&#91;transmission_status])) + '...' AS TransmissionStatus,
'No rows should exist in the queue, all awaiting processing.' AS Expectation,
COUNT(*) AS NumberofItemsInTheTransmissionQueue,
'
DECLARE @conversation UNIQUEIDENTIFIER;
WHILE EXISTS (SELECT 1 FROM &#91;sys].&#91;transmission_queue] )
BEGIN
SET @conversation = (SELECT TOP 1 &#91;conversation_handle] FROM &#91;sys].&#91;transmission_queue] );
END CONVERSATION @conversation WITH CLEANUP;
END;
'
AS DrainCommand ,
MAX(CONVERT(VARCHAR(100),&#91;message_body])) + '...' AS MessageHeader
--select *
FROM sys.transmission_queue ;
SELECT COUNT(conversation_handle) AS HandleCount , is_initiator, s.name as 'local service',
far_service, sc.name 'contract', state_desc
FROM sys.conversation_endpoints ce
LEFT JOIN sys.services s
ON ce.service_id = s.service_id
LEFT JOIN sys.service_contracts sc
ON ce.service_contract_id = sc.service_contract_id
GROUP BY is_initiator, s.name ,
far_service, sc.name , state_desc
SELECT db_name(&#91;mon].&#91;database_id]) AS DatabaseName,&#91;database_id] ,
&#91;queues].&#91;name] ,
&#91;mon].&#91;queue_id] ,
&#91;mon].&#91;state] ,
&#91;mon].&#91;last_empty_rowset_time] ,
&#91;mon].&#91;last_activated_time] ,
&#91;mon].&#91;tasks_waiting]
FROM sys.dm_broker_queue_monitors &#91;mon]
INNER JOIN sys.&#91;service_queues] &#91;queues] ON &#91;queues].&#91;object_id] = &#91;mon].&#91;queue_id]
SELECT &#91;queues].&#91;name] ,
CASE WHEN &#91;mon].is_receive_enabled = 1 AND &#91;mon].&#91;is_enqueue_enabled] = 1 THEN ''
ELSE 'ALTER QUEUE ' + QUOTENAME(OBJECT_SCHEMA_NAME(&#91;queues].&#91;object_id])) + '.' + QUOTENAME(&#91;queues].&#91;name]) + ' WITH STATUS = ON'
END AS ClearQueueCommand,
&#91;parti].&#91;rows] AS CurrentRows,
&#91;mon].&#91;name] ,
&#91;mon].&#91;type] ,
&#91;mon].&#91;type_desc] ,
&#91;mon].&#91;create_date] ,
&#91;mon].&#91;modify_date] ,
&#91;mon].&#91;max_readers] ,
&#91;mon].&#91;activation_procedure] ,
&#91;mon].&#91;execute_as_principal_id] ,
USER_NAME(&#91;mon].&#91;execute_as_principal_id]) AS ExecutePrincipalName,
&#91;mon].&#91;is_activation_enabled] ,
&#91;mon].&#91;is_receive_enabled] ,
&#91;mon].&#91;is_enqueue_enabled] ,
&#91;mon].&#91;is_retention_enabled] ,
&#91;mon].&#91;is_poison_message_handling_enabled],
QUOTENAME(OBJECT_SCHEMA_NAME(&#91;queues].&#91;object_id])) + '.' + QUOTENAME(&#91;queues].&#91;name]) AS QualifiedName ,
SCHEMA_NAME(&#91;queues].&#91;schema_id]) AS SchemaName ,
&#91;queues].&#91;name] AS ServiceBrokerName ,
' SELECT CONVERT(VARCHAR(MAX),message_body) AS msg,''' + &#91;queues].&#91;name] + ''' AS ServiceBrokerQueueName, * FROM '
+ QUOTENAME(OBJECT_SCHEMA_NAME(&#91;queues].&#91;object_id])) + '.' + QUOTENAME(&#91;queues].&#91;name]) + ';' AS cmd ,
ClearQueueCommand = 'WHILE EXISTS( SELECT ''' + &#91;queues].&#91;name] + ''' AS ServiceBrokerQueueName, * FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME(&#91;queues].&#91;object_id])) + '.' + QUOTENAME(&#91;queues].&#91;name]) + ')
BEGIN
waitfor (
receive top(1)
conversation_group_id
from ' + QUOTENAME(OBJECT_SCHEMA_NAME(&#91;queues].&#91;object_id])) + '.' + QUOTENAME(&#91;queues].&#91;name]) + '
), timeout 1000;
END'
FROM &#91;sys].&#91;objects] AS objz
INNER JOIN &#91;sys].&#91;partitions] AS parti ON &#91;parti].&#91;object_id] = objz.&#91;object_id]
INNER JOIN &#91;sys].&#91;objects] AS queues ON objz.&#91;parent_object_id] = &#91;queues].&#91;object_id]
INNER JOIN sys.&#91;service_queues] &#91;mon] ON &#91;queues].&#91;object_id] = &#91;mon].&#91;object_id]
WHERE &#91;queues].&#91;type_desc] = 'SERVICE_QUEUE'
AND &#91;parti].&#91;index_id] = 1
SELECT 'If the values are other than 00000000-0000-0000-0000-000000000000 it indicates encryption is being used:
' AS Notes,COUNT(*) AS endpointCount,MIN(inbound_session_key_identifier) AS inbound_session_key_identifier, MAX(outbound_session_key_identifier) AS outbound_session_key_identifier
FROM sys.conversation_endpoints
Author: ChatGPT
### **???? Purpose of the Script**
This script is designed to **audit, diagnose, and generate corrective actions for SQL Server Service Broker** across all databases on an instance. It checks for **potential issues related to Service Broker GUIDs, queue activation, encryption, and conversation endpoints**.
---
### **???? Benefits**
??? **Prevents Service Broker Conflicts**
- Identifies duplicate `service_broker_guid` values across databases and **suggests fixes**.
- Ensures each database has a unique Service Broker identifier.
??? **Ensures Message Processing**
- Detects if the **Service Broker is disabled** in a database and **generates the appropriate command** to enable it.
- Identifies **inactive or disabled queues** and **provides commands** to activate them.
??? **Resolves Transmission Queue Errors**
- Checks for **messages stuck in the transmission queue** and **generates cleanup commands** to forcefully terminate unresolved conversations.
??? **Validates Broker Conversations & Endpoints**
- Displays **conversations awaiting processing** and categorizes their current state.
- Checks if **encryption is in use** for Service Broker communication.
---
### **???? How It Works**
1. **Detects Duplicate Service Broker GUIDs**
- If multiple databases have the same GUID, **a `SET NEW_BROKER` command is generated**.
2. **Checks if Service Broker is Enabled**
- If `is_broker_enabled = 0`, **it suggests an `ALTER DATABASE ... SET ENABLE_BROKER` command**.
3. **Analyzes the Transmission Queue**
- If messages are stuck in `sys.transmission_queue`, it generates a **cleanup command**:
```sql
DECLARE @conversation UNIQUEIDENTIFIER;
WHILE EXISTS (SELECT 1 FROM &#91;sys].&#91;transmission_queue] )
BEGIN
SET @conversation = (SELECT TOP 1 &#91;conversation_handle] FROM &#91;sys].&#91;transmission_queue] );
END CONVERSATION @conversation WITH CLEANUP;
END;
```
4. **Validates Conversation Endpoints**
- Lists conversations, their states, and services involved.
5. **Audits Service Queues**
- Generates **commands to re-enable queues** if they are disabled.
- Shows pending messages and provides a command to **read all pending messages** from a queue.
6. **Checks if Encryption is Enabled for Service Broker**
- If values in `sys.conversation_endpoints` are not `00000000-0000-0000-0000-000000000000`, **it confirms encryption is being used**.
---
### **???? Example Output**
#### **1?????? Duplicate Service Broker GUIDs**
| DatabaseName | service_broker_guid | DuplicateServiceBrokerFix |
|-------------|---------------------|---------------------------|
| DB1 | 550e8400-e29b-41d4 | `ALTER DATABASE &#91;DB1] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;` |
| DB2 | 550e8400-e29b-41d4 | `ALTER DATABASE &#91;DB2] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;` |
#### **2?????? Stuck Transmission Queue Messages**
| Note | TransmissionStatus | Expectation | NumberofItemsInTheTransmissionQueue | DrainCommand |
|------|--------------------|-------------|------------------------------------|--------------|
| All rows in the queue are errors | "Error: Deadlock detected..." | No rows should exist | 50 | Cleanup command generated |
#### **3?????? Disabled Service Broker**
| DatabaseName | is_broker_enabled | EnableCommand | ToggleCommand |
|-------------|------------------|---------------|---------------|
| DB1 | 0 | `ALTER DATABASE &#91;DB1] SET ENABLE_BROKER;` | `ALTER DATABASE &#91;DB1] SET DISABLE_BROKER; ALTER DATABASE &#91;DB1] SET ENABLE_BROKER;` |
#### **4?????? Inactive Service Queues**
| name | is_receive_enabled | is_enqueue_enabled | ClearQueueCommand |
|------|--------------------|--------------------|--------------------|
| OrdersQueue | 0 | 1 | `ALTER QUEUE &#91;dbo].&#91;OrdersQueue] WITH STATUS = ON;` |
#### **5?????? Service Broker Encryption**
| Notes | endpointCount | inbound_session_key_identifier | outbound_session_key_identifier |
|------|--------------|--------------------------------|--------------------------------|
| If the values are other than `00000000-0000-0000-0000-000000000000` it indicates encryption is being used | 2 | `A1B2C3D4...` | `X9Y8Z7W6...` |
---
### **???? How to Use**
1. **Run the script** to get a diagnostic report.
2. **Review the suggested commands** (`ALTER DATABASE`, `ALTER QUEUE`, `END CONVERSATION`).
3. **Execute the generated commands** to **fix Service Broker issues**.
4. If problems persist, **check the SQL Server Error Log for detailed broker-related errors**.
This script is **extremely useful after database restores, migrations, or troubleshooting stuck messages** in Service Broker. ????
Would you like to extend this script to check **activation stored procedures** or **detailed message body analysis**?
Author: user
Declare @TheResults varchar(max),
@vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)
SET @TheResults = '
use master
go
sp_configure ''show advanced options'',1
go
reconfigure with override
go
sp_configure ''Database Mail XPs'',1
--go
--sp_configure ''SQL Mail XPs'',0
go
reconfigure
go
'
SELECT @TheResults = @TheResults + '
--#################################################################################################
-- BEGIN Mail Settings ' + &#91;p].&#91;name] + '
--#################################################################################################
IF NOT EXISTS(SELECT * FROM &#91;msdb].&#91;dbo].&#91;sysmail_profile] WHERE &#91;name] = ''' + &#91;p].&#91;name] + ''')
BEGIN
--CREATE Profile &#91;' + &#91;p].&#91;name] + ']
EXECUTE &#91;msdb].&#91;dbo].&#91;sysmail_add_profile_sp]
@profile_name = ''' + &#91;p].&#91;name] + ''',
@description = ''' + ISNULL(&#91;p].&#91;description],'') + ''';
END --IF EXISTS profile
'
+
'
IF NOT EXISTS(SELECT * FROM &#91;msdb].&#91;dbo].&#91;sysmail_account] WHERE &#91;name] = ''' + &#91;a].&#91;name] + ''')
BEGIN
--CREATE Account &#91;' + &#91;a].&#91;name] + ']
EXECUTE &#91;msdb].&#91;dbo].&#91;sysmail_add_account_sp]
@account_name = ' + CASE WHEN &#91;a].&#91;name] IS NULL THEN ' NULL ' ELSE + '''' + &#91;a].&#91;name] + '''' END + ',
@email_address = ' + CASE WHEN &#91;a].&#91;email_address] IS NULL THEN ' NULL ' ELSE + '''' + &#91;a].&#91;email_address] + '''' END + ',
@display_name = ' + CASE WHEN &#91;a].&#91;display_name] IS NULL THEN ' NULL ' ELSE + '''' + &#91;a].&#91;display_name] + '''' END + ',
@replyto_address = ' + CASE WHEN &#91;a].&#91;replyto_address] IS NULL THEN ' NULL ' ELSE + '''' + &#91;a].&#91;replyto_address] + '''' END + ',
@description = ' + CASE WHEN &#91;a].&#91;description] IS NULL THEN ' NULL ' ELSE + '''' + &#91;a].&#91;description] + '''' END + ',
@mailserver_name = ' + CASE WHEN &#91;s].&#91;servername] IS NULL THEN ' NULL ' ELSE + '''' + &#91;s].&#91;servername] + '''' END + ',
@mailserver_type = ' + CASE WHEN &#91;s].&#91;servertype] IS NULL THEN ' NULL ' ELSE + '''' + &#91;s].&#91;servertype] + '''' END + ',
@port = ' + CASE WHEN &#91;s].&#91;port] IS NULL THEN ' NULL ' ELSE + '''' + CONVERT(VARCHAR,&#91;s].&#91;port]) + '''' END + ',
@username = ' + CASE WHEN &#91;c].&#91;credential_identity] IS NULL THEN ' NULL ' ELSE + '''' + &#91;c].&#91;credential_identity] + '''' END + ',
@password = ' + CASE WHEN &#91;c].&#91;credential_identity] IS NULL THEN ' NULL ' ELSE + '''NotTheRealPassword''' END + ',
@use_default_credentials = ' + CASE WHEN &#91;s].&#91;use_default_credentials] = 1 THEN ' 1 ' ELSE ' 0 ' END + ',
@enable_ssl = ' + CASE WHEN &#91;s].&#91;enable_ssl] = 1 THEN ' 1 ' ELSE ' 0 ' END + ';
END --IF EXISTS account
'
+ '
IF NOT EXISTS(SELECT *
FROM &#91;msdb].&#91;dbo].&#91;sysmail_profileaccount] pa
INNER JOIN &#91;msdb].&#91;dbo].&#91;sysmail_profile] p ON &#91;pa].&#91;profile_id] = &#91;p].&#91;profile_id]
INNER JOIN &#91;msdb].&#91;dbo].&#91;sysmail_account] a ON &#91;pa].&#91;account_id] = &#91;a].&#91;account_id]
WHERE p.&#91;name] = ''' + &#91;p].&#91;name] + '''
AND a.&#91;name] = ''' + &#91;a].&#91;name] + ''')
BEGIN
-- Associate Account &#91;' + &#91;a].&#91;name] + '] to Profile &#91;' + &#91;p].&#91;name] + ']
EXECUTE &#91;msdb].&#91;dbo].&#91;sysmail_add_profileaccount_sp]
@profile_name = ''' + &#91;p].&#91;name] + ''',
@account_name = ''' + &#91;a].&#91;name] + ''',
@sequence_number = ' + CONVERT(VARCHAR,&#91;pa].&#91;sequence_number]) + ' ;
END --IF EXISTS associate accounts to profiles
--new block for public profiles and default profiles
IF NOT EXISTS( SELECT * FROM &#91;msdb].&#91;dbo].&#91;sysmail_principalprofile] &#91;pp]
INNER JOIN &#91;msdb].&#91;dbo].&#91;sysmail_profile] p
ON &#91;pp].&#91;profile_id] = &#91;p].&#91;profile_id]
WHERE &#91;p].&#91;name] = ''' + &#91;p].&#91;name] + ''')
BEGIN
-- do stuff with sysmail_add_principalprofile_sp
EXECUTE &#91;msdb].&#91;dbo].&#91;sysmail_add_principalprofile_sp] @profile_name =''' + &#91;p].&#91;name] + ''''
+ CASE
WHEN pp.&#91;principal_sid] = 0x00
THEN ' ,@principal_name = ''public'''
WHEN pp.&#91;principal_sid] IS NULL
THEN ' ,@principal_name = NULL'
ELSE ' ,@principal_name = ''' + SUSER_NAME(pp.&#91;principal_sid]) + ''''
END
+ CASE
WHEN pp.&#91;is_default] = 1
THEN ',@is_default = 1'
ELSE ',@is_default = 0'
END + '
END
--#################################################################################################
-- Drop Settings For ' + &#91;p].&#91;name] + '
--#################################################################################################
/*
IF EXISTS(SELECT *
FROM &#91;msdb].&#91;dbo].&#91;sysmail_profileaccount] pa
INNER JOIN &#91;msdb].&#91;dbo].&#91;sysmail_profile] p ON &#91;pa].&#91;profile_id] = &#91;p].&#91;profile_id]
INNER JOIN &#91;msdb].&#91;dbo].&#91;sysmail_account] a ON &#91;p].&#91;account_id] = &#91;a].&#91;account_id]
WHERE p.name = ''' + &#91;p].&#91;name] + '''
AND a.name = ''' + &#91;a].&#91;name] + ''')
BEGIN
EXECUTE &#91;msdb].&#91;dbo].&#91;sysmail_delete_profileaccount_sp] @profile_name = ''' + &#91;p].&#91;name] + ''',@account_name = ''' + &#91;a].&#91;name] + '''
END
IF EXISTS(SELECT * FROM &#91;msdb].&#91;dbo].&#91;sysmail_account] WHERE &#91;name] = ''' + &#91;a].&#91;name] + ''')
BEGIN
EXECUTE &#91;msdb].&#91;dbo].&#91;sysmail_delete_account_sp] @account_name = ''' + &#91;a].&#91;name] + '''
END
IF EXISTS(SELECT * FROM &#91;msdb].&#91;dbo].&#91;sysmail_profile] WHERE &#91;name] = ''' + &#91;p].&#91;name] + ''')
BEGIN
EXECUTE &#91;msdb].&#91;dbo].&#91;sysmail_delete_profile_sp] @profile_name = ''' + &#91;p].&#91;name] + '''
END
*/
' --select *
FROM &#91;msdb].&#91;dbo].&#91;sysmail_profile] &#91;p]
LEFT JOIN &#91;msdb].&#91;dbo].&#91;sysmail_profileaccount] &#91;pa] ON &#91;p].&#91;profile_id] = &#91;pa].&#91;profile_id]
LEFT JOIN &#91;msdb].&#91;dbo].&#91;sysmail_account] &#91;a] ON &#91;pa].&#91;account_id] = &#91;a].&#91;account_id]
LEFT OUTER JOIN &#91;msdb].&#91;dbo].&#91;sysmail_server] &#91;s] ON &#91;a].&#91;account_id] = &#91;s].&#91;account_id]
LEFT OUTER JOIN &#91;sys].&#91;credentials] &#91;c] ON &#91;s].&#91;credential_id] = &#91;c].&#91;credential_id]
LEFT OUTER JOIN &#91;msdb].&#91;dbo].&#91;sysmail_principalprofile] &#91;pp] ON &#91;p].&#91;profile_id] = &#91;pp].&#91;profile_id]
SELECT @TheResults
--the lines results might end in a number of different chars:
--clean it Up
SET @TheResults = REPLACE(REPLACE(@TheResults,CHAR(10),CHAR(13) + CHAR(10)),CHAR(13) + CHAR(13), CHAR(13))
;WITH &#91;E01](&#91;N]) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1), -- 10 or 10E01 rows
&#91;E02](&#91;N]) AS (SELECT 1 FROM &#91;E01] &#91;a], &#91;E01] &#91;b]), -- 100 or 10E02 rows
&#91;E04](&#91;N]) AS (SELECT 1 FROM &#91;E02] &#91;a], &#91;E02] &#91;b]), -- 10,000 or 10E04 rows
&#91;E08](&#91;N]) AS (SELECT 1 FROM &#91;E04] &#91;a], &#91;E04] &#91;b]), --100,000,000 or 10E08 rows
--E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need,
&#91;Tally](&#91;N]) AS (SELECT ROW_NUMBER() OVER (ORDER BY &#91;N]) FROM &#91;E08]),
&#91;ItemSplit](
&#91;ItemOrder],
&#91;Item]
) as (
SELECT &#91;N],
SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,&#91;N] + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @TheResults + @vbCrLf,&#91;N] + DATALENGTH(@vbCrLf)) - &#91;N] - DATALENGTH(@vbCrLf))
FROM &#91;Tally]
WHERE &#91;N] < DATALENGTH(@vbCrLf + @TheResults)
--WHERE N < DATALENGTH(@vbCrLf + @INPUT) -- REMOVED added @vbCrLf
AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,&#91;N],DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter
)
select
row_number() over (order by &#91;ItemOrder]) as &#91;ItemID],
&#91;Item]
from &#91;ItemSplit]
Author: user
Declare @TheResults varchar(max),
@vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)
SET @TheResults = '
use master
go
sp_configure ''show advanced options'',1
go
reconfigure with override
go
sp_configure ''Database Mail XPs'',1
--go
--sp_configure ''SQL Mail XPs'',0
go
reconfigure
go
'
SELECT @TheResults = @TheResults + '
--#################################################################################################
-- BEGIN Mail Settings ' + &#91;p].&#91;name] + '
--#################################################################################################
IF NOT EXISTS(SELECT * FROM &#91;msdb].&#91;dbo].&#91;sysmail_profile] WHERE &#91;name] = ''' + &#91;p].&#91;name] + ''')
BEGIN
--CREATE Profile &#91;' + &#91;p].&#91;name] + ']
EXECUTE &#91;msdb].&#91;dbo].&#91;sysmail_add_profile_sp]
@profile_name = ''' + &#91;p].&#91;name] + ''',
@description = ''' + ISNULL(&#91;p].&#91;description],'') + ''';
END --IF EXISTS profile
'
+
'
IF NOT EXISTS(SELECT * FROM &#91;msdb].&#91;dbo].&#91;sysmail_account] WHERE &#91;name] = ''' + &#91;a].&#91;name] + ''')
BEGIN
--CREATE Account &#91;' + &#91;a].&#91;name] + ']
EXECUTE &#91;msdb].&#91;dbo].&#91;sysmail_add_account_sp]
@account_name = ' + CASE WHEN &#91;a].&#91;name] IS NULL THEN ' NULL ' ELSE + '''' + &#91;a].&#91;name] + '''' END + ',
@email_address = ' + CASE WHEN &#91;a].&#91;email_address] IS NULL THEN ' NULL ' ELSE + '''' + &#91;a].&#91;email_address] + '''' END + ',
@display_name = ' + CASE WHEN &#91;a].&#91;display_name] IS NULL THEN ' NULL ' ELSE + '''' + &#91;a].&#91;display_name] + '''' END + ',
@replyto_address = ' + CASE WHEN &#91;a].&#91;replyto_address] IS NULL THEN ' NULL ' ELSE + '''' + &#91;a].&#91;replyto_address] + '''' END + ',
@description = ' + CASE WHEN &#91;a].&#91;description] IS NULL THEN ' NULL ' ELSE + '''' + &#91;a].&#91;description] + '''' END + ',
@mailserver_name = ' + CASE WHEN &#91;s].&#91;servername] IS NULL THEN ' NULL ' ELSE + '''' + &#91;s].&#91;servername] + '''' END + ',
@mailserver_type = ' + CASE WHEN &#91;s].&#91;servertype] IS NULL THEN ' NULL ' ELSE + '''' + &#91;s].&#91;servertype] + '''' END + ',
@port = ' + CASE WHEN &#91;s].&#91;port] IS NULL THEN ' NULL ' ELSE + '''' + CONVERT(VARCHAR,&#91;s].&#91;port]) + '''' END + ',
@username = ' + CASE WHEN &#91;c].&#91;credential_identity] IS NULL THEN ' NULL ' ELSE + '''' + &#91;c].&#91;credential_identity] + '''' END + ',
@password = ' + CASE WHEN &#91;c].&#91;credential_identity] IS NULL THEN ' NULL ' ELSE + '''NotTheRealPassword''' END + ',
@use_default_credentials = ' + CASE WHEN &#91;s].&#91;use_default_credentials] = 1 THEN ' 1 ' ELSE ' 0 ' END + ',
@enable_ssl = ' + CASE WHEN &#91;s].&#91;enable_ssl] = 1 THEN ' 1 ' ELSE ' 0 ' END + ';
END --IF EXISTS account
'
+ '
IF NOT EXISTS(SELECT *
FROM &#91;msdb].&#91;dbo].&#91;sysmail_profileaccount] pa
INNER JOIN &#91;msdb].&#91;dbo].&#91;sysmail_profile] p ON &#91;pa].&#91;profile_id] = &#91;p].&#91;profile_id]
INNER JOIN &#91;msdb].&#91;dbo].&#91;sysmail_account] a ON &#91;pa].&#91;account_id] = &#91;a].&#91;account_id]
WHERE p.&#91;name] = ''' + &#91;p].&#91;name] + '''
AND a.&#91;name] = ''' + &#91;a].&#91;name] + ''')
BEGIN
-- Associate Account &#91;' + &#91;a].&#91;name] + '] to Profile &#91;' + &#91;p].&#91;name] + ']
EXECUTE &#91;msdb].&#91;dbo].&#91;sysmail_add_profileaccount_sp]
@profile_name = ''' + &#91;p].&#91;name] + ''',
@account_name = ''' + &#91;a].&#91;name] + ''',
@sequence_number = ' + CONVERT(VARCHAR,&#91;pa].&#91;sequence_number]) + ' ;
END --IF EXISTS associate accounts to profiles
--new block for public profiles and default profiles
IF NOT EXISTS( SELECT * FROM &#91;msdb].&#91;dbo].&#91;sysmail_principalprofile] &#91;pp]
INNER JOIN &#91;msdb].&#91;dbo].&#91;sysmail_profile] p
ON &#91;pp].&#91;profile_id] = &#91;p].&#91;profile_id]
WHERE &#91;p].&#91;name] = ''' + &#91;p].&#91;name] + ''')
BEGIN
-- do stuff with sysmail_add_principalprofile_sp
EXECUTE &#91;msdb].&#91;dbo].&#91;sysmail_add_principalprofile_sp] @profile_name =''' + &#91;p].&#91;name] + ''''
+ CASE
WHEN pp.&#91;principal_sid] = 0x00
THEN ' ,@principal_name = ''public'''
WHEN pp.&#91;principal_sid] IS NULL
THEN ' ,@principal_name = NULL'
ELSE ' ,@principal_name = ''' + SUSER_NAME(pp.&#91;principal_sid]) + ''''
END
+ CASE
WHEN pp.&#91;is_default] = 1
THEN ',@is_default = 1'
ELSE ',@is_default = 0'
END + '
END
--#################################################################################################
-- Drop Settings For ' + &#91;p].&#91;name] + '
--#################################################################################################
/*
IF EXISTS(SELECT *
FROM &#91;msdb].&#91;dbo].&#91;sysmail_profileaccount] pa
INNER JOIN &#91;msdb].&#91;dbo].&#91;sysmail_profile] p ON &#91;pa].&#91;profile_id] = &#91;p].&#91;profile_id]
INNER JOIN &#91;msdb].&#91;dbo].&#91;sysmail_account] a ON &#91;p].&#91;account_id] = &#91;a].&#91;account_id]
WHERE p.name = ''' + &#91;p].&#91;name] + '''
AND a.name = ''' + &#91;a].&#91;name] + ''')
BEGIN
EXECUTE &#91;msdb].&#91;dbo].&#91;sysmail_delete_profileaccount_sp] @profile_name = ''' + &#91;p].&#91;name] + ''',@account_name = ''' + &#91;a].&#91;name] + '''
END
IF EXISTS(SELECT * FROM &#91;msdb].&#91;dbo].&#91;sysmail_account] WHERE &#91;name] = ''' + &#91;a].&#91;name] + ''')
BEGIN
EXECUTE &#91;msdb].&#91;dbo].&#91;sysmail_delete_account_sp] @account_name = ''' + &#91;a].&#91;name] + '''
END
IF EXISTS(SELECT * FROM &#91;msdb].&#91;dbo].&#91;sysmail_profile] WHERE &#91;name] = ''' + &#91;p].&#91;name] + ''')
BEGIN
EXECUTE &#91;msdb].&#91;dbo].&#91;sysmail_delete_profile_sp] @profile_name = ''' + &#91;p].&#91;name] + '''
END
*/
' --select *
FROM &#91;msdb].&#91;dbo].&#91;sysmail_profile] &#91;p]
LEFT JOIN &#91;msdb].&#91;dbo].&#91;sysmail_profileaccount] &#91;pa] ON &#91;p].&#91;profile_id] = &#91;pa].&#91;profile_id]
LEFT JOIN &#91;msdb].&#91;dbo].&#91;sysmail_account] &#91;a] ON &#91;pa].&#91;account_id] = &#91;a].&#91;account_id]
LEFT OUTER JOIN &#91;msdb].&#91;dbo].&#91;sysmail_server] &#91;s] ON &#91;a].&#91;account_id] = &#91;s].&#91;account_id]
LEFT OUTER JOIN &#91;sys].&#91;credentials] &#91;c] ON &#91;s].&#91;credential_id] = &#91;c].&#91;credential_id]
LEFT OUTER JOIN &#91;msdb].&#91;dbo].&#91;sysmail_principalprofile] &#91;pp] ON &#91;p].&#91;profile_id] = &#91;pp].&#91;profile_id]
SELECT @TheResults
--the lines results might end in a number of different chars:
--clean it Up
SET @TheResults = REPLACE(REPLACE(@TheResults,CHAR(10),CHAR(13) + CHAR(10)),CHAR(13) + CHAR(13), CHAR(13))
;WITH &#91;E01](&#91;N]) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1), -- 10 or 10E01 rows
&#91;E02](&#91;N]) AS (SELECT 1 FROM &#91;E01] &#91;a], &#91;E01] &#91;b]), -- 100 or 10E02 rows
&#91;E04](&#91;N]) AS (SELECT 1 FROM &#91;E02] &#91;a], &#91;E02] &#91;b]), -- 10,000 or 10E04 rows
&#91;E08](&#91;N]) AS (SELECT 1 FROM &#91;E04] &#91;a], &#91;E04] &#91;b]), --100,000,000 or 10E08 rows
--E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need,
&#91;Tally](&#91;N]) AS (SELECT ROW_NUMBER() OVER (ORDER BY &#91;N]) FROM &#91;E08]),
&#91;ItemSplit](
&#91;ItemOrder],
&#91;Item]
) as (
SELECT &#91;N],
SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,&#91;N] + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @TheResults + @vbCrLf,&#91;N] + DATALENGTH(@vbCrLf)) - &#91;N] - DATALENGTH(@vbCrLf))
FROM &#91;Tally]
WHERE &#91;N] < DATALENGTH(@vbCrLf + @TheResults)
--WHERE N < DATALENGTH(@vbCrLf + @INPUT) -- REMOVED added @vbCrLf
AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,&#91;N],DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter
)
select
row_number() over (order by &#91;ItemOrder]) as &#91;ItemID],
&#91;Item]
from &#91;ItemSplit]
Author: ChatGPT
It looks like this script is designed to configure **Database Mail** in SQL Server, ensuring that the necessary profiles, accounts, and associations are correctly set up. Here's a breakdown of what it does:
### **Purpose of the Script**
- Enables the **Database Mail XPs** feature.
- Ensures that the **Database Mail profile and account** exist in SQL Server.
- Creates or updates **mail accounts, profiles, and their associations**.
- Sets up **default profile and security settings** for Database Mail.
- Provides a **cleanup section** (commented out) that allows for the removal of existing configurations if needed.
### **Advantages**
??? **Automates Configuration** ??? Eliminates manual setup by dynamically checking and applying the correct settings.
??? **Ensures Idempotency** ??? Prevents duplicate objects by checking for existing profiles, accounts, and associations before creating them.
??? **Security Considerations** ??? Does not store plaintext passwords but uses placeholders (`NotTheRealPassword`).
??? **Standardizes Email Configuration** ??? Ensures consistency across multiple SQL Server instances.
??? **Provides Cleanup Options** ??? Includes commented-out DROP statements for removing profiles and accounts if needed.
??? **Handles Line Breaks Consistently** ??? Cleans up carriage return inconsistencies in the output.
Would you like to refine the script further or add any additional functionality, such as **validations or alerts**? ????