April 12, 2013 at 9:35 am
gsc_dba (4/12/2013)
Awesome script, I had most of these in separate scripts and now have a combined solution - thanks! 🙂I did get one error, and fixed it by with this:
IF OBJECT_ID('tempdb..#tempdates') IS NOT NULL
BEGIN
DROP TABLE #TEMPDATES
END
I think one of the conditional checks did not create the table so wasnt able to drop it.
Thank you, we have an update that is about to be released, I'll incorporate this fix.
mipe10 (4/12/2013)
Hi,Great tool!
I would just like to mentione that it will not work on sql server 2012 since some column names have changed in for example sys.dm_os_sys_info. There are other places as well.
I ran the setup on SQL 2012 + SP1
Regards
Thank you very much. We have a 2012 sandbox so we can look into the column changes.
marcel.eppel (4/12/2013)
To get the total volume space of a HDD you might find this link useful:http://www.sqlservercentral.com/articles/powershell/68011/
It can be done using PowerShell to collect the data in in SQLAgentJob.
The Problem with other solutions found here on the platform is that high security permissions on the server for running extended commands are needed to run.
That's a great article. I really like Powershell but we've tried to keep dbWarden completely SQL-based for ease of use.
Jim Murphy (4/12/2013)
I think this is a great idea and I am glad you are sharing with the community. I also really enjoy the integration and using other free and very robust scripts, like from Adam and Ola.I may have a few interesting scripts that I can send your way (over time as I get a chance) to capture some other interesting things as well.
Oh - look at the Glenn Berry DMV scripts! Free and juicy.
Thanks for your hard work and thought!
Thank you, I look forward to checking them out! We'll have to check out Glenn's DMV scripts.
Bert De Haes (4/12/2013)
Awesome script, thanks a lot.I wanted to try this on my server, but failed because my server uses collation 'Latin1_General_bin' ( case sensitive => also for object names ).
I changed the script and now it seems to work.
Doh! That's something I never considered. I'd be interested to see the changes you made! Thanks!
April 12, 2013 at 1:03 pm
The script doesn't work for SQL Server 2012. Especially the log area DBCC LOGINFO and memory
--SQL Server 2008 version
--SELECT physical_memory_in_bytes/1048576.0 as [SystemPhysicalMemoryMB],
--virtual_memory_in_bytes/1048576.0 as [SystemVirtualMemoryMB],
--(bpool_committed*8)/1024.0 as [BufferPoolCommitMB],
--(bpool_commit_target*8)/1024.0 as [BufferPoolCommitTgtMB]
--FROM sys.dm_os_sys_info
--SQL Server 2012 version
SELECT
physical_memory_kb/1024.0 as [SystemPhysicalMemoryMB],
virtual_memory_kb/1024.0 as [SystemVirtualMemoryMB],
(committed_kb)/1024.0 as [BufferPoolCommitMB],
(committed_target_kb)/1024.0 as [BufferPoolCommitTgtMB]
FROM sys.dm_os_sys_info
April 12, 2013 at 1:09 pm
I am still trying to make the script MS SQL 2012 compatible.
How can I send you the adapted script ?
April 12, 2013 at 2:12 pm
Thank you for making this script available. I had just recently started looking at collecting baseline stats for monitoring and had been slowly working at piecing something similar together. This is getting my monitoring off the ground at this point.
Thanks,
Kelly
April 12, 2013 at 3:24 pm
Yet more "Thanks!"
Is there a way you could incorporate monitoring of additional SQL-oriented services? We had an issue with Red Gate's SQL Backup Service ceasing when the SQL Service was stopped and, having no alerting, were unaware of its demise. Would be great to see as an add-in...
Target is SQL2005 so this is an extremely timely article, especially as an LDF just maxed out the disk and we had no idea over the weekend that a bad disk on another server was causing SQL Backup's network file transfers to hang every time....
Could you add, "Must have master..sp_whoisactive installed from: http://sqlblog.com/files/folders/beta/entry42453.aspx" to the prerequisite verbiage?
Is there reason why you use 3-part naming immediately after the MANY "Use [dba]" statements? It takes about 5 minutes to edit the entire script to change the database name everywhere. One cannot use a "Replace All" because the job names and operators feature DBA or dba. Using 2-part naming after the USE statements would obviate about half the editing... Just a thought... 🙂
April 12, 2013 at 3:50 pm
SAinCA (4/12/2013)
Yet more "Thanks!"Is there a way you could incorporate monitoring of additional SQL-oriented services? We had an issue with Red Gate's SQL Backup Service ceasing when the SQL Service was stopped and, having no alerting, were unaware of its demise. Would be great to see as an add-in...
Target is SQL2005 so this is an extremely timely article, especially as an LDF just maxed out the disk and we had no idea over the weekend that a bad disk on another server was causing SQL Backup's network file transfers to hang every time....
I've traditionally created a SQL Job that has a schedule type of "Start automatically when SQL Server Agent starts" and have the job execute msdb.dbo.sp_send_dbmail to email me when the SQL Service restarts. That doesn't help though if the server is completely down. 🙂
April 12, 2013 at 3:52 pm
SAinCA (4/12/2013)
Could you add, "Must have master..sp_whoisactive installed from: http://sqlblog.com/files/folders/beta/entry42453.aspx" to the prerequisite verbiage?Is there reason why you use 3-part naming immediately after the MANY "Use [dba]" statements? It takes about 5 minutes to edit the entire script to change the database name everywhere. One cannot use a "Replace All" because the job names and operators feature DBA or dba. Using 2-part naming after the USE statements would obviate about half the editing... Just a thought... 🙂
We've used the 3 part naming convention to easily search/replace by looking up "[dba]" the brackets seperate it from the dba names in the operators and jobs.
Hope that helps.
April 12, 2013 at 3:55 pm
We made a few modifications based on feedback. The following modifications were made:
- Changed Health Report to only show last 24 hours worth of File Stats instead of since server restart
- Modified usp_MemoryUsageStats, usp_FileStats and rpt_HealthReport to be SQL Server 2012 compatible.
- Fixed bug in rpt_HealthReport - Changed #TEMPDATES from SELECT INTO - > CREATE, INSERT INTO
The updated file is available on our SourceForge page. The file still being hosted on SQLServerCentral is the old copy. I'm asking that it is removed to avoid confusion...
Thanks and let us know how the changes work for all the SQL 2012 users out there!
April 12, 2013 at 4:18 pm
Hi, I ran the SQL and created dba all right but when I ran HealthReport I got an error:
Cannot drop the table #TEMDATES because it does not exist or you do not have permission.
I would appreciate help getting past this.
Thanks,
Gabor
PS. Sorry I didn't notice that this had been discussed. I'm a newbie. I can't delete the reply but I can edit, so ... sorry
April 12, 2013 at 4:23 pm
Hi, I ran the SQL and created dba all right but when I ran HealthReport I got an error:
Cannot drop the table #TEMPDATES because it does not exist or you do not have permission.
I would appreciate help getting past this.
Thanks,
Gabor
PS. Sorry I didn't notice that this had been discussed. I'm a newbie. I can't delete the reply but I can edit, so ... sorry
April 12, 2013 at 4:57 pm
Hi Gabor,
Yes, that issue has been addressed and there is an updated file on the Sourceforge page.
I'm also logging issues in the Tickets section on that site to help track bugs that people find.
April 12, 2013 at 5:38 pm
michaelrounds (4/12/2013)
SAinCA (4/12/2013)
Yet more "Thanks!"Is there a way you could incorporate monitoring of additional SQL-oriented services? We had an issue with Red Gate's SQL Backup Service ceasing when the SQL Service was stopped and, having no alerting, were unaware of its demise. Would be great to see as an add-in...
Target is SQL2005 so this is an extremely timely article, especially as an LDF just maxed out the disk and we had no idea over the weekend that a bad disk on another server was causing SQL Backup's network file transfers to hang every time....
I've traditionally created a SQL Job that has a schedule type of "Start automatically when SQL Server Agent starts" and have the job execute msdb.dbo.sp_send_dbmail to email me when the SQL Service restarts. That doesn't help though if the server is completely down. 🙂
I was thinking in terms of looking for the Red Gate SQL Backup Service, not SQL Agent.
Realized that the last section of the Health Check email shows whether backups are being taken... DUH!
April 12, 2013 at 5:42 pm
michaelrounds (4/12/2013)
SAinCA (4/12/2013)
Could you add, "Must have master..sp_whoisactive installed from: http://sqlblog.com/files/folders/beta/entry42453.aspx" to the prerequisite verbiage?Is there reason why you use 3-part naming immediately after the MANY "Use [dba]" statements? It takes about 5 minutes to edit the entire script to change the database name everywhere. One cannot use a "Replace All" because the job names and operators feature DBA or dba. Using 2-part naming after the USE statements would obviate about half the editing... Just a thought... 🙂
Sp_whoisactive is included in the script, at the bottom. It will create it in the Master DB.
We've used the 3 part naming convention to easily search/replace by looking up "[dba]" the brackets seperate it from the dba names in the operators and jobs.
Hope that helps.
Any possibility that the sp_whoisactive could be added before sp's dependent upon it are added - would do away with the message I read and responded to by jumping over to Adam's blog...
Also, for anyone needing a good DBMail setup script, search for DBMail_Setup.sql and you'll get what you need for 2005 at least. Saves having to use the nasty DBMail dialogs...
April 12, 2013 at 7:22 pm
This script has a lot of potential to become one of the best free monitoring tools. The output HTML report looks so professional. Thanks a lot for putting in so much effort and sharing with the community.:-)
However I ran into some troubles on a server that hosted 12 TB database. Some INT data type columns cannot handle the large values. I am working my way through it to make it compatible with my database.:cool:
Where can I find the SQL 2012 compatible script?
April 12, 2013 at 7:37 pm
arvindravish (4/12/2013)
This script has a lot of potential to become one of the best free monitoring tools. The output HTML report looks so professional. Thanks a lot for putting in so much effort and sharing with the community.:-)However I ran into some troubles on a server that hosted 12 TB database. Some INT data type columns cannot handle the large values. I am working my way through it to make it compatible with my database.:cool:
Where can I find the SQL 2012 compatible script?
Thank you so much! The newest version is up on the Sourceforge page, https://sourceforge.net/projects/dbwarden/ .
Viewing 15 posts - 16 through 30 (of 186 total)
You must be logged in to reply to this topic. Login to reply