SAP SQL : "No Free space in file"

[Former Member]

Hi,

I am new to SQL and I have the issue in ST04 that my database is having the error "no free space in file" for OPEDATA1, OPEDATA2, and OPEDATA3.

Yet I have 40Gb free on my server and the files are set to "autogrowth" and "no limit" as I can see in ST04. Does that mean that it will grow from itself once it's trully full on all file (OPEDATA1, OPEDATA2....) ?

Here's the SQL view

Here's the SAP view

what should I do (IF I have something to do at all) ?

Regards,

Dimitri

[luisdarui · Accepted solution]

Hi Dimitri,

You shouldn't rely on autogrowth on your database. You should manually increase the size of data files, but you should leave autogrowth enabled to avoid data full situations. For the size of your data files, the next growth of 60MB on each file is subpar value. You should consider leaving something like 500mb-1GB. Every time the data file has to grow, it is an expensive operation. If you are growing 60mb every time this will be painfully slow. Furthermore, you should have all data files on the same size and the very same free space. This is ensured when you enable the traceflag -1117.

Please read carefully the SAP Note 1238993.

[hasanajsh · Accepted solution]

Hello Luis,

As far as I understand the point of flag 1117 (which SAP suggests) is to autogrow the files equally automatically and all we have to do is make sure there is enough free disk space. My question is, why are you suggesting that we should manually increase the size of the data files?

Regards

[luisdarui · Accepted solution]

Because you shouldn't rely on autogrowth. This is a very expensive operation (I/O) and will cause, in some cases, a hanging situation that will be even perceived by the end users (depending on the number of times it has to grow automatically or the size that it has to grow).

You as a database/basis administrator would like to increase it manually during a time with low usage.

You as a database/basis administrator would like to have autogrowth enable so in case of an unexpected situation (e.g. some user running a data import and therefore making the database grow significantly), to avoid a DATA FULL situation and causing a system down situation.

[hasanajsh · Accepted solution]

I get your point and it makes sense. Anyway, referring to note 1238993 and Whitepaper SAP on SQL Server 2012 and SQL Server 2014 I understand that, starting with SQL Server 2008, it is possible (and actually SAP is encouraging it ) to rely on automatic file growth. Moreover, implementing the necessary configurations, you will have very little or no performance impact at all.

[jonel_rienton]

Hi - look at your G:\OPEDATA1 and G:\OPEDATA2 paths, if I have to guess, those are probably mount points, i.e., another volume mounted to a folder. Those are the ones probably not having enough free space for the database files to grow. Extend those mount points and that should address your issue.