Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1783

Planning database size management

Show all headers | View raw

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?

Back to comp.databases.ms-sqlserver | Previous | Next | Find similar

Thread

Planning database size management rja.carnegie@gmail.com - 2014-07-24 04:36 -0700

csiph-web