Thursday, March 1, 2012

Auto Growth considerations

Auto growth is the event that occurs when a query needs additional space in the database to get executed

SQL Server provides two options if auto growth is enabled;
Auto growth in MB or in percentage.

When the current allocated size is filled, an auto growth event is triggered. During this event SQL collects the required space from OS and initializes it.

The auto growth will resource intensive, and hence recommended to give auto growth setting in such a way that it should not occur too often, at same time it should not grow very large that the auto growth itself takes much time to complete.


You can alter the file size and growth settings in database property window.



Alternately you can use the TSQL command to change database size and growth settings


ALTER DATABASE databaseName
MODIFY FILE (NAME=LogicalFilename,FILEGROWTH=XMB)


To get the logical name and other informations you can use the following command.


USE DatabaseName
SP_HELPFILE


or


SP_HELPDB DatabaseName

Now the question is how can we find out what is the optimum size for auto growth?
The answer is you need to first monitor your database growth and decide on this.

If you are using SQL 2005 and abaove and if you have default trace enabled, you can use following query to find this.




if
(select convert(int,value_in_use) from sys.configurations where name = 'default trace enabled' ) = 1
begin
declare
@curr_tracefilename varchar(500) ;
declare
@base_tracefilename varchar(500) ;
declare
@indx int ;
select
@curr_tracefilename = path from sys.traces where is_default = 1 ;
set
@curr_tracefilename = reverse(@curr_tracefilename);
select
@indx = patindex('%\%', @curr_tracefilename) ;
set
@curr_tracefilename = reverse(@curr_tracefilename) ;
set
@base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ;
select
(dense_rank() over (order by StartTime desc))%2 as l1,
convert(int, EventClass) as EventClass, DatabaseName,
Filename, (Duration/1000) as Duration, StartTime,
EndTime
, (IntegerData*8.0/1024) as ChangeInSize
from ::fn_trace_gettable( @base_tracefilename, default )
where
EventClass >= 92 and EventClass <= 95 and ServerName = @@servername --and DatabaseName = db_name()
order
by StartTime desc ;
end
else
select
-1 as l1, 0 as EventClass, 0 DatabaseName, 0 as Filename, 0 as Duration, 0 as StartTime, 0 as EndTime,0 as ChangeInSize




To determine space utilized inside the data files for each databases use





Create Table ##DB_usage
(
Database_name varchar(255),
Logical_name varchar(255),
PhysicalLocation Varchar(1000),
size float,
Used float)

exec sp_msforeachdb
'use ?; insert into ##DB_usage
select ''?'', name,physical_name,size*8/1024,FILEPROPERTY(name,''spaceused'')*8/1024 from sys.database_files'


select * from ##DB_usage
drop table ##DB_usage


For more details see.

No comments:

Post a Comment