Just increase database size? 1

[snootalope]

Hello

I'm fairly new to SQL so I appologize if this is a really simple deal, I just don't want to mess up a live server.

Our SQL 2005 server's db files are currently setup like so:

DATA - Initial Size> 5000MB Autogrowth: By 1MB, unrestrited growth
LOG - Initial Size> 3059MB Authogrowth: By 10%, restricted growth

A couple of weeks ago I started to receive "Disk Space" warnings from a program that monitors the database. So, I go into the Management Studio and hit the properties of the datase and see this:

Size: 8058 MB
Space Avaiable: 133 MB

That 133MB is causing the warnings. It's getting closer to 0 space available by the hour!!

How can I reclaim space or just increase the size available? The database itself is sitting on a 800 GB partition so there's plenty of space available.. I thought about doing a shrink, but I'm nervous how that would affect the info in the db.

What's my best approach here?! Thanks for any help!

 

[ptheriault]

First I would change the Log file growth to unrestricted. Then change it to grow by MB instead of %. You could start with 50MB chunks and check to see how many autogrow events in the day you get. Then adjust from there to limit your autogrow events.


What is the recovery model of your database.
If it is FULL are you backing up your Transaction Log?
What is the current size of both files.

Please run sp_helpdb <db_name> and post the results back here.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[snootalope]

Thanks for the quick response!

The recovery model, at the moment, is Full and we're doing an hourly backup of the Transaction Log via a maintenance plan. here's the output from the sp_helpdb:

Database,8058.94 MB,MM\admin,6,Oct 11 2006,Status=ONLINE, Updateability=READ_WRITE,
UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsAutoUpdateStatistics,80



Database,1,I:\SQL Data File\Database.mdf,PRIMARY,5120000 KB,Unlimited,1024 KB,data only
Database_log,2,D:\SQL Data Log\Database_log.ldf,NULL,3132352 KB,2147483648 KB,10%,log only

Are you thinking I should increase my DB file sizes by 50MB or increase the growth settings to 50MB?

 

[ptheriault]

For your data file
Database.mdf. Change your file growth to 50MB unrestricted.

For your log file
Database_Log.ldf change your file growth to 50MB unrestricted.

ARe both your .mdf and .ldf files on the same disk?
The 800GB partition?

Also, I would manually manage the autogrow events when the .mdf file get's close to an autogrow event. Autogrow events are IO intensive and can cause performance problems is they happen during peak operational hours.

What is your growth rate now? You may want to keep around 500MB free in your datafile. So tonight off hours you should add 350MB to your .mdf file.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[snootalope]

Both files are on the same RAID Array, but different partitions. The data file is on an 800GB partition and log file is on a 40GB.

So, when you say "add 350MB to my .mdf file" - I'd do that in the properties of the database on the File menu correct? And just change the Initial File Size to whatever it is plus 350MB right?

 

[ptheriault]

Correct. You can also do it with TSQL from a query window.

ALTER DATABASE Test1
MODIFY FILE
(SIZE = 8020MB)--or what ever
GO

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[snootalope]

Excellent, thanks for your help!

Oh, increase the size won't affect the data inside the database in any way will it?

 

[ptheriault]

not at all. it will simply add free space to the end of the file.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

I would recommend growing the database by well more than 50 Megs. How fast does the database grow in a day? I would grow the database by 250 Megs at a time. That way you get less auto grows per day or week while still having a small enough auto grow size that the files will grow very quickly.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com