Grow Database File in SQL

Share on:

I use both SQL Express 2005 on a server and SQL 2008 on another server. The following script is nice to manually grow a file. By default, SQL auto-grows database files by 10% when it runs low on space. In most cases this is fine, but I’m a perfectionist and hate receiving errors about low DB Space in my SCE (System Center Essentials) console.

use OperationsManager

declare @dbname nvarchar(300)

declare @dbsize dec(15)

declare @UsedSpace dec(15)

declare @FreeProc int

set @dbname=REPLACE(db_name(),””,”””)

EXEC(‘DBCC UPDATEUSAGE (”’[email protected]+”’)’)

set @dbname=QUOTENAME(@dbname)

select @dbsize = sum(convert(dec(15),size))

from dbo.sysfiles

where (status & 64 = 0)

select @UsedSpace=sum(convert(dec(15),reserved))

from sysindexes where indid in (0, 1, 255)

select @dbsize,@UsedSpace

select @FreeProc=((@[email protected])*100)/@dbsize

–If free under 20%, grow database by 20%

–The first datafile is used

if @FreeProc<20 begin declare @filename nvarchar(300) declare @bytesperpage dec(15) declare @pagesperMB dec(15) declare @newsize dec(15) select @filename=(select top 1 [name] from dbo.sysfiles where (status & 64 = 0) order by groupid,fileid) set @filename=REPLACE(rtrim(@filename),'''','''''') select @bytesperpage = [low] from master.dbo.spt_values where number = 1 and type = ‘E’ set @pagesperMB = 1048576 / @bytesperpage set @newsize=(@dbsize*12/10)/@pagesperMB exec(‘ALTER DATABASE ‘[email protected]+’ MODIFY FILE (NAME = N'‘‘[email protected]+’'', SIZE = ‘[email protected]+’)') end