Grow Database File in SQL

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 (”’+@dbname+”’)’)
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=((@dbsize-@UsedSpace)*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 '+@dbname+' MODIFY FILE (NAME = N'''+@filename+''', SIZE = '+@newsize+')') end

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: