Definitions More Info.
Definition ID	2.029
Title	SQL
Category	NOTES
Definition	Job-history dolu full ise kontrol sorgusu ve delete sorgusu
Definition Description	-- hangi jobda nekadar history line var SELECT b.name, a.job_id, a.run_date, COUNT(*) NumberofRun FROM sysjobhistory a, sysjobs b WHERE a.job_id = b.job_id GROUP by a.job_id, b.name, run_date ORDER BY NumberofRun DESC -- sadece cdc history silmek için yazilan sorgu https://www.sqlservercentral.com/forums/topic/cdc-agent-job-fills-up-the-agent-jobs-history IF OBJECT_ID('tempdb.dbo.#sp_Get_sqlagent_properties') IS NOT NULL DROP TABLE #sp_Get_sqlagent_properties CREATE TABLE #sp_Get_sqlagent_properties ( auto_start varchar(1000) ,msx_server_name varchar(1000) ,sqlagent_type varchar(1000) ,startup_account varchar(1000) ,sqlserver_restart varchar(1000) ,jobhistory_max_rows varchar(1000) ,jobhistory_max_rows_per_job varchar(1000) ,errorlog_file varchar(1000) ,errorlogging_level varchar(1000) ,error_recipient varchar(1000) ,monitor_autostart varchar(1000) ,local_host_server varchar(1000) ,job_shutdown_timeout varchar(1000) ,cmdexec_account varchar(1000) ,regular_connections varchar(1000) ,host_login_name varchar(1000) ,host_login_password varchar(1000) ,login_timeout varchar(1000) ,idle_cpu_percent varchar(1000) ,idle_cpu_duration varchar(1000) ,oem_errorlog varchar(1000) ,sysadmin_only varchar(1000) ,email_profile varchar(1000) ,email_save_in_sent_folder varchar(1000) ,cpu_poller_enabled varchar(1000) ,alert_replace_runtime_tokens varchar(1000) ) INSERT INTO #sp_Get_sqlagent_properties EXEC msdb.dbo.sp_Get_sqlagent_properties ;with cdc_job_history ( job_name ,job_id ,job_history_row_num ,instance_id ,jobhistory_max_rows_per_job ) AS (SELECT j.name ,j.job_id ,ROW_NUMBER() OVER (PARTITION BY h.job_id ORDER BY h.instance_id DESC) AS job_history_row_num ,h.instance_id ,a.jobhistory_max_rows_per_job FROM msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id AND j.name like 'cdc%' CROSS APPLY (SELECT jobhistory_max_rows_per_job FROM #sp_Get_sqlagent_properties) a (jobhistory_max_rows_per_job) ) DELETE h FROM msdb.dbo.sysjobhistory h INNER JOIN cdc_job_history c ON c.instance_id = h.instance_id WHERE c.job_history_row_num > c.jobhistory_max_rows_per_job --SELECT * -- FROM msdb.dbo.sysjobhistory h -- INNER JOIN cdc_job_history c -- ON c.instance_id = h.instance_id -- WHERE c.job_history_row_num > c.jobhistory_max_rows_per_job
RecordBy	cunay
Record Date	23-06-2020 18:41:50
Düzenle
Kopyala
Sil