I have the opportunity to plan and design our database    
   size settings.  Can I ask for comments?   
      
   We have several SQL Server 2005 virtual servers -   
   with migration to 2012 some time soon - each of which    
   holds many databases, most of which represent one site    
   for one customer.  The design of these databases evolves    
   from time to time, so, database files may need to grow    
   automatically.   
      
   We are also considering polling databases overnight    
   and executing growth events, as an alternative to    
   letting this happen during a user's transaction.   
      
   Monitoring of database size and disk free space    
   probably will be implemented independently of   
   that.   
      
   I'm told that disk storage for each virtual server    
   consists of a single volume of SAN-type storage,    
   but I'm hoping that there is a layer of data striping    
   below that - I'm waiting to find out.  I believe that    
   that will allow me to create OS file fragmentation    
   in the short term without much of a performance penalty.   
      
   Simple recovery will be used.  I think that disk space    
   isn't particularly scarce.   
      
   I suppose that the first step is to set appropriate    
   long term sizes for data files and transaction log files,   
   but I may skip that, except in special cases.   
      
   I plan to use the standard database auto-growth controls    
   as the basis of growth actions triggered overnight;   
   specifically, if the next auto-growth will add more    
   space than the current free space in the file,    
   then I will execute an equivalent file growth now.   
   This will mean that my files usually have a generous    
   amount of free space.  I don't know if there are    
   permission obstacles to doing this, particularly with   
   "dynamic Transact-SQL".  I think that there may be,   
   which will make it more complicated to do - a client   
   programming job.   
      
   Data file growth (MDF) I intend to set to a fixed size    
   in MB that corresponds approximately to any data striping    
   provision and to estimated need.  This may mean that    
   large databases undergo more growth events and the data    
   file becomes fragmented at OS filesystem level, but I think   
   that on my servers, this can be tolerated and/or repaired.   
      
   Transaction log growth (LDF) I am considering setting to    
   20 percent of file size.  I think this is relatively    
   "generous" and may produce unnecessarily large files,    
   but with relatively fewer "virtual log files" inside    
   the transaction log.   
      
   Maximum file size may be set to twice the current size,   
   and may be reset by the overnight poll of file sizes,   
   i.e. to twice the file size now.  I expect this will    
   be sufficient to let most foreseeable processes run   
   without allowing a rogue program to add unlimited    
   data in a database an¢ fill the disk.  Having said    
   that, for some large databases, this calculation    
   probably also needs to take actual disk space into    
   account, in case the file maximum needs to be set lower.   
      
   I am not planning to use more than one MDF or more than   
   one LDF in most databases, unless there is a good reason    
   for it.  If such a configuration is used, then my default   
   plan would be to manage each file size using the same    
   calculations.   
      
   Maybe someone has already done the work for something   
   like this scheme?   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)