| Author |
Topic |
|
sql-lover
Yak Posting Veteran
99 Posts |
 Posted - 2015-03-09 : 10:24:57
|
Got this error recently on one of my databases:Could not allocate space for object 'MyIndex1' in database 'MyDatabase' because the 'PRIMARY' filegroup is full. ... and to be honest, it is the 1st time I see it happens. I've used this method for years when a LUN or drive space is running out of space. I just created a secondary data files on a different LUN and enable autogrowth there, while turning autogrowth off on the other one.There are two data files. One has auto-growth disabled but the other does not. Both are on the same FG. Why MS-SQL did not use the other one and instead, gave this error?By chance, both data files reside on same LUN, because the database was restored from a different server. But still, the secondary data file has autogrowth enabled.Any comments? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-09 : 11:20:25
|
| Are you out of free space on the mount point or drive so that it can't autogrow?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
sql-lover
Yak Posting Veteran
99 Posts |
Posted - 2015-03-09 : 11:28:40
|
quote:
Originally posted by tkizer
Are you out of free space on the mount point or drive so that it can't autogrow?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Hi Tara,No, I am not.And I am actually testing this, I am concerned. I restored the database on my lab, digged further , and the issue is related to the Sunday's optimization job. To be more precise, this command:ALTER INDEX [MyIndex] ON [MyDatabase].[dbo].[MyTable] REORGANIZE WITH (LOB_COMPACTION = ON); I checked via DMV internal space utilization on each data file and found that mdf is 100% full (obviously). And secondary is 99% full.Autogrowth on secondary file is 100MB. Initial size for the old one, the one that is disabled is 30300MB. The secondary data file initial size is 9000MB. I expanded to 11GB the secondary data file, ran the reorganize again, and keeps failing on my lab.Does that mean that I will have to move that object to the ndf?? Really? |
|
|
|
sql-lover
Yak Posting Veteran
99 Posts |
Posted - 2015-03-09 : 11:39:13
|
Did this:ALTER INDEX [MyIndex] ON [MyDatabase].[dbo].[MyTable] REBUILDALTER INDEX [MyIndex] ON [MyDatabase].[dbo].[MyTable] REORGANIZE WITH (LOB_COMPACTION = ON); And that fixed the problem.So it seems that the REBUILD , effectively moved the object ( I am guessing) to the other data file?It may be due REORGANIZE command using existing space? And if that's the case, how to avoid this on other databases before it happens? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-09 : 11:40:40
|
| 100MB for autogrowth? Yuck! Set that to a higher value as it's much too small for a database of that size. For a 40GB database, I'd probably use 1024MB if instant file initialization is setup.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
sql-lover
Yak Posting Veteran
99 Posts |
Posted - 2015-03-09 : 11:43:01
|
quote:
Originally posted by tkizer
100MB for autogrowth? Yuck! Set that to a higher value as it's much too small for a database of that size. For a 40GB database, I'd probably use 1024MB if instant file initialization is setup.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Agree, but that was not the root cause of my problem.Do you know if the only way to fix this would be running REBUILD? I'm afraid it is. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-09 : 12:27:02
|
| Well I have seen an ALTER INDEX job fail because the data file couldn't grow fast enough, which is why I suggested increasing the autogrowth. I suspect it's working on a large index.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
sql-lover
Yak Posting Veteran
99 Posts |
Posted - 2015-03-09 : 13:08:46
|
quote:
Originally posted by tkizer
Well I have seen an ALTER INDEX job fail because the data file couldn't grow fast enough, which is why I suggested increasing the autogrowth. I suspect it's working on a large index.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
I increased to 500MB, even 1GB, and it was still failing.In fact, I ended putting 10GB, which is way too much, just because is on my Dev box and I have space and still it fails.It seems that the object is on the mdf file, not the ndf. The fact that autogrowth is disabled and the REORGANIZE always use existing space seem to be affecting the operation. Based on what I tested, the REBUILD fixes this as, based on what I know, the REBUILD recreates or move the Index to the ndf file which has enough space and auto growth is enabled.I am just curious as I've used this secondary or ndf file method for years and never face this issue. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-09 : 13:17:39
|
| Gotcha. Yes a rebuild will be needed. You'll want to rebuild most/all indexes to get them on the new secondary file.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
sql-lover
Yak Posting Veteran
99 Posts |
Posted - 2015-03-09 : 13:22:20
|
quote:
Originally posted by tkizer
Gotcha. Yes a rebuild will be needed. You'll want to rebuild most/all indexes to get them on the new secondary file.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Interesting.I may have to do that on my big clients if I want to be proactive, not reactive.I am using Ola Hallengren's solution, which is fantastic and have zero complaints. But it's smart enough to switch from reorganize to rebuild when needed, based on fragmentation values. Hence, why the job picked reorganize and not a rebuild. |
|
|
|
|
PRIMARY' filegroup is full