• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2096
  • Last Modified:

Manually grow database

I have a database that has heavy traffic during the day and I don't want to Use Autogrow because this could potentially cause timeouts if SQL decides to do this during peak hours.
I would like to implement a nightly or weekly job to expand the database file by 10 percent of the spaceused.(Not 10% of the current size). I want to preemptively grow the database so that after each time I do so, I have 10% free space.

  • 2
1 Solution
Anthony PerkinsCommented:
use YourDatabase
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 10%, grow by 20%
--The first datafile is used
if @FreeProc<10 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+')')

Good luck !
acampomaAuthor Commented:
I have already done this myself.
It is very similar to yours.
I did not know about the UpdateUsage before sp_SpaceUsed.
Good tip.
Also, QUOTENAME (I have never used this before)
what does this do?
where (status & 64 = 0)

I pass in databasename and Filename as parameters. I see you do it automatically.
Excellent job!

declare @TimsColumn varchar(50)
set @TimsColumn='Tim''s column'
select @TimsColumn,QUOTENAME('Tim''s column',''''), QUOTENAME('Tim''s column','"') , QUOTENAME('Tim''s column')

I used it together with REPLACE, but it was redundant.

(status & 64 = 0) ...  bit 6 not set


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now