dbWarden - A Free SQL Server Monitoring Package

  • 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

  • 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
  • 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.

  • 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]"

  • 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.

  • 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 (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

  • 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

  • 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

  • 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.

  • 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...

  • 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
  • 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.

    Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137 Cannot insert the value NULL into column \'owner_sid\', table \'msdb.dbo.sysjobs\'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137 Cannot insert the value NULL into column \'owner_sid\', table \'msdb.dbo.sysjobs\'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137 Cannot insert the value NULL into column \'owner_sid\', table \'msdb.dbo.sysjobs\'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137 Cannot insert the value NULL into column \'owner_sid\', table \'msdb.dbo.sysjobs\'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137 Cannot insert the value NULL into column \'owner_sid\', table \'msdb.dbo.sysjobs\'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137 Cannot insert the value NULL into column \'owner_sid\', table \'msdb.dbo.sysjobs\'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137 Cannot insert the value NULL into column \'owner_sid\', table \'msdb.dbo.sysjobs\'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137 Cannot insert the value NULL into column \'owner_sid\', table \'msdb.dbo.sysjobs\'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    Any ideas?

    Thanks again.

  • All,

    Unfortunately you're going to have to remove the code for sp_whoisactive from this project.

    The license for Who is Active, which is in the header, is as follows:

    --

    Who is Active? is free to download and use for personal, educational, and internal

    corporate purposes, provided that this header is preserved. Redistribution or sale

    of Who is Active?, in whole or in part, is prohibited without the author's express

    written consent.

    --

    I did not give my consent, and do not give my consent, for inclusion of Who is Active in this project's source files. If you'd like to link to Who is Active for download by your users you're welcome to, but you may not redistribute it.

    Thanks,

    Adam Machanic

    --
    Adam Machanic
    whoisactive

  • Our apologies. It's been removed from the script as of now. I will get a new version released today that doesn't utilize your procedure to gather query data.

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

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