dstanger-666122 (10/25/2012)
Hi Steve,Regarding "I wrote that calculated data growth for all databases on an instance and then used that to calculate how many days would elapse before I ran out of space on the data drives." Would you be willing to share your solution? Just the query(s) would be ideal. I've spent hours trying to do this (data growth info) but can't get it how I want it.
Your editoral inspired me to continue working on solutions to scan SQL instances and report back to me using SSRS reports. For example, I have a report that emails me daily a list of databases on an instance with the dates of the last FULL, DIFF, and LOG backups. The report will show stuff in Red based on our business rules and expectations for SQL Server backups. For example, if a FULL backup hasn't been done within 7 days, the database name is shown in Red. Just the other day on one servers, all the dates for the DIFF and LOG backups of each database was in Red, which clearly indicated a problem. Turns out our sys admin patched the server, rebooted it, but the SQL Agent wasn't restarted so backup jobs hadn't run for a day.
Also, on my report is information form Ola Hallengren's CommandLog table. So, daily I receive a report via e-mail for each server that saves me time from manually going to each server to inspect stuff.
Thanks again!
Thanks.
On the solution, I was looking for this recently. I think I lost the code in a few job changes, and I've been working on something for SQL Monitor, but haven't devoted a lot of time to testing. Really it means calculating growth across days to get a backup size, doubling it (I ususally want 2 days) and then dividing by free space. I think I used to do this in a few stages as a proc, not really a nice query, but I'm trying to get a good query that runs in one step for the Red Gate tool.