how to increase database size actually in my company use MSSQL

[Former Member]

Hi Expert,

Can any body tell me how to increase database size actually in my company use MSSQL but i dont know how to increase DB size. in MY PRD system it shows-

Data Files

No. of files-4

Total size(MB)-79,840

Alocated MB-79,825(99.98%)

Free MB-15(0.02%)

LOG

no of files-1

total size(MB)-2385

alocate MB-16

free-2342

recovery model-Full

Reuse wait resion-NOTHING

Now What i do give me suggessin

Thanks In Advance

[ganesh_borase2]

Hi,

Before increase the size of datebase, you have to consider the Free space on that drive.

After increase the size you cannot decrease the size.

For increase size of database, follow the Nibu's' suggest path.

so be careful.

Thanks

Ganesh

[Former Member]

Hi,

MSSQL should manage it's space allocation automatically as long as you had enough space on disk drive and auto mode was on.

Provided Max size of data file should be reasonably high.

Regards.

[Nibu]

Hi Raju,

To increase the DB size, you need to increase the size of each data file of the DB .

Please let us know the MS SQL version you are using. In MS SQL 2005 or 2008 , start the MS SQL studio> login > Select the Data Base > right click > Properties > Files > Initial Size . Here you see the size of data file already allocated . INcrease the size as you wish .

Regards,

Nibu Antony

[Former Member]

Hi Nibu Antony

In our company use MSSQL 2008

so please details in step by step its a request,

Thanks

Raju

[Former Member]

Hi

I am not getting DB size exactly is their requirement for increase DB size pls tell me I wrote everything here of my PRD system DB size.so first what i do? server stop and then Increase DB size.

Data Files

No. of files-4

Total size(MB)-79,840

Alocated MB-79,825(99.98%)

Free MB-15(0.02%)

LOG

no of files-1

total size(MB)-2385

alocate MB-16

free-2342

recovery model-Full

Reuse wait resion-NOTHING

Now What i do give me suggessin

Thanks In Advance

[Former Member]

add one more datafile. How follow how to extend database: technet.microsoft.com/en-us/library/ms175890.aspx

[Former Member]

Hi,

you don't have to stop your database to increase the file size - it's an online operation. If you are more familiar with SQL code here is what you have to do:

use <SID> /*Put your database name here, e.g. PRD*/
go
select file_id, name, physical_name, size 
from sys.database_files 
where type_desc = 'ROWS'

In the resultset you will find a column called 'name' - that are the logical file names that we need for the increase. If you have a standard installation the names should look like <SID>DATA{1-4}. Next step ist the actual increase of the files:

/*Replace <SID> with your DB name*/
alter database <SID> modify file(name='<SID>DATA1', size=25GB) /*25GB ist just an example*/
alter database <SID> modify file(name='<SID>DATA2', size=25GB)
alter database <SID> modify file(name='<SID>DATA2', size=25GB)
alter database <SID> modify file(name='<SID>DATA4', size=25GB)

Thats it. If you run the first statement again, you will see that the filesize has been increased. You can also check on filesystem level.

Regards,

Sven

[Former Member]

Hi

when i tried to change the size of memory of database properties of memory option , which was previously allocated its not changes, so how to change pls tell me or how to increasse page file ,datafile

[Former Member]

Hi Sven,

Thanks for your reply.I am not getting DB(MSSQL2008) size exactly i am check in SAP(ECC6.0) using tcode DB02 and found this msg, is their requirement for increase DB size pls tell me I wrote everything here of my PRD system DB size.so first what i do? datbase size increase or datafile size increase.i am not understand. is their any requirement for writing a sql code.pls tell me.

Data Files

No. of files-4

Total size(MB)-79,840

Alocated MB-79,825(99.98%)

Free MB-15(0.02%)

LOG

no of files-1

total size(MB)-2385

alocate MB-16

free-2342

recovery model-Full

Reuse wait resion-NOTHING

Now What i do give me suggessin

Thanks In Advance

[Former Member]

Hi Rajuka,

the size of your database is the sum of your datafiles. So if you want to increase the size of the database you have to increase the size of the datafiles. Apparently your datafiles have the size of 20GB, so the size of your database is 80GB. If you increase your datafiles to 25GB your database has the size of 100GB.

In order to increase the size of your datafiles you can use the Management Studio as described by Nibu or you can use the provided sql code - it's up to you. There is another option called 'autogrow' that will increase the size of your datafiles automatically if needed. You can check in tcode DB02 on the Files tab (scroll to the right). Autogrow is not the recommended way to increase your datafiles - check with what Claas' wrote in his answer.

Regards,

Sven

[clas_hortien]

Hi,

see the note 363018, it describes in detail how to handle the files within SQL Server.

Please ensure that all data files will have the same amount of freespace, after you have expanded them.

Best regards

Clas

Edited by: Clas Hortien on Dec 8, 2010 8:02 AM