June 30, 2010 at 5:17 pm
hmmm. Let me think. I wish Sean (the author) was onlilne right now. But he is good about getting back to you within a day.
I'm going to look at the previous replies to this...You too.
Let's see what we come up with.
June 30, 2010 at 5:22 pm
It sounds like you are set up to send mail from SQL Server, but can you confirm this? And if yes, do you have any other processes that currently send mail from SQL Server?
June 30, 2010 at 5:53 pm
My SQL server database mail is not set up
June 30, 2010 at 6:15 pm
The report is intended to compile SQL Server information and then send the results to a recipient via email. You will need to have mail functionality turned on and configured. There's a lot of great resources online to help you do this:
http://www.google.ca/search?q=how+to+set+up+sql+server+2005+mail
Let me know if you have question or feel I can help more.
October 5, 2010 at 6:10 am
I've tried to run this on my 2008 machines that have Database Mail set up and running successfully with job notifications, but I don't receive this sproc's email.
October 5, 2010 at 4:12 pm
It's pointing to the correct db mail profile and you changed the email address in the proc, correct?
October 5, 2010 at 4:22 pm
Yes. I believe I figured out what the problem was. It just so happens that the 2008 servers are larger servers and therefore have more databases, indexes, etc. After confirming in the system views that the mail indeed showed "sent" I suspected it had something to do with the data itself so I changed the "body" to print "test" and it went through fine. I suspected that it may have been the size of the data in the @vBody variable so I commented out the "unused indexes" section and ran it again and it went through fine. I then tried changing it to send the output in an attachment with the parameter to allow for the longer length fields and it indeed erred out with a size limit. So, I believe the mystery is solved.
October 5, 2010 at 6:10 pm
Good news. Thanks for sharing the solution. 🙂
October 11, 2010 at 8:05 am
Sean, love the report (but for me it doesn't produce anything for the scheduled SQL jobs recently run?).
You seem to have a list of improvements/additions from the various respondees, so any idea when the revamped version is going to arrive?
More power to the efforts put in by the likes of yourself, as sharing the 'tricks' of the trade all helps make our lives a lot easier.
Thanks.
October 15, 2010 at 9:34 am
Would it be possible to run this script from a "central server" and using Linked Servers collect data from remote servers?
SQL Padre
aka Robert M Bishop
"Do or do not, there is no try" -- Yoda
October 16, 2010 at 10:09 am
Unfortunately, it will likely be a while from now. I've recently changed jobs and on top of that I am expecting my first child in < 1 month. So, things are busy! But it's still on my radar, just no ETA. For the recent jobs run, you *may* have a process which clears the jobs out of the history tables fairly frequently (I had the same problem on one server I was working with... someone had setup a cleanup process to run every 5 minutes!). You may want to query these tables separately to see what they contain. Let me know if you need help, and thanks for the feedback!
October 16, 2010 at 10:16 am
For sure. You would need to have all of your linked servers set up correctly, and then from there you could add an input parameter to the proc which would pass the linked server name and append it to the SQL code (some adjustments would need to be made to the code). Or, you could query all of the linked server names from the system tables and loop through, producing a report for each (again, modifications to the code would be needed). Let me know if this all makes sense. 🙂
November 4, 2010 at 4:16 am
Sean
It would be really useful if you could add the path and fiilename of the database and log files to the Database Size (Summary) / Distribution Stats table, e.g. F:\dataorthwind.mdf.
George25
November 4, 2010 at 4:24 am
Just read my last post. Not sure what happened to the text but what I wrote got garbled in the translation. What I meant was F:\data\dbname.mdf
November 4, 2010 at 3:21 pm
I was thinking of doing that, but I noticed an "issue" with a one-to-many relationship for tables that were partitioned to I opted to leave it out for the first version of the report. I think I will revisit it though. Thanks for the comment. 🙂
Viewing 15 posts - 46 through 60 (of 189 total)
You must be logged in to reply to this topic. Login to reply