September 22, 2009 at 9:21 am
Steve Jones - Editor (9/22/2009)
There are tools like Quest's Spotlight (or Foglight) and maybe Patrol from BMC, that will monitor this.The way I used to do it was track backup sizes from a script and then compare them over time. The backup is a good guess of your data size and can tell you how quickly it's growing.
That's what I do too. You can run this to look at the size of your full backups .... assuming you're backing up your databases ! If not, then get ready to join the "I lost all my data" club.
SELECT a.server_name as 'Server',
a.database_name as 'Database',
convert(varchar(25),a.backup_start_date,100) AS 'Start Date',
convert(varchar(25),a.backup_finish_date,100) AS 'Finish Date',
DATENAME(weekday, a.backup_finish_date) AS 'Day' ,
datediff(minute, a.backup_start_date, a.backup_finish_date) as 'Mins' ,
cast(cast(datediff(minute, a.backup_start_date, a.backup_finish_date)
as decimal (8,3))/60 as decimal (8,1)) as 'Hours' ,
case
when datediff(minute, a.backup_start_date, a.backup_finish_date) > 0
then cast(ceiling(a.backup_size /1048576) / datediff(minute, a.backup_start_date, a.backup_finish_date) as decimal (8,1))
else 0
end as 'Meg/Min',
ceiling(a.backup_size /1048576) as 'Size Meg' ,--cast((a.backup_size /1048576) as decimal (9,2)) as 'Size Meg' ,
cast((a.backup_size /1073741824) as decimal (9,2)) as 'Gig', -- div by 1073741824 to get gig
a.user_name,a.backup_size as 'Raw Size'
FROM msdb.dbo.backupset a
join msdb.dbo.backupset b on a.server_name = b.server_name and a.database_name = b.database_name
WHERE a.type = 'D' and b.type = 'D' /*D=Full*/ AND a.backup_start_date > '2009-06-01'
group by a.server_name, a.database_name, a.backup_start_date, a.backup_finish_date, a.backup_size, a.user_name
order by a.server_name, a.database_name, a.backup_start_date desc
September 23, 2009 at 5:44 am
Thank you for this code. I have another question. One of my db, contains this:
DB:
MB Day1 Day2
Data 732805
Logs 837837
This db is growing, is it ok that logs are bigger than data? If not how can I fix it.
Thank you
September 23, 2009 at 7:47 am
What are the log backup sizes? Please use commas and format it a little better.
The log could be bigger than the data, but usually isn't. Likely you had some event that caused the log to grow too large, or didn't have log backups running. If we know the rough backup sizes and schedule, we can help you size the log.
September 28, 2009 at 9:43 am
I agree, look at the size of your backups in the BACKUP directory. Do you have a separate backup file for each time you backup? If so, do you have a process for deleting the old backups?
Steve
September 28, 2009 at 9:59 am
Separate backup file for each backup.
The maintenance plans have a maintenance cleanup that does this well. We used to script something, but the maint plan item does it better. You can create a plan with just the cleanup
September 29, 2009 at 7:51 am
Buy FolderInfo and use it to quickly spot space consumers on the drive of concern. Very useful utility I have been using for a LONG time.
Also, instead of going back and forth for days on a forum, just get a professional to connect up to your system for an hour or two and you will probably be AMAZED at what they may find to help you out. Given your lack of SQL Server knowledge you really need someone like that on retainer anyway. There are many ways you can be zapped if you use sql server without a knowledgable DBA on staff.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 29, 2009 at 8:11 am
TheSQLGuru (9/29/2009)
Buy FolderInfo and use it to quickly spot space consumers on the drive of concern. Very useful utility I have been using for a LONG time.Also, instead of going back and forth for days on a forum, just get a professional to connect up to your system for an hour or two and you will probably be AMAZED at what they may find to help you out. Given your lack of SQL Server knowledge you really need someone like that on retainer anyway. There are many ways you can be zapped if you use sql server without a knowledgable DBA on staff.
Folderinfo is available as a free download.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 29, 2009 at 9:05 am
Free download for evaluation doesn't make it a free product. If people would actually pay for products they use I think we would all benefit because developers would make more money and be more likely to come up with new/better software. If you use something - pay for it! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 29, 2009 at 9:07 am
I'd agree. If you use it, $25 isn't much, and it helps a fellow programmer that's written something.
September 29, 2009 at 9:48 am
TheSQLGuru (9/29/2009)
Free download for evaluation doesn't make it a free product. If people would actually pay for products they use I think we would all benefit because developers would make more money and be more likely to come up with new/better software. If you use something - pay for it! 😎
Typical quote from a MVP, for this troubleshooting case I meant, not just in general. If you apply your quote to microsoft software, we should have perfect products considering how much they cost. ;-), how does open source software work then, doesnt your statement contradict the whole philosphy behind open source.
And to be totally frank Steve, any purchase can be too much, especially for a one time use only. $25 might not sounds like much but it is still money. And if you think different, it says a lot about the budgets you have had to play with. There have been enough posts on these forums asking for free tools because they are not allowed to buy any tools to help them with their job.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 29, 2009 at 10:11 am
Silverfox (9/29/2009)
TheSQLGuru (9/29/2009)
Free download for evaluation doesn't make it a free product. If people would actually pay for products they use I think we would all benefit because developers would make more money and be more likely to come up with new/better software. If you use something - pay for it! 😎Typical quote from a MVP, for this troubleshooting case I meant, not just in general. If you apply your quote to microsoft software, we should have perfect products considering how much they cost. ;-), how does open source software work then, doesnt your statement contradict the whole philosphy behind open source.
And to be totally frank Steve, any purchase can be too much, especially for a one time use only. $25 might not sounds like much but it is still money. And if you think different, it says a lot about the budgets you have had to play with. There have been enough posts on these forums asking for free tools because they are not allowed to buy any tools to help them with their job.
Shew - not sure where the personal attack(s) is coming from, but clearly I have touched a nerve here. A few follow-ups:
1) No software of any complexity will ever be perfect, regardless of cost. I personally believe that Microsoft products are often less costly than their equivalent FOR FEE counterparts. Good example given that this is a SQL Server forum is Oracle RDBMS.
2) If software has no cost (unsupported open source) then it is pointless for your statement about "contradicting ...". Obviously you cannot pay for something that has no cost and my statement was directly aimed at a piece of FOR FEE software.
3) If you don't have money to spend, then DON'T use an EVALUATION product to do PRODUCTION WORK. Live with your own limitations or realize that you need to find the resources to do your job. I note that that resource often IS a free product. http://foldersizebrowser.codeplex.com/ for example. And if you cannot do your job without violating licensing agreements or pirating software and you cannot find or build your own substitute and you cannot convince your employer to pony up the dollars then that is unfortunate. Still doesn't make it right to do the former however.
4) Your comment to Steve about his budgets is inappropriate as well. I also doubt you know jack about his budgets (or if he even has one to spend).
5) spending some small amount of money to avoid server outages, save many man-hours of effort (which IS a cost, btw) is often VERY appropriate with HUGE ROI, despite the fact that "$25 is still money".
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 29, 2009 at 10:13 am
September 29, 2009 at 10:22 am
Open Source != free. It means that someone is allowing you to see how the code works.
Lots of these little utilities are written by someone that does it in their spare time and want to be paid for their work. How would you feel if you asked for payment for your services and someone said they didn't want to pay? If you use the tool for production use, then pay for it.
You don't have to pay, but then you shouldn't use the tool. Someone selling their product, and giving you an eval, doesn't mean that it's your right to just it without paying. It means you try it. If it works, you should pay for it.
I've bought software out of my own pocket when it does the job. I have various utilities that my boss won't pay for, but I like them, they work, so I pay the author.
September 29, 2009 at 10:23 am
TheSQLGuru (9/29/2009)
Silverfox (9/29/2009)
TheSQLGuru (9/29/2009)
Free download for evaluation doesn't make it a free product. If people would actually pay for products they use I think we would all benefit because developers would make more money and be more likely to come up with new/better software. If you use something - pay for it! 😎Typical quote from a MVP, for this troubleshooting case I meant, not just in general. If you apply your quote to microsoft software, we should have perfect products considering how much they cost. ;-), how does open source software work then, doesnt your statement contradict the whole philosphy behind open source.
And to be totally frank Steve, any purchase can be too much, especially for a one time use only. $25 might not sounds like much but it is still money. And if you think different, it says a lot about the budgets you have had to play with. There have been enough posts on these forums asking for free tools because they are not allowed to buy any tools to help them with their job.
Shew - not sure where the personal attack(s) is coming from, but clearly I have touched a nerve here. A few follow-ups:
1) No software of any complexity will ever be perfect, regardless of cost. I personally believe that Microsoft products are often less costly than their equivalent FOR FEE counterparts. Good example given that this is a SQL Server forum is Oracle RDBMS.
2) If software has no cost (unsupported open source) then it is pointless for your statement about "contradicting ...". Obviously you cannot pay for something that has no cost and my statement was directly aimed at a piece of FOR FEE software.
3) If you don't have money to spend, then DON'T use an EVALUATION product to do PRODUCTION WORK. Live with your own limitations or realize that you need to find the resources to do your job. I note that that resource often IS a free product. http://foldersizebrowser.codeplex.com/ for example. And if you cannot do your job without violating licensing agreements or pirating software and you cannot find or build your own substitute and you cannot convince your employer to pony up the dollars then that is unfortunate. Still doesn't make it right to do the former however.
4) Your comment to Steve about his budgets is inappropriate as well. I also doubt you know jack about his budgets (or if he even has one to spend).
5) spending some small amount of money to avoid server outages, save many man-hours of effort (which IS a cost, btw) is often VERY appropriate with HUGE ROI, despite the fact that "$25 is still money".
LOL, not a personal attack, so apologies for that, I still stand by my comments, there are a lot of people who use evaluation products on production servers, not advisable but it happens. noone lives in a perfect world. Regarding Steve, he doesnt need anyone to defend him, he knows from previous topics how opinionated I can be on some subjects and he knows it isnt personal. regarding the $25 I have worked at organisations where even buying a $10 utility is not allowed, regardless of the benefit. As much as we would like to think, just because it can be used to help the business, it doesnt mean that any software would be purchased regardless of the cost.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 29, 2009 at 10:27 am
Steve Jones - Editor (9/29/2009)
Open Source != free. It means that someone is allowing you to see how the code works.Lots of these little utilities are written by someone that does it in their spare time and want to be paid for their work. How would you feel if you asked for payment for your services and someone said they didn't want to pay? If you use the tool for production use, then pay for it.
You don't have to pay, but then you shouldn't use the tool. Someone selling their product, and giving you an eval, doesn't mean that it's your right to just it without paying. It means you try it. If it works, you should pay for it.
I've bought software out of my own pocket when it does the job. I have various utilities that my boss won't pay for, but I like them, they work, so I pay the author.
I appreciate the points you are making nuff said. although speaking as a former software developer, just the fact that someone took the time to download and use the product and say its a great product, thanks for that. meant more than any money could.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply