Changing when database is extended 1

[mrdenny]

Does anyone know of a way to change when the database extends it self. I know that when a database fills it will extend it self. Is there a way to tell the SQL Server that when the database only has 100 Megs free to extend the database? One of our programmers insists on extending it manually, instead of letting the SQL Server handle it.

Denny Cherry

 

[john230873]

Hope this helps, play with it to change the name of the database and amount to change by


SET nocount on
Declare @SQLText varchar(2000)

Declare Filecur Cursor for
select
sf.name,
sf.Filename,
sf.Size *8,
case
when MaxSize = -1 then -1
else sf.MaxSize *8
end
from
sysfiles sf

Declare @name nchar(128), --Logical name of the file.
@filename nchar(260), --Name of the physical device, including the full path of the file.,
@size int, --in kbytes
@maxsize int -- in kbytes -1 indicates unlimited


Open Filecur
FETCH NEXT FROM Filecur INTO @name,@filename,@size,@maxsize
WHILE (@@fetch_status <> -1)
BEGIN
if @Maxsize = -1
BEGIN
if @MaxSize - @Size < (1024 * 10) --amount to check for
Begin
Set @Size = @Size + (1024 *10) --amount of kbytes to increase
Set @SQLTEXT = 'alter Database <YOUR DATABASE> '+
'modify file
(name ='+ @name +',
size = '+ convert (varchar(10),@size) +'kb)'
exec (@SQLTEXT)
print @SQLTEXT
End
END
FETCH NEXT FROM Filecur INTO @name,@filename,@size,@maxsize
END
Close Filecur
deallocate Filecur