October 14, 2013 at 10:53 am
shane.davies (10/10/2013)
shane.davies (9/30/2013)
michaelrounds (9/27/2013)
shane.davies (9/27/2013)
I have installed this today and it works fine except for the health report. This fails on linked servers. Is there any way this part can be disabled?Thanks
Shane
What error message are you receiving? It will help me nail down the issue.
Thanks![/quote
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SIM-5SERIES-00" was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391) OLE DB provider "SQLNCLI10" for linked server "SIM-5SERIES-00" returned message "The partner transaction manager has disabled its support for remote/network transactions.". [SQLSTATE 01000] (Error 7412)]
Basically I do not want this to cover linked servers, just the BD's on the server this is running on.
Thanks
Shane
Has there been anydevelopment on this? I haven't seen a reply since I posted the error message?
Thanks
Shane
Hi,
Sorry Shane, I haven't had any time lately to work on dbWarden. I'm hoping I get some time next week. There are a number of bugs that need resolved at this point.
October 15, 2013 at 2:22 am
michaelrounds (10/14/2013)
shane.davies (10/10/2013)
shane.davies (9/30/2013)
michaelrounds (9/27/2013)
shane.davies (9/27/2013)
I have installed this today and it works fine except for the health report. This fails on linked servers. Is there any way this part can be disabled?Thanks
Shane
What error message are you receiving? It will help me nail down the issue.
Thanks![/quote
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SIM-5SERIES-00" was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391) OLE DB provider "SQLNCLI10" for linked server "SIM-5SERIES-00" returned message "The partner transaction manager has disabled its support for remote/network transactions.". [SQLSTATE 01000] (Error 7412)]
Basically I do not want this to cover linked servers, just the BD's on the server this is running on.
Thanks
Shane
Has there been anydevelopment on this? I haven't seen a reply since I posted the error message?
Thanks
Shane
Hi,
Sorry Shane, I haven't had any time lately to work on dbWarden. I'm hoping I get some time next week. There are a number of bugs that need resolved at this point.
Thanks Michael, no panic, I am testing these on our development server and once I am happy hope to deploy to production.
Many Thanks
Shane
December 30, 2013 at 2:06 pm
Apparently a couple databases were dropped. DBWarden sends error message that the object does not exist, <database>.<objectname -- I believe schemachanges but forget> Is this in the databasesettings table? Am I supposed to edit this table because other DBA's say databases are dropped all the time and no errors are generated.
March 4, 2014 at 1:45 pm
Love the script. I was also getting a blank report. What I discovered was that when adding the values for best_latency, worst_latency and average_latency, it did not have a value for a merge replication when building the html. Once I put another 'else' to accommodate this the report worked perfectly.
June 7, 2014 at 7:53 pm
Hi All, I'm brand new to SQL Server as just starting a Module on it at Uni and loving it to death, I think DBA is going to be my chosen career path, I have it mostly sussed out whats going on with the script and how it's going to be of use to me, but I'm having a bit of a problem as I can't get the Health Report emails to send.
The HTML is going in to the field and is being populated in the correct table, and if I copy its contents I can paste it in to a html page so it's no biggie, but I'd like to get the 06:05:00 email running just to save the hassle.
I've created several email addresses in the Management - Database Mail section and they send me test emails fine.
When running the procedure directly or running the schedualed one (server agent) it gives me an error of: execution of job 'dbWarden HealthReport failed, see the histry log.
Failed to notify 'SQL_DBA' via email.
In my params I ahve the following:
EXEC [dbWarden].dbo.rpt_HealthReport @Recepients = 'my email address', @cc = NULL, @InsertFlag = 1
If anyone has a few minutes spare and can help me out then your help will be greatly welcomed.
Oh almost forgot to the authors of the script and everyone who has previously posted on this thread, THANK YOU and BIG HUGS to ALL.
January 15, 2015 at 11:01 pm
Great script.
One question, what unit of measurement is "ss" as used in the long run queries, e.g. "elapsed time (ss)" with value of 2710.8320 ?
Thank you.
January 4, 2017 at 2:55 am
Hi,
I installed the package after reading the instructions, but I could not execute the Health Report.
It generated the following error:
Msg 213, Level 16, State 7, Procedure sp_replmonitorhelppublication, Line 322
Column name or number of supplied values does not match table definition.
After some digging I found that the problem is on section /* Replication Publisher */of the rpt_HealthReport stored procedure.
More specifically, the TEMP table #PUBINFO is populated with the output of sp_replmonitorhelppublication (on the distribution db) but it lacks the last column named publisher.
I added the missing column with a single line
[highlight="#ffff11"],publisher NVARCHAR(128) -- due to inconsistency with sp_replmonitorhelppublication's outut[/highlight]
right after line 998 on rpt_HealthReport and the Health Report was generated without any issue!
* DON'T FORGET THE COMMA AT THE BEGINNING
My SQL version is Microsoft SQL Server 2012 (SP3-GDR) (KB3194721) - 11.0.6248.0 (X64)
I will also test it on 2008R2 to see if the same tweak is needed.
Thanks for the great tool!
January 4, 2017 at 4:27 am
Great that you found the trick - it works with my SQL Server 2014 🙂
Best Regards,
Volker Bachmann
January 4, 2017 at 4:38 am
Volker.Bachmann (1/4/2017)
Great that you found the trick - it works with my SQL Server 2014 🙂
I was just about to write that the problem exists on 2014 too 🙂
January 12, 2017 at 5:29 am
As I decided to setup the package to all SQL Servers that I monitor, I am starting to face various issues that need to be addressed.
One of the common ones is when there is already a Mail Profile available but you want to use a new one instead (when you want emails sent by a different email account, without affecting the alerts that already exist).
You can overcome this by using the @profile parameter whenever the procedure msdb..sp_send_dbmail is used. Unfortunately there are numerous occurrences of this procedure but it is worth the effort.
To adopt this to the Health Report follow these steps.
Use the following to identify the existing Profiles:
EXEC msdb.dbo.sysmail_help_profile_sp
and copy the name of the profile you want to use.
Then ALTER PROCEDURE [dbo].[rpt_HealthReport]...
Find the following line
EXEC msdb..sp_send_dbmail
and add the following line right after
@profile_name= '<your_profile_name>',
It should look like this:
EXEC msdb..sp_send_dbmail
@profile_name= '<your_profile_name>',
@recipients=@Recepients,
...
The next time you execute the rpt_HealthReport the email will be sent through your preferred Profile.
If someone has the time and patience to modify the whole script so that the Profile name is a variable, and only set once, it would be great!
Regards
January 12, 2017 at 8:05 am
Hi, hoping that my findings might be useful to someone
I would like to post another issue I have encountered with the tool.
If you are in SQL Server 2008 chances are that the error
Msg 213, Level 16, State 7, Procedure sp_replmonitorhelppublication, Line 322
Column name or number of supplied values does not match table definition.
appears when you execute the rpt_HealthReport procedure.
But the resolution is different in comparison to SQL Server 2012/2014.
In 2008 it seems that a column name produced from the sp_replmonitorhelppublication function is different than the column name of the TEMP table #PUBINFO.
You have to change column
runningdisagentcount
with
runningdistagentcount
Have fun!
June 4, 2017 at 5:48 pm
I have been using this package with sql server 2016 for about half a year now for some reason in the last week has been blowing up the size of my tongue DB data files and killing my disk space any ideas what may be happening? Seems to be related to perf stats job but not sure yet.
January 15, 2018 at 8:17 am
I have been using dbwarden for the past five years...including through an upgrade from sql2008 to sql2012r2. A couple of weeks ago, SQL2012 service pack 4 came out and wrecked the dbwarden health report. Its run fine on any node that does not have the service pack installed - but those (nearly all our production servers) that have it will not run it anymore with this 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.
Given that I'm a network/server/security admin and dba by default, i'm having the hardest of times trying to figure out what table column name or number value doesn't match what table definition. Some indicators seem to point to a discrepancy between the dbwarden table columns and the temp table? Anyone else having this issue and how did you resolved it?
January 16, 2018 at 8:04 am
cobrien 20540 - Monday, January 15, 2018 8:17 AMI have been using dbwarden for the past five years...including through an upgrade from sql2008 to sql2012r2. A couple of weeks ago, SQL2012 service pack 4 came out and wrecked the dbwarden health report. Its run fine on any node that does not have the service pack installed - but those (nearly all our production servers) that have it will not run it anymore with this 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.
Given that I'm a network/server/security admin and dba by default, i'm having the hardest of times trying to figure out what table column name or number value doesn't match what table definition. Some indicators seem to point to a discrepancy between the dbwarden table columns and the temp table? Anyone else having this issue and how did you resolved it?
Hi, please find the solution in the entry above: https://www.sqlservercentral.com/Forums/FindPost1847505.aspx
With SQL 2017 there is another adjustment needed.
In the temporary table #REPLINFO need to be added two more fields at the end.
[deletebatchsize_xact] int,
[deletebatchsize_cmd] INT
Then the health report works again.
Regards,
Volker
January 16, 2018 at 9:02 am
So i thought it was going to complete...it did run longer than it has before but my celebration was for not when it returned this error:
"Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Cannot insert the value NULL into column 'DateStamp', table 'DBWarden.dbo.HealthReport'; column does not allow nulls. INSERT fails. [SQLSTATE 23000] (Error 515) Mail (Id: 6022) queued. [SQLSTATE 01000] (Error 0) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed."
sounds like i need to change the column for "datestamp' to allow nulls? so in Design for the dbo.healthreport table, i tried to check the box for DateStamp to allow nulls - but i get a save error that says it is not permitted. the changes require the follwoing tables to be dropped and recreated "healthreport" says i either made changes to a table that can't be recreated or enabled the option Prevent saving changes that require the table to be recreated.
I tried turning off prevent saving changes, but the change still fails. Note that the report DOES run and emails to me, but i get a failure notice when the job completes. I think it is going to require a change maybe in the stored procedure in regards to not allowing NULL in the DateStamp? Ideas?
Viewing 15 posts - 166 through 180 (of 186 total)
You must be logged in to reply to this topic. Login to reply