Manually grow database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Manually grow database

Author

Topic

odopas
Starting Member

3 Posts

Posted - 2008-01-15 : 11:13:50

Hi, 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 periodic job to expand the database file by X percent of the spaceused.

How can I do that.

Tanks a lot.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-15 : 23:45:37

Run 'alter database' as sql job.

odopas
Starting Member

3 Posts

Posted - 2008-01-16 : 10:42:33

Thank you rmiao. But I don't know what you mean. I have the next script ...

use northwind

declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @newsize sql_variant
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)

select @logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)

select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'

select @pagesperMB = 1048576 / @bytesperpage

set @newsize = ltrim(str((@dbsize / @pagesperMB) * 1.10)+'MB')
select @newsize

ALTER DATABASE northwind
MODIFY FILE( Name = northwind_data, SIZE = @newsize)

but when I send the new value of the size I get the next error:

Line 27: Incorrect syntax near '@newsize'.

I think is because the parameter of size variable is not a char or numeric type.

However Thanks a lot for your help but I still having the problem.

P.D. Sorry about my english.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-16 : 23:55:31

Tried with dynamic sql like:

exec ('ALTER DATABASE northwind MODIFY FILE( Name = northwind_data, SIZE = ' + @newsize)

odopas
Starting Member

3 Posts

Posted - 2008-01-17 : 08:41:33

Thanks a lot rmiao your solution was excellent, I just have to add a parenthesis at the end and it works.

exec ('ALTER DATABASE northwind MODIFY FILE( Name = northwind, SIZE = ' + @newsize +')')

Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources