Clarity Database file Auto-Growth modification requirements.

In order to change the Auto-Growth of the file size from its current percentile state to a fixed amount, which in itself, should increase performance as the system does not need to calculate the amount that the increase should be.  The following SQL statement should be executed.

              Use master

              GO

              ALTER DATABASE               COMPANY_CPY

              MODIFY FILE

                            (NAME =               COMPANY_Data,

                            SIZE = 20MB)

              GO

In order for this to work appropriately the file size modification should also be applied against the Log file for the company as well.  These statements can be run during a DB Upgrade process.

Recommendation:

I recommend that we add this policy to increase by a fixed amount instead of a percentage.  However, as to make it a step in DB upgrade to part of the “Utilities” within the system is an option. 

Furthermore, by being a Utility option we can have the system calculate what it would like to set the increase size to based on a calculation using the file creation date; current date; start size; and current size.  This could then be displayed to the user and they could then change the increase size as they desire (at which point we could put a limitation of a minimum increase amount so the system would not take a performance hit caused by the user).

For the most part basic clients would not change the value.  However, IT managers may desire the amount of the increase to be higher so that regular increases are kept at a minimum.

Approx time required.

To do the step in DB Upgrade and the size fixed for all individuals would take about 1.5 hours (we must spend time (about 1 of the 1.5 hours) to decide what the amount should be)

To do the utility would likely take 0.5 days but would be the best option .

This is to execute the increase but also to add the new security fields and calculate the increase amounts.

.

Another capability, and one that may be beneficial when working on separating Year Data, is the ability to add an additional data file/log file.  This can be done by doing the following.

              USE master

              GO

              ALTER DATABSE                             COMPANY_CPY

              ADD FILE

              (

                            NAME = COMPANY_DATA#,

                            FILENAME = ‘location\name.{ndf,mdf, ldf}’,

                            SIZE = xMB,

                            MAXSIZE = {xMB, UNLIMITED},

                            FILEGROWTH = {xMB, x%}

              )

              GO

In the above example there are some { } brackets, these detail options separated by commas.

NB: I will look further into this later when time permits to see how exactly the system handles multiple database files and the storage of the data. But this could be very beneficial.

Reference Material:

Alter Database (T-SQL)

http://doc.ddart.net/mssql/sql70/aa-az_3.htm

Considerations for the “autogrow” and “autoshrink” settings in SQL Server

http://support.microsoft.com/kb/315512

Database sizing

http://technet.microsoft.com/en-us/library/bb418778(printer).aspx

SQL Server 2000 Operations Guide:  Capacity and Storage Management

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx?pf=true