dbWarden - A Free SQL Server Monitoring Package

Question

dbWarden - A Free SQL Server Monitoring Package

Viewing 15 posts - 61 through 75 (of 186 total)

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

philcart SSC-Forever Points: 47796 More actions · Answer 1

Looks nice for a single server, any thoughts on producing a consolidated summary report across multiple servers?

My current repository collects data locally. That data is then aggregated to my management server which produces a daily summary via SQL Reporting Services.

It runs for SQL 2000/2005/2008, but is lacking a lot of the reporting functionality that I'd like it to have.

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

AlexSQLForums SSChampion Points: 14277 More actions · Answer 2

alchemistmatt (4/16/2013)
I'm now running this in a production environment on 8 servers. I have some suggested fixes for you
Expand [Filename] to NVARCHAR(255) when creating table #BACKUPS. I have some long database names leading to long backup paths and with nvarchar(128) the stored procedure was reporting an error due to truncation.
Personally, I would have stored FileMBSize as an int in table FileStatsHistory, but you have it as nvarchar. Thus, you need to Cast to an int when performing comparisons. In usp_CheckFiles the code selects from FileStatsHistory into #TEMP and leaves FileMBSize as nvarchar. Then you perform a comparison of "t.FileMBSize < t2.FileMBSize" and that ends up comparing a text value to a numeric value, which results in incorrect results (I was receiving e-mails saying a log file had grown when instead it had shrunk; very perplexing). The solution I went with was to explicitly define #TEMP before creating it, though another option would be to perform the cast on FileMBSize when implicitly creating #TEMP or even to add a cast in tht comparison query looking for FileMBSize increasing.
Also in usp_CheckFiles, you are excluding tables 'model' and 'tempdb' (which is the correct logic). However, those table names are stored in FileStatsHistory as '[model]' and '[tempdb]' so your IN clauses need to have "NOT IN ('model','tempdb','[model]','[tempdb]')". Furthermore, population of #TEMP3 in that procedure needs "IN ('tempdb','[tempdb]')"
My log files commonly grow, so I chose to update usp_CheckFiles to ignore log files less than 200 MB in size. This could potentially be an option if you add an options table
I have a long-running sql backup job that runs using RedGate's Sql Backup and runs via a stored procedure. Thus, I chose to create an AlertExclusions table with two columns: Category_Name and FilterLikeClause. Category_Name has a foreign key relationship to AlertSettings.Category_Name. The Alert Settings table has one row with values 'LongRunningQueries' and 'sqlBackup'. I then updated usp_LongRunningQueries to left outer join to my Alert Exclusions table and filter, like this:
SELECT QueryHistoryID, collection_time, start_time, login_time, session_id, CPU, reads, writes, physical_reads, [host_name], [Database_Name], login_name, sql_text, [program_name]
FROM [dba].dbo.QueryHistory QH
LEFT OUTER JOIN [dba].dbo.T_Alert_Exclusions AlertEx
ON AlertEx.Category_Name = 'LongRunningQueries' AND QH.sql_text LIKE AlertEx.FilterLikeClause
WHERE (DATEDIFF(ss,start_time,collection_time)) >= @QueryValue
AND (DATEDIFF(mi,collection_time,GETDATE())) < (DATEDIFF(mi,@LastCollectionTime, collection_time))
AND [Database_Name] NOT IN (SELECT [DBName] FROM [dba].dbo.DatabaseSettings WHERE LongQueryAlerts = 0)
AND sql_text NOT LIKE 'BACKUP DATABASE%'
AND sql_text NOT LIKE 'RESTORE VERIFYONLY%'
AND sql_text NOT LIKE 'ALTER INDEX%'
AND sql_text NOT LIKE 'DECLARE @BlobEater%'
AND sql_text NOT LIKE 'DBCC%'
AND sql_text NOT LIKE 'WAITFOR(RECEIVE%'
AND AlertEx.Category_Name Is Null
I updated usp_LongRunningQueries to make the following change just in case somebody enters a blank value in the CellList column instead of a null value. It would be good to make this change whereever an e-mail and/or cell info is being retrieved from the AlertSettings table
-- Change from
If @CellList Is Not Null
-- To
If IsNull(@CellList, '') <> ''

Matt

By how much does your dba database grow daily?

Alex S

ShawnTherrien Default port Points: 1481 More actions · Answer 3

File Stats - Last 24 Hours...

I'll look into it, but only the Filename and IO% are populated on my daily health report. Everything else is all 0's.

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

dbWarden 2.3.5 is now available on sourceforge. Please note, we changed the default database name from "dba" to "dbWarden"

There are also a few table changes, but the script contains safe alters to correct existing installations.

Please let me know if you encounter any issues. We tested on 2005, 2008R2 and 2012 SP1 with databases up to 1TB.

Changelog:

- Renamed created database from dba to dbWarden

- Changed defaults of DatabaseSettings table to OFF for everything. REPLACE CHANGEME in Update to DatabaseSettings to enable

databases you wish to track (this also fixes issues when trying to track a database that is OFFLINE

- Updated Instructions at the top

- usp_MemoryUsageStats - Fixed Buffer Hit Cache and Buffer Page Life showing 0 for SQL Server 2012

- dbo.FileStatsHistory table, usp_FileStats and rpt_HealthReport procs- Changed NVARCHAR(30) to BIGINT for Read/Write columns, FileMBSize, FileMBUsed, FileMBEmpty in #FILESTATS

- rpt_HealthReport - hopefully fixed the "File Stats - Last 24 hours" section to show accurate data

- usp_CheckFiles - Added database names "[model]" and "[tempdb]"

alchemistmatt Mr or Mrs. 500 Points: 525 More actions · Answer 5

Matt
By how much does your dba database grow daily?
Alex S

Good question; I hadn't checked yet. Turns out I can use the FileStatsHistory table data to answer your question: dba.mdf is growing at 7.2 MB per day on a system with 148 databases. I plotted the size vs. time in Excel and the growth rate is steady. At this rate, the database will be around 2.5 GB after a year.

Here are the biggest tables:

Table_Name Space_Used_MB Table_Row_Count

FileStatsHistory 19.36 27807

HealthReport 3.40 5

SchemaChangeLog 2.76 248

JobStatsHistory 1.33 3128

QueryHistory 0.25 357

CPUStatsHistory 0.23 6404

MemoryUsageHistory 0.21 367

PerfStatsHistory 0.14 1099

Looks like I could slow the growth rate by decreasing the sampling frequency of the file stats. The other thing I'll need to do is delete old data after a while; e.g. delete data over 1 year old.

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

Data purging is something we've talked about but have deferred on because we wanted to eventually incorporate some sort of analytics. It's a work in progress for sure 🙂

alchemistmatt Mr or Mrs. 500 Points: 525 More actions · Answer 7

alchemistmatt (4/18/2013)
Looks like I could slow the growth rate by decreasing the sampling frequency of the file stats. The other thing I'll need to do is delete old data after a while; e.g. delete data over 1 year old.

I changed the dba_CheckFiles job to run every 8 hours instead of every hour. In addition, I tweaked the Sql in rpt_HealthReport to properly compare the latest stats vs. the stats from 24 hours ago:

-- Find the FileStatsDateStamp that corresponds to 24 hours before the most recent entry in FileStatsHistory

-- Note that we use 1470 instead of 1440 to allow for the entry from 24 hours ago to be slightly more than 24 hours old

SELECT @MaxFileStatsDateStamp = MAX(FileStatsDateStamp) FROM [dba].dbo.FileStatsHistory

SELECT @MinFileStatsDateStamp = MIN(FileStatsDateStamp) FROM [dba].dbo.FileStatsHistory WHERE FileStatsDateStamp >= DateAdd(minute, -1470, @MaxFileStatsDateStamp)

IF @MinFileStatsDateStamp IS NOT NULL

BEGIN

-- Update the stats in #FILESTATS to reflect the change over the last 24 hours

-- (instead of the change since the Sql Server service last started)

--

...

END

gazzer SSC Eights! Points: 885 More actions · Answer 8

Hi guys, this an excellent project and one I'm sure we will utilize in our environment.

I am however having difficulty in getting one of the jobs to run.

Namely

LongRunningJobsAlert

I get the following error message

'Procedure usp_JobStats has no parameters and arguments were supplied'

Had a look at the SP but I don't see anything wrong with it.

Cheers

ShawnTherrien Default port Points: 1481 More actions · Answer 9

gazzer (4/19/2013)
I am however having difficulty in getting one of the jobs to run.
Namely
LongRunningJobsAlert
I get the following error message
'Procedure usp_JobStats has no parameters and arguments were supplied'
Had a look at the SP but I don't see anything wrong with it.
Cheers

In the job dba_LongRunningJobsAlert?

Here is my code in the sproc

EXEC [dbWarden].dbo.usp_LongRunningJobs

gkiss 73362 SSC Rookie Points: 28 More actions · Answer 10

I have made progress in generating non-NULL HTML.

I have found that by commenting out the following three IF blocks, I get non-NULL HTML and there is something to look at in the resulting email:

IF EXISTS .... #REPLINFO

IF EXISTS .... #PUBINFO

IF EXISTS .... #REPLSUB

I'm not sure yet why these are problematic, and why the HTML becomes NULL if they are allowed to run. I'm sure it has to do with the COALESCE as someone pointed out.

I would appreciate any thoughts, but at least this is useful already.

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

gkiss,

Thanks, I will take a closer look at that section.

I only ran replication on one of my test systems when I wrote that section, the rest of the test boxes don't have replication setup at all and I've never had an issue with those sections causing the blob to be NULL. Interesting...

AlexSQLForums SSChampion Points: 14277 More actions · Answer 12

alchemistmatt (4/18/2013)
Matt
By how much does your dba database grow daily?
Alex S
Good question; I hadn't checked yet. Turns out I can use the FileStatsHistory table data to answer your question: dba.mdf is growing at 7.2 MB per day on a system with 148 databases. I plotted the size vs. time in Excel and the growth rate is steady. At this rate, the database will be around 2.5 GB after a year.
Here are the biggest tables:
Table_Name Space_Used_MB Table_Row_Count
FileStatsHistory 19.36 27807
HealthReport 3.40 5
SchemaChangeLog 2.76 248
JobStatsHistory 1.33 3128
QueryHistory 0.25 357
CPUStatsHistory 0.23 6404
MemoryUsageHistory 0.21 367
PerfStatsHistory 0.14 1099
Looks like I could slow the growth rate by decreasing the sampling frequency of the file stats. The other thing I'll need to do is delete old data after a while; e.g. delete data over 1 year old.

Thank You

Alex S

gazzer SSC Eights! Points: 885 More actions · Answer 13

Hi, thanks a great deal, that helped me out and it is now working.

I had originally set-up to use in another Database, but then went with the default, and forgot to change the code.

On another note, I set this up on our production server (both test/live SQL 2008 R2).

when I run on LIVE I get the following error messages.