Manually grow database

Posted on 2003-03-20
Medium Priority
Last Modified: 2007-12-19
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.

Question by:acampoma
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8176136
LVL 13

Accepted Solution

ispaleny earned 1000 total points
ID: 8177871
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 !

Author Comment

ID: 8181133
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!

LVL 13

Expert Comment

ID: 8181372
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question