March 6, 2018 at 4:22 am
Hi All,
I have been using the dbWarden for about 2 weeks now and everything was working fine with monitoring and also a nice daily report to myself and the team.
So coming to the problem now, i have actually installed this on 4 sql instances which are on 2008 R2 SP3 and for a wierd reason of which i am not aware of the daily report HealthReport job which executes the rpt_HealthReport stored procedure is running forever.
Previously it used to run for about 1-2 mins but this morning it kept on running for more than 3 hours and i had to kill the session as it using most of the CPU and temp DB resources with massive amounts of logical & physical reads.
Saying all the above the report job works perfectly fine on the other 3 instances without any problem and reports are being emailed as usual, it is just this one instance where the job is running longer than expected and just goes on and on and on....until the procedure is cancelled or the job is stopped or killed.
Can anyone please point out what could the problem be and help me out.
@Stevie Rounds & @michael-2 Rounds
Thanks in advance
March 6, 2018 at 5:22 am
S_M - Tuesday, March 6, 2018 4:22 AMHi All,I have been using the dbWarden for about 2 weeks now and everything was working fine with monitoring and also a nice daily report to myself and the team.
So coming to the problem now, i have actually installed this on 4 sql instances which are on 2008 R2 SP3 and for a wierd reason of which i am not aware of the daily report HealthReport job which executes the rpt_HealthReport stored procedure is running forever.
Previously it used to run for about 1-2 mins but this morning it kept on running for more than 3 hours and i had to kill the session as it using most of the CPU and temp DB resources with massive amounts of logical & physical reads.
Saying all the above the report job works perfectly fine on the other 3 instances without any problem and reports are being emailed as usual, it is just this one instance where the job is running longer than expected and just goes on and on and on....until the procedure is cancelled or the job is stopped or killed.Can anyone please point out what could the problem be and help me out.
@Stevie Rounds & @michael-2 Rounds
Thanks in advance
I am not sure what happened, but the stored procedure ran successfully now after 3 hours....Its a bit WIERD
March 21, 2018 at 9:37 pm
Hello,
Firstly huge thanks for creating such awesome scripts. I want to setup this script on my enviornment and I dont have mail sending configuration, so just want to generate Health Check report daily onto disk. Also, I dont need any alerts at this time.
Can anyone help me what changes are needed for this ?
March 26, 2018 at 8:28 pm
jayshah7 - Wednesday, March 21, 2018 9:37 PMHello,Firstly huge thanks for creating such awesome scripts. I want to setup this script on my enviornment and I dont have mail sending configuration, so just want to generate Health Check report daily onto disk. Also, I dont need any alerts at this time.
Can anyone help me what changes are needed for this ?
Appreciate your help authors !!!!
July 5, 2018 at 9:40 am
Hello,
I have been using db warden for about 6 months now and it works quite brilliantly, but it just pops up with some issues which i have posted earlier and it got resolved automatically.
Now there is a new issue where the LongRunningJobsAlert SQL job which uses usp_LongRunningJobs PROC is alerting me of the there are two jobs are long running now and in process but in reality they are not running on the instance. And it is also logging the same information in the JobStatsHistory table.
To confirm this i have checked both the job history & the job activity monitor and no job was actually running. This exclusively alerts only for two jobs out of which one is a differential backup maintenance plan job and the other is the DBCC check DB job which are scheduled late in the night after 10 PM (GMT).
If possible can you shed some light on this Stevie Rounds (@Old Hand) & Micheal Rounds.
Thanks in advance.
March 20, 2019 at 4:08 am
Hi Micheal & Stevie,
I have been using the dbWarden Monitoring tool for about an year now and seems to work brilliantly as this is currently running on SQL Server 2008 R2.
It does work fine even in SQL 2016 as well, but when I recently added the scripts to one of our SQL server 2017 development instance the HealthReport jobs keeps failing with the below error,
Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Column name or number of supplied values does not match table definition. [SQLSTATE 21S01] (Error 213). The step failed.
And when executed the rpt_HealthReport stored procedure explicitly we get the following error,
Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 213, Level 16, State 7, Procedure sp_helpdistributor, Line 434 [Batch Start Line 2]
Column name or number of supplied values does not match table definition.
Initially I was looking for the sp_helpdistributor stored procedure, but later on realised that it was a system SP and when compared the parameters of the stored procedure between SQL 2008 R2 & SQL 2017, noticed that there has been inclusion of new parameters in SQL 2017 (delete batch size xact, delete batch size cmd, distribution_listener), but delete batch size xact, delete batch size cmd parameters were also part of SQL 2016 which worked fine and I think it is the distribution_listener parameter which was causing the failure of the job & the SP.
So after modifying the create table script as below for #REPLINFO temporary table the procedure and the job worked perfectly fine,
/* Replication Distributor */
CREATE TABLE #REPLINFO (
distributor NVARCHAR(128) NULL,
[distribution database] NVARCHAR(128) NULL,
directory NVARCHAR(500),
account NVARCHAR(200),
[min distrib retention] INT,
[max distrib retention] INT,
[history retention] INT,
[history cleanup agent] NVARCHAR(500),
[distribution cleanup agent] NVARCHAR(500),
[rpc server name] NVARCHAR(200),
[rpc login name] NVARCHAR(200),
publisher_type NVARCHAR(200),
[delete batch size xact] INT,
[delete batch size cmd] INT,
distribution_listener NVARCHAR(128)
)
Thanks again for your scripts Micheal & Stevie Rounds.
December 14, 2020 at 7:33 pm
I was trying to use this on a SQL 2019 server but when the report is sent it is completely blank. Tried setting the Compatibility to SQL 2017 but still did not work. I put this on a SQL 2017 server and it worked perfectly.
Anyone have any ideas?
Viewing 7 posts - 181 through 186 (of 186 total)
You must be logged in to reply to this topic. Login to reply