File group size issues

Question

Post reply

File group size issues

mtz676

SSCrazy Eights

Points: 9212
More actions
August 10, 2023 at 5:50 am

#4257433

Error: Could not allocate space for object because filegroup is full.
dbName FileName type_desc CurrentSizeMB FreeSpaceMB
DBName ABCD_01 ROWS 100000.00 0.00
DBName ABCD_02 ROWS 100000.00 0.00
Autogrowth is 500 MB. Max File size is 1500000. There is plenty of disk space available. Why are we still seeing the error ?

Viewing 15 posts - 1 through 15 (of 21 total)

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

Anoop Agarwal SSC Enthusiast Points: 141 More actions · Answer 1

are you able not able to create any objects in the database or getting creating a specific object

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Not enough info to help here. Are you sure that you created the file group on the correct disk drive?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

If the auto-growth cannot respond before its timeout has passed, you'll receive this error message.

The extent will happen eventually.

Please double check.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 4

mtz676

SSCrazy Eights

Points: 9212

August 18, 2023 at 7:31 am

#4264316

Yes

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

I'd script out the db, make sure you know the default filegroup and files are set up correctly. Likely this is either something you've missed when checking, or as Johan says, you had a delay in the auto grow process responding.

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 6

This appears to be the case. Where do we check the time-out constraint and can we change this to a bigger number ? And any pitfalls if we do so ?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 7

You can increase the timeout for connections, but honestly, to me this is a proactive things DBAs need to do. VLFs are less of a problem in modern versions, but you still want to be adding space to your files (quarterly, IMHO) to ensure there is no need to grow during workloads.

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 8

Yes, that is exactly what is happening. HOw can we make the auto growth happen before the timeout.

Can we increase the timeout ? If yes how and where ?Thanks

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 9

Is there any connection between instant file initialization set to (Y) and sqlserver autogrowth ?

Does IFI(Y) means faster autogrowth ?

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 10

Best is to prevent the autogrow !

Set the file to a size(s) you estimate will be needed to host the data for the next couple of months / years

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 11

mtz676 wrote:

Is there any connection between instant file initialization set to (Y) and sqlserver autogrowth ?
Does IFI(Y) means faster autogrowth ?

For non-LOG files, IFI causes the grow to be 'instantly' because it will only zero out pages when they get used for the first time.

Log files will always be zeroed out at growth time.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 12

How can we check if SAN is too old and the response time from IO is timed out when SQL Server need to auto grow its size ?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 13

You need to look at SAN monitoring.

Johan's advice is what I use. Grow manually before you need the space.

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 14

...so a delay in the auto grow process response results in an error.

can Instant_File_Initialization if enabled lessen the number of times these alerts can happen.

Will IFI = enabled help expand space right away(mdf's) when required with minimal delay as per autogrowth options ?