dbWarden - A Free SQL Server Monitoring Package

Question

dbWarden - A Free SQL Server Monitoring Package

Viewing 15 posts - 106 through 120 (of 186 total)

You must be logged in to reply to this topic. Login to reply

SQLR45 SSC Journeyman Points: 77 More actions · Answer 1

Yes I'm sure, here's the original error:

Message

Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Conversion failed when converting the nvarchar value '9 lockTimeout=' to data type int. [SQLSTATE 22018] (Error 245). The step failed.

You see line 8 in my previous post because I have taken that part of the script to troubleshoot.

usp_TodaysDeadlocks

Msg 245, Level 16, State 1, Procedure usp_TodaysDeadlocks, Line 40

Conversion failed when converting the nvarchar value '9 lockTimeout=' to data type int.

same error.

Thanks,

philcart SSC-Forever Points: 47794 More actions · Answer 2

Ok, that makes sense now.

So if you select from the #ERRORLOG table, what do you see? I'm going to guess that you'll see "9 lockTimeout=" in the LogDate column. If that's what is happening, then wrap the CONVERT statements in a CASE that checks ISDATE.

EG: Something like this

SELECT DISTINCT

CASE

WHEN ISDATE(LogDate) THEN CONVERT(VARCHAR(30), LogDate, 120)

ELSE NULL

END as LogDate

FROM #ERRORLOG

--------------------
Colt 45 - the original point and click interface

michaelrounds SSC-Addicted Points: 490 More actions · Answer 3

usp_TodaysDeadlocks and the health report have the same code. I had run into this issue before, it has to do with text formatting differences. I came up with the logic to parse out the specific deadlock info Text, but there are issues with it depending on the systems configuration.

It's hard to troubleshoot though as none of my current test boxes exhibit this behavior.

You can try commenting out columns that deal with the Text column in #ERRORLOG to help narrow down which area is failing. The problem will lie in one or more of the Victim or Locking Login/SPID/

Hostname columns.

SQLR45 SSC Journeyman Points: 77 More actions · Answer 4

Thanks guys,

I think i'm near a resolution, if I add

set dateformat dmy

In the proc, I have a different error message:

Msg 242, Level 16, State 3, Procedure usp_TodaysDeadlocks, Line 35

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The statement has been terminated.

(1 row(s) affected)

It might be something with how date time are stored in my server, i'm looking forward to find how it is store.

tarat99 Newbie Points: 1 More actions · Answer 5

This tool looks extremely interesting!

One obvious question comes to mind-- has anyone tried to modify this tool/set of scripts to allow centralized monitoring of multiple SQL Servers from a single central server?

I think this would greatly simplify installation/deployment and increase adoption of this solution.

So instead of installing individually on each monitored server, one would install this tool/dbWarden database once on the central server and this server would then monitor other instances through DMVs, etc. and send alerts and daily reports.

Otherwise, it's simply not practical to use this tool in environments with 20+ SQL Servers, especially if one has to upgrade to the latest version/set of fixes each week.

Thanks!!

SQLR45 SSC Journeyman Points: 77 More actions · Answer 6

Hi tarat99,

You can use SQL Overview, it's using SSIS,

http://qa.sqlservercentral.com/articles/Monitoring/69649/[/url]

It's not the same info but working good to.

michaelrounds SSC-Addicted Points: 490 More actions · Answer 7

tarat99 (5/12/2013)
This tool looks extremely interesting!
One obvious question comes to mind-- has anyone tried to modify this tool/set of scripts to allow centralized monitoring of multiple SQL Servers from a single central server?
I think this would greatly simplify installation/deployment and increase adoption of this solution.
So instead of installing individually on each monitored server, one would install this tool/dbWarden database once on the central server and this server would then monitor other instances through DMVs, etc. and send alerts and daily reports.
Otherwise, it's simply not practical to use this tool in environments with 20+ SQL Servers, especially if one has to upgrade to the latest version/set of fixes each week.
Thanks!!

Hi Tara,

That's something my wife and I have discussed quite a bit. I run dbWarden on many servers and use Registered Servers in SSMS to help manage deploying updates. It works quite nicely.

I agree with you though, having a centralized server with the ability to control configurations of other servers from one location would be nice.

Regards,

Michael

michaelrounds SSC-Addicted Points: 490 More actions · Answer 8

SQLR45 (5/9/2013)
Yes I'm sure, here's the original error:
Message
Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Conversion failed when converting the nvarchar value '9 lockTimeout=' to data type int. [SQLSTATE 22018] (Error 245). The step failed.
You see line 8 in my previous post because I have taken that part of the script to troubleshoot.
usp_TodaysDeadlocks
Msg 245, Level 16, State 1, Procedure usp_TodaysDeadlocks, Line 40
Conversion failed when converting the nvarchar value '9 lockTimeout=' to data type int.
same error.
Thanks,

Alchemistmatt found an error in the parsing of the error log that is likely causing this issue. The fix will be in the next version released later today (v 2.4.1)

michaelrounds SSC-Addicted Points: 490 More actions · Answer 9

v2.4.1 is available for download from the SourceForge page.

There are a bunch of bug fixes and a couple big changes are in this release.

A couple things to mention:

Blank HealthReports and errors in the Deadlock section are hopefully a thing of the past!

The biggest change was new triggers on the DatabaseSettings table.

When you INSERT a record into this table and SchemaTracking is set to 1, the SchemaChangeLog table and Database trigger will automatically be installed.

When you UPDATE the SchemaTracking column to 1, the SchemaChangeLog table and Database trigger will automatically be installed if they don't already exist.

When you UPDATE the SchemaTracking column to 0, the Database trigger will automatically be dropped if it exists. The SchemaChangeLog table will remain for historical data preservation.

When you DELETE a record in DatabaseSettings, it will drop the Database trigger.

Read through the change log, which is now located on the Wiki page on this site. All revision history has been removed from the script.

BigSam SSCrazy Points: 2146 More actions · Answer 10

I've found this tool very helpful, in automating too many tasks, we've been doing manually or with a combination of tools. Having everything in one place is wonderful.

We also have a number of remote databases (SQL Express); is there a way to include databases from those sites into this tool?

Thanks,

BigSam

SAinCA SSCrazy Points: 2162 More actions · Answer 11

BigSam (5/23/2013)
I've found this tool very helpful, in automating too many tasks, we've been doing manually or with a combination of tools. Having everything in one place is wonderful.
We also have a number of remote databases (SQL Express); is there a way to include databases from those sites into this tool?
Thanks,
BigSam

I have the exact same topology! Very interested to know the answer to Sam's final question.

michaelrounds SSC-Addicted Points: 490 More actions · Answer 12

Hey everyone,

Just wanted to let everyone know that we've released dbWarden 2.5! It's posted on the Sourceforge page, here: https://sourceforge.net/projects/dbwarden

I'm probably not going to update this thread with new releases anymore. Check out the Blog and Wiki on dbWarden's Sourceforge page for future updates and news.

Feel free to continue posting suggestions here, via email or on the SF page.

2.5 release notes: (The full changelog details are available on the Wiki)

A few big features and many bug fixes in this release.

- The biggest part of this release is the addition of sp_Sessions, replacing sp_Query. sp_Sessions is a complete rewrite from sp_Query (which was a temporary workaround after removing integration with another utility).

- The QueryHistory table has been expanded to collect the new data that sp_sessions gathers.

A couple major bugs with the schema change tracking has also been resolved.

If you delete a database, the health report will now tell you that Schema change tracking is enabled in the DatabaseSettings table, but the database no longer exists.

The Health Report has new sections: Database Settings and SQL Server Config.

- The Database Settings section will show you system settings that have been changed from their default value.

- The SQL Server Config section will show you things like DB Owner, Compatibility level, User access mode (single, multi, etc), whether Encryption is on, etc.

- There are also new parameters in the AlertSettings table for the Health Report: ShowLogBackups, ShowErrorLog, ShowDatabaseSettings, ShowServerConfigSettings

It will also alert you to databases that haven't been recently backed up.

SQLR45 SSC Journeyman Points: 77 More actions · Answer 13

Hello,

Any clue why version 2.5 is sending empty health report on 10.50.4000 ?

They are both instances on the same cluster, and both not working, what needs to be enable for the HTML report to be generated?

*Edit

Can that be cause by the non-standard collation?

SQL_Latin1_General_CP1_CI_AI

JonR76 Old Hand Points: 303 More actions · Answer 14

Hello All.

Just started using the dbWarden yesterday. Looks like an awesome tool. One issue that I'm having and hoping someone has seen. My Health Check report yields the error below, complaining about a linked server that I have. Has anyone seen this? I am at 10.50.1600. Thanks for any help!

Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "PERS_ORACLE" was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391) OLE DB provider "OraOLEDB.Oracle" for linked server "PERS_ORACLE" returned message "New transaction cannot enlist in the specified transaction coordinator. ". [SQLSTATE 01000] (Error 7412). The step failed.

michaelrounds SSC-Addicted Points: 490 More actions · Answer 15

SQLR45 (6/11/2013)
Hello,
Any clue why version 2.5 is sending empty health report on 10.50.4000 ?
They are both instances on the same cluster, and both not working, what needs to be enable for the HTML report to be generated?
*Edit
Can that be cause by the non-standard collation?
SQL_Latin1_General_CP1_CI_AI

Hmm, I thought these empty health report issues have all been resolved, darn. I'll comb over the HTML again to see where a NULL value might pop up (which causes the HTML blob to blank out).