Less than 5% free space in File

[ganesh_borase2]

Hello everybody,

I have been suffer from problem that on Executing DB02, the screen shows the warning that Less than 5% free space in file, when i check the free space on file system then it is 87GB. Auto grow set to 10%, previous is 60Mb, but till i have the problem

statics are

Number of files 5

Total Size MB 71,000

Allocated MB 68,326 = 96.23 %

Free MB 2,674 = 3.77 %

RPDDATA1 14,200 1.00 1,420 E:\RPDDATA1\RPDDATA1.mdf 87,865 10% no limit PRIMARY Less than 5% free space in file

RPDDATA2 14,200 1.00 1,420 E:\RPDDATA2\RPDDATA2.ndf 87,865 10% no limit PRIMARY Less than 5% free space in file

RPDDATA3 14,200 1.00 1,420 E:\RPDDATA3\RPDDATA3.ndf 87,865 10% no limit PRIMARY Less than 5% free space in file

RPDDATA4 14,200 1.00 1,420 E:\RPDDATA4\RPDDATA4.ndf 87,865 10% no limit PRIMARY Less than 5% free space in file

PLease suggest me how to solve this problem bcz. Now free space in only 3.77% .

thanks

[former_member438568]

Please review the following document available on sapnet:

SAP with Microsoft SQL Server 2005 - Best Practices for High

Availability, Maximum Performance, and Scalability.pdf.

Having autogrowth on is a nice safety feature which insures that your data files will grow as necessary, provided there is enough disk space available. However, you should not solely rely on autogrow to manage your file sizes. Try and manually grow the files before autogrow kicks in, leaving approximately the same amount of free space in each data file.

You don't need to stress out and worry about the shrinking free space in your data files, but you should proactively manage the file size to achieve better i/o performance. Note 1263390 addresses the same topic and mentions the above document.

[Former Member]

Hello Ganesh,

what is the size allocated to each datafile and what has been currently used.I am not able to get this from the figures you have sent?

but regarding your issue,i guess what is happening is that datafile is increasing till its limit and then when it requires space,it automatically extends by 10%.so when the datafile gets filled,it will again increase by 10 % and hence will enable free space

I would recommend you to tune your datafile space statistics through SQL server agent,allocate more size to each datafile,that will help

Rohit

[ganesh_borase2]

For datafile Autogrow option is set on.But on HDD 85 GB free space is available. then why on executon DB02 it is giving warning message. please suggest me.

second thing, if i added the one more HDD to existing RAID, then which precaution must consider?

I want to add all space of New HDD to existing Drive (d:\)

please suggest How to do?

Thanks

[Former Member]

Hello Ganesh

dont worry about the warning,what haapens is when the data file space is about to get filled,it automatically gets increased by 10%

What I guess is there is no need to increase the space in datafile.

Dont go for a space inrease

Rohit

[ganesh_borase2]

Hi

Thanks for reply, But I am worried

On executin Tx. DB02 ,status show in Yellow mark and on Overview tab following message shown

Number of files 5

Total Size [MB] 71,000

Allocated [MB] 67,702 = 95.35 %

Free [MB] 3,298 = 4.65 % <--- I am worried about this

Once again thanks for prompt reply.

Thanks

[Former Member]

are these the details for database size,size used and size free in DB01

....that means you have aroung 4 gb free now more

see what will happen is that database size will increase almost upto 71,000 Mb and the if there is free space in your drive it will automatically increase,so you should not worry!!

now what is the total size that you have fixed for database,how much is the space free on the drive after this 71Gb is consumed

Rohit

[ganesh_borase2]

No the previously provided details shown on exec. DB02 transaction, Overview tab

On my HDD (RAID server) 87 GB free space available.

If 87GB free space available then why this Alert is display ,like Less than 5% free space in File

thanks

[Former Member]

hello Ganesh

thats what I am trying to tell you,this is the way SQL server works,automatic growth of SQL database

now what will happen is once your 3 gb is occupied and database size goes upto 71,000Mb, database will automatically grow by 10% and use the space from 87 Gb

You dont need to worry about this

If in DB02 you check the free space in the datafiles,you will see a lot of free space

So you have plenty of space free in SQL server,dont worry and ignore these alerts

go through the following article,it might help you understand better:

http://www.sql-server-performance.com/tips/database_settings_p1.aspx

Rohit