April 16, 2013 at 7:39 pm
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
April 17, 2013 at 4:32 am
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?
April 17, 2013 at 11:41 am
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.
April 17, 2013 at 8:53 pm
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]"
April 18, 2013 at 1:38 pm
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.
April 18, 2013 at 4:59 pm
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 🙂
April 18, 2013 at 7:15 pm
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
April 19, 2013 at 1:42 am
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
April 19, 2013 at 9:42 am
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
April 19, 2013 at 11:09 am
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.
April 19, 2013 at 11:20 am
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...
April 19, 2013 at 1:33 pm
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
April 21, 2013 at 11:46 pm
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.
April 22, 2013 at 8:08 am
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
April 22, 2013 at 8:22 am
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