Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


dbWarden - A Free SQL Server Monitoring Package


dbWarden - A Free SQL Server Monitoring Package

Author
Message
philcart
philcart
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3004 Visits: 1435
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.

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
AlexSQLForums
AlexSQLForums
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1481 Visits: 2249
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
    ShawnTherrien
    SSC Journeyman
    SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

    Group: General Forum Members
    Points: 87 Visits: 765
    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
    michaelrounds
    Valued Member
    Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

    Group: General Forum Members
    Points: 72 Visits: 479
    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
    alchemistmatt
    SSC Veteran
    SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)

    Group: General Forum Members
    Points: 291 Visits: 52
    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
    michaelrounds
    Valued Member
    Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

    Group: General Forum Members
    Points: 72 Visits: 479
    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 Smile
    alchemistmatt
    alchemistmatt
    SSC Veteran
    SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)

    Group: General Forum Members
    Points: 291 Visits: 52
    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
    gazzer
    SSC Rookie
    SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

    Group: General Forum Members
    Points: 45 Visits: 135
    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
    ShawnTherrien
    SSC Journeyman
    SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

    Group: General Forum Members
    Points: 87 Visits: 765
    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
    gkiss 73362
    Forum Newbie
    Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

    Group: General Forum Members
    Points: 6 Visits: 56
    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.
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search