Właściwości SQLAgenta z T-SQL

Aby wyświetlić właściwości SQLAgenta należy wykonać polecenie

EXEC msdb.dbo.sp_get_sqlagent_properties


auto_start  msx_server_name       sqlagent_type startup_account                    sqlserver_restart jobhistory_max_rows jobhistory_max_rows_per_job errorlog_file                                                                          errorlogging_level error_recipient       monitor_autostart local_host_server            job_shutdown_timeout cmdexec_account          regular_connections host_login_name host_login_password login_timeout idle_cpu_percent idle_cpu_duration oem_errorlog sysadmin_only email_profile email_save_in_sent_folder cpu_poller_enabled alert_replace_runtime_tokens
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0           NULL                  1             NT Service\SQLSERVERAGENT          1                 1000                100                         C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT       3                  NULL                  1                 NULL                         15                   NULL                     0                   NULL            NULL                30            10               600               0            NULL          NULL          0                         0                  0


Aby wyświetlić tylko jeden parametr z właściwości SQLAgenta należy np wykonac poniższe zapytanie

CREATE TABLE #sqlagent_properties
(
auto_start INT,
msx_server_name sysname NULL,
sqlagent_type INT,
startup_account NVARCHAR(100) NULL,
sqlserver_restart INT,
jobhistory_max_rows INT,
jobhistory_max_rows_per_job INT,
errorlog_file NVARCHAR(255) NULL,
errorlogging_level INT,
errorlog_recipient NVARCHAR(255) NULL,
monitor_autostart INT,
local_host_server sysname NULL,
job_shutdown_timeout INT,
cmdexec_account VARBINARY(64) NULL,
regular_connections INT,
host_login_name sysname NULL,
host_login_password VARBINARY(512) NULL,
login_timeout INT,
idle_cpu_percent INT,
idle_cpu_duration INT,
oem_errorlog INT,
sysadmin_only NVARCHAR(64) NULL,
email_profile NVARCHAR(64) NULL,
email_save_in_sent_folder INT,
cpu_poller_enabled INT,
alert_replace_runtime_tokens INT
)

INSERT INTO #sqlagent_properties
EXEC msdb.dbo.sp_get_sqlagent_properties
GO

SELECT errorlogging_level FROM #sqlagent_properties

DROP TABLE #sqlagent_properties

Wynik:

(1 row affected)
errorlogging_level
-----------------------------------
3

(1 row affected)

Jesli chcielibyśmy zmienić powyższy parametr wykonujemy polecenie:

EXEC msdb.dbo.sp_set_sqlagent_properties @errorlogging_level=1

Commands completed successfully.

Aby wyświetlić parametry SQLAgenta w bardziej przyjazny użytkownikowi sposób udało mi się znaleźć skrypt (na stronie
https://www.sqlserverscience.com/sql-server-agent/agent-properties/ )


DECLARE @MasterPath nvarchar(512);
DECLARE @LogPath nvarchar(512);
DECLARE @ErrorLog nvarchar(512);
DECLARE @ErrorLogPath nvarchar(512);

SELECT @MasterPath=substring(physical_name, 1, len(physical_name) - charindex(N'\', reverse(physical_name)))
FROM master.sys.database_files
WHERE name=N'master';

SELECT @LogPath=substring(physical_name, 1, len(physical_name) - charindex(N'\', reverse(physical_name)))
FROM master.sys.database_files
WHERE name=N'mastlog';

SELECT @ErrorLog=cast(SERVERPROPERTY(N'errorlogfilename') as nvarchar(512));

SELECT @ErrorLogPath=substring(@ErrorLog, 1, len(@ErrorLog) - charindex(N'\', reverse(@ErrorLog)));

DECLARE @SmoRoot nvarchar(512);
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
, N'SOFTWARE\Microsoft\MSSQLServer\Setup'
, N'SQLPath'
, @SmoRoot OUTPUT;

IF OBJECT_ID('tempdb..#tmp_sp_get_sqlagent_properties') IS NOT NULL
DROP TABLE #tmp_sp_get_sqlagent_properties;
CREATE TABLE #tmp_sp_get_sqlagent_properties
(
auto_start int null
, msx_server_name sysname null
, sqlagent_type int null
, startup_account nvarchar(255) null
, sqlserver_restart int null
, jobhistory_max_rows int null
, jobhistory_max_rows_per_job int null
, errorlog_file nvarchar(255) null
, errorlogging_level int null
, error_recipient nvarchar(30) null
, monitor_autostart int null
, local_host_server sysname null
, job_shutdown_timeout int null
, cmdexec_account varbinary(64) null
, regular_connections int null
, host_login_name sysname null
, host_login_password varbinary(512) null
, login_timeout int null
, idle_cpu_percent int null
, idle_cpu_duration int null
, oem_errorlog int null
, sysadmin_only int null
, email_profile nvarchar(64) null
, email_save_in_sent_folder int null
, cpu_poller_enabled int null
, replace_alert_tokens_enabled int null
);

INSERT INTO #tmp_sp_get_sqlagent_properties
(
auto_start
, msx_server_name
, sqlagent_type
, startup_account
, sqlserver_restart
, jobhistory_max_rows
, jobhistory_max_rows_per_job
, errorlog_file
, errorlogging_level
, error_recipient
, monitor_autostart
, local_host_server
, job_shutdown_timeout
, cmdexec_account
, regular_connections
, host_login_name
, host_login_password
, login_timeout
, idle_cpu_percent
, idle_cpu_duration
, oem_errorlog
, sysadmin_only
, email_profile
, email_save_in_sent_folder
, cpu_poller_enabled
, replace_alert_tokens_enabled
)
EXEC msdb.dbo.sp_get_sqlagent_properties;

DECLARE @DatabaseMailProfile nvarchar(255);
DECLARE @AgentMailType int;
DECLARE @ServiceStartMode int;
DECLARE @ServiceAccount nvarchar(512);
DECLARE @AgtGroup nvarchar(512);

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
, N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
, N'DatabaseMailProfile'
, @param = @DatabaseMailProfile OUT
, @no_output = N'no_output';

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
, N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
, N'UseDatabaseMail'
, @param = @AgentMailType OUT
, @no_output = N'no_output';

EXEC master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
, 'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'
, N'Start'
, @ServiceStartMode OUTPUT;

EXEC master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
, 'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'
, N'ObjectName'
, @ServiceAccount OUTPUT;

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
, N'SOFTWARE\Microsoft\MSSQLServer\Setup'
, N'AGTGroup'
, @AgtGroup OUTPUT;

SELECT Items.*
FROM #tmp_sp_get_sqlagent_properties AS tsgsp
CROSS APPLY (VALUES
('Name', CAST(serverproperty(N'ServerName') AS sql_variant))
, ('MsxServerName', ISNULL(tsgsp.msx_server_name,N''))
, ('JobServerType', CONVERT(sql_variant, tsgsp.sqlagent_type))
, ('SqlServerRestart', CONVERT(sql_variant, tsgsp.sqlserver_restart))
, ('SqlAgentRestart', CONVERT(sql_variant, tsgsp.monitor_autostart))
, ('MaximumHistoryRows', CONVERT(sql_variant, tsgsp.jobhistory_max_rows))
, ('MaximumJobHistoryRows', CONVERT(sql_variant, tsgsp.jobhistory_max_rows_per_job))
, ('ErrorLogFile', CONVERT(sql_variant, tsgsp.errorlog_file))
, ('AgentLogLevel', CONVERT(sql_variant, tsgsp.errorlogging_level))
, ('NetSendRecipient', CONVERT(sql_variant, ISNULL(tsgsp.error_recipient,N'')))
, ('AgentShutdownWaitTime', CONVERT(sql_variant, tsgsp.job_shutdown_timeout))
, ('SqlAgentMailProfile', CONVERT(sql_variant, ISNULL(tsgsp.email_profile,N'')))
, ('SaveInSentFolder', CONVERT(sql_variant, CAST(tsgsp.email_save_in_sent_folder AS bit)))
, ('WriteOemErrorLog', CONVERT(sql_variant, CAST(tsgsp.oem_errorlog AS bit)))
, ('IsCpuPollingEnabled', CONVERT(sql_variant, CAST(tsgsp.cpu_poller_enabled AS bit)))
, ('IdleCpuPercentage', CONVERT(sql_variant, tsgsp.idle_cpu_percent))
, ('IdleCpuDuration', CONVERT(sql_variant, tsgsp.idle_cpu_duration))
, ('LoginTimeout', CONVERT(sql_variant, tsgsp.login_timeout))
, ('HostLoginName', CONVERT(sql_variant, ISNULL(tsgsp.host_login_name,N'')))
, ('LocalHostAlias', CONVERT(sql_variant, ISNULL(tsgsp.local_host_server,N'')))
, ('SqlAgentAutoStart', CONVERT(sql_variant, tsgsp.auto_start))
, ('ReplaceAlertTokensEnabled', CONVERT(sql_variant, tsgsp.replace_alert_tokens_enabled))
, ('DatabaseMailProfile', CONVERT(sql_variant, ISNULL(@DatabaseMailProfile,N'')))
, ('AgentMailType', CONVERT(sql_variant, ISNULL(@AgentMailType, 0)))
, ('SysAdminOnly', CONVERT(sql_variant, 1))
, ('ServiceStartMode', CONVERT(sql_variant, @ServiceStartMode))
, ('ServiceAccount', CONVERT(sql_variant, ISNULL(@ServiceAccount,N'')))
, ('AgentDomainGroup', CONVERT(sql_variant, ISNULL(suser_sname(sid_binary(ISNULL(@AgtGroup,N''))),N'')))
)Items(ItemName, ItemValue)
UNION ALL
SELECT Items.*
FROM (VALUES
('IsCaseSensitive', CAST(case when 'a' <> 'A' then 1 else 0 end AS bit))
, ('MaxPrecision', @@MAX_PRECISION)
, ('ErrorLogPath', @ErrorLogPath)
, ('RootDirectory', @SmoRoot)
, ('IsFullTextInstalled', CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit))
, ('MasterDBLogPath', @LogPath)
, ('MasterDBPath', @MasterPath)
, ('VersionString', SERVERPROPERTY(N'ProductVersion'))
, ('Edition', CAST(SERVERPROPERTY(N'Edition') AS sysname))
, ('ProductLevel', CAST(SERVERPROPERTY(N'ProductLevel') AS sysname))
, ('IsSingleUser', CAST(SERVERPROPERTY('IsSingleUser') AS bit))
, ('EngineEdition', CAST(SERVERPROPERTY('EngineEdition') AS int))
, ('Collation', convert(sysname, serverproperty(N'collation')))
, ('NetName', CAST(SERVERPROPERTY(N'MachineName') AS sysname))
, ('IsClustered', CAST(SERVERPROPERTY('IsClustered') AS bit))
, ('ResourceVersionString', SERVERPROPERTY(N'ResourceVersion'))
, ('ResourceLastUpdateDateTime', SERVERPROPERTY(N'ResourceLastUpdateDateTime'))
, ('CollationID', SERVERPROPERTY(N'CollationID'))
, ('ComparisonStyle', SERVERPROPERTY(N'ComparisonStyle'))
, ('SqlCharSet', SERVERPROPERTY(N'SqlCharSet'))
, ('SqlCharSetName', SERVERPROPERTY(N'SqlCharSetName'))
, ('SqlSortOrder', SERVERPROPERTY(N'SqlSortOrder'))
, ('SqlSortOrderName', SERVERPROPERTY(N'SqlSortOrderName'))
, ('BuildClrVersionString', SERVERPROPERTY(N'BuildClrVersion'))
, ('ComputerNamePhysicalNetBIOS', SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS'))
)Items(ItemName, ItemValue)
ORDER BY Items.ItemName;

Po wykonaniu skryptu otrzymujemy

ItemName                    ItemValue
--------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AgentDomainGroup            NT SERVICE\SQLSERVERAGENT
AgentLogLevel               3
AgentMailType               0
AgentShutdownWaitTime       15
BuildClrVersionString       v4.0.30319
Collation                   Polish_CI_AS
CollationID                 53267
ComparisonStyle             196609
ComputerNamePhysicalNetBIOS DELL_MICEK1968
DatabaseMailProfile         
Edition                     Developer Edition (64-bit)
EngineEdition               3
ErrorLogFile                C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT
ErrorLogPath                C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log
HostLoginName               
IdleCpuDuration             600
IdleCpuPercentage           10
IsCaseSensitive             0
IsClustered                 0
IsCpuPollingEnabled         0
IsFullTextInstalled         0
IsSingleUser                0
JobServerType               1
LocalHostAlias              
LoginTimeout                30
MasterDBLogPath             C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA
MasterDBPath                C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA
MaximumHistoryRows          1000
MaximumJobHistoryRows       100
MaxPrecision                38
MsxServerName               
Name                        DELL_MICEK1968
NetName                     DELL_MICEK1968
NetSendRecipient            
ProductLevel                RTM
ReplaceAlertTokensEnabled   0
ResourceLastUpdateDateTime  2021-11-03 19:53:07.600
ResourceVersionString       15.00.4188
RootDirectory               C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL
SaveInSentFolder            0
ServiceAccount              NT Service\SQLSERVERAGENT
ServiceStartMode            3
SqlAgentAutoStart           0
SqlAgentMailProfile         
SqlAgentRestart             1
SqlCharSet                  4
SqlCharSetName              cp1250
SqlServerRestart            1
SqlSortOrder                0
SqlSortOrderName            bin_ascii_8
SysAdminOnly                1
VersionString               15.0.4188.2
WriteOemErrorLog            0

(53 rows affected)
Ten wpis został opublikowany w kategorii Microsoft SQL. Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz