Urgent - Need to increase database size.

Question

Post reply

Urgent - Need to increase database size.

lucky-80472

SSCertifiable

Points: 7793
More actions
November 8, 2005 at 9:45 am

#89888

Hello Experts,
I'm running out of disk space for a database with the following information:
Size of database : 542214.88 MB
Space available : 58269.95 MB
Data File : 531200 MB (44713.38 MB used, 84086.62 MB free)
Log File : 11014.99 MB (129.19 MB used, 10885.8 MB free)
Hard Disk : 679 GB (109 GB free)
The .mdf file is showing as 548 GB in the explorer.
Is there a way to increase the database by another 30 gig. If so, let me know the process..
Thanks in advance.

Lucky

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Adam Wilbur SSCertifiable Points: 7001 More actions · Answer 1

Lookup alter database in BOL

A.J.
DBA with an attitude

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 2

Hello Enthusiast,

Thanks for the reply. Can you be more specific with the command to be executed?

alter database

name = 'logical name',

filename = 'path of physical filename',

size = ????

maxsize = ????

filegrowth = ???

I've checked up in EM->Properties and the space allocated is 531200 MB and Auto grow is checked with filegrowth of 30%.

Thanks in advance.

Lucky

Adam Wilbur SSCertifiable Points: 7001 More actions · Answer 3

If autogrow is checked, then you really have nothing to worry about.

A.J.
DBA with an attitude

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 4

It is not showing the growth physically in the properties when checked in EM. The management wants to have a 30 gig increase. So how can I work out.

Thanks

Lucky

Adam Wilbur SSCertifiable Points: 7001 More actions · Answer 5

When a database is set to autogrow - that's what it does. It's autogrows the file(s) as nessecary. It is not needed to increase the size at this time. Although if your management team has their heads so far up their ass that they insist this be done, then the syntax would be just as it says in BOL.

E. Modify a file

This example increases the size of one of the files added to the Test1 database in Example B.

USE masterGOALTER DATABASE Test1 MODIFY FILE   (NAME = test1dat3,   SIZE = 20MB)GO

Try actually reading the suggested reading in BOL before you ask more questions that make you look ignorant.

A.J.
DBA with an attitude

DCPeterson SSCoach Points: 15266 More actions · Answer 6

If the database is set to autogrow then you shouldn't have to do anything. SQL Server will allocate new disk space when it needs it. So at least you know that SQL Server won't just stop responding when it uses the existing free space.

It is hard to make specific recommendations without knowing more details of your system, how fast it has grown and its anticipated growth, etc... (offhand, 84GB of free space seems like plenty...)

If your database is going to grow quickly you are probably best off by allocating additional storage manually in order to minimize the effect of the allocation on day to day operations. In which case you would want to issue the following command:

ALTER DATABASE DBName

MODIFY FILE

(NAME = LogicalFileName,

SIZE = 561200MB)

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 7

Hello Peter,

Thanks for the clarification but my dept head writes

If you can force the Autogrow to happen at a 15% free space threshold I’m okay with that .. we had set 15% as the lower limit of available space and we are well below that limit now. . . Past experience has shown the application performance starts to degrade at that threshold. .

already the database is having the filegrowth as 30% and I'm not having any idea, how to proceed further with the information provided by my dept head.

any further guidance would be appreicated.

Lucky

DCPeterson SSCoach Points: 15266 More actions · Answer 8

I think I detect some confusion in your question...

The % specified in the autogrow option doesn't have anything to do with the amount of space that is currently free in the data file. It is the amount of space added to the file when SQL Server grows the file. So, to keep it really simple, if your data file was 100 MB and the autogrow was set to 30%, when SQL Server runs out of room in the data file it would allocate approximately 30 MB (30% of the total size of the file to be expanded).

There is no setting to add space when the free space drops below a certain percentage.

I don't see how having less than 15% free space in your file would adversely affect performance per se. When SQL Server allocates new space, that introduces a significant overhead, particularly when the amount of space added is large (30% of 500 GB is large!) When you are dealing with a large database that is heavily used and grows frequently, you should manually allocate space during "quiet" times thereby minimizing the adverse effects.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/