Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
 Posted - 2010-06-17 : 12:12:30
|
| Note: Not a SQL Server 2008 specific topic; there just isn't a general administration forum.I wanted to start a discussion about management of space in database files to see how people handle it in different organizations. I'll go first.I have seen many posts from people saying that they try to proactively allocate space to databases, instead of letting them auto-grow. I have never done that in our environment because of the large number of servers and databases (hundreds of servers and many thousands of databases). It certainly could not be any process that requires more than a bare minimum of DBA time per server to manage.Instead, I try to set reasonable auto-growth sizes for data and log files for each database, and then monitor the total space used on each server drive with an automated tool to make sure we are not running out of space. I occasionally manually review the space usage on each server in detail, like just before we perform OS maintenance or when we have an issue with space. There are a few servers that we have to pay more frequent attention to, but it is a fairly small and managable number. This process seems to keep us running OK (with an occasional burp).Anyone have suggestions about a better way to handle this? Are there any great tools out there for handling this better (hopefully that don’t cost $500/server).How do you handle this in your environment?CODO ERGO SUM |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-06-17 : 17:54:25
|
| For critical servers/databases its a good idea to setup alerts to find out when the space is running out and manually expand during low activity/downtimes so the expansion doesnt happen at 9 AM on Monday morning.. the expansion process pauses all activity/connections.. this can be a few seconds to a few minutes.. not really an ideal experience for those users waiting in the queue. For non critical databases this may not be an issue but for critical ones, we have to be proactive.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-17 : 19:17:19
|
| Log filesizes daily, then draw a pretty graph showing when you predict disk space to be running low?I suppose you could set a "safety buffer limit" somewhat below total disk capacity and have all the databases on the server auto-shrink when that threshold is reached!!!I think Tara has a script for logging filesizes / available disk space?Reporting on files that have suddenly grown very large, or even just the rate of growth has changed would be helpful on an 80:20 basis so that a DBA specifically eye-balled databases if/when they that behave "abnormally"Perhaps we should have a SQL Team Open Source project to build the all-singing-all-dancing admin tool that MS should have bundled with the product by now ... |
|
|
|
|
|
|
Discussion: Management of Database File Space