December 17, 2009 at 8:51 am
I am getting this ERROR MSG:
Error: 1105, Severity: 17, State: 2
Could not allocate space for object 'dbo.SORT temporary run storage: 423214136426496' in database 'MyDatabase' because the 'FG4' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Then I look at that FileGroups sizes and see:
Total MB = 13000
Used MB = 11764
Percentage Used = 90.50
So why am I getting this error because the FileGroup has 10% FREE Space?
Does this also mean that whatever data that was trying to be updated/inserted is LOST?
...thanks
December 17, 2009 at 8:56 am
Have you checked the actual hard drive space? Locate not only your user db files, but the tempDB files. If tempDB is full, it can also cause this error.
AFA the data goes, it depends on your definition of the word "lost". If the transactions were logged, then they'll commit when space is freed up. If you were loading data from a file or another database, the file or other database will still have the data. But if this was direct data entry or an update script to change data, then chances are the whole thing was rolled back.
The update script can be re-run. But direct data entry will have to be redone. You won't know for sure, though, until you clear up the space issue.
December 17, 2009 at 9:23 am
Thanks for the quick response, Brandie.
Regarding the actual Hard Drive space. Yes, it has 9.57 GB of FREE space. I also looked at TempDB and it has 1053MB Space Available.
Also, the FileGroup 4 I reference in my problem, I have it's AUTOGROWTH setting turned OFF. I did this trying to contain it's growth. Please also note that I am monitoring it for FREE space and when it dips below 5% I increase the size of the datafile. Currently, it has about 10% FREE Space available.
I'm still confused as to why I'm receiving this error when I see FREE Space in "FileGroup 4" it's complaining about as well as the TempDB database?
December 17, 2009 at 10:01 am
1053MB is NOT MUCH when it comes to TempDB.
Understand that just because it's showing free right now, does not mean there's enough space to do what your transaction is attempting to do. What probably happened is TempDB tried to increase its size past that 1053 MB and when it couldn't, the entire process rolled back.
Here are your options:
1) Redo the script so it uses less overhead
2) Move the tempDB (off hours preferably) to a bigger hard drive
3) Add data files to the tempDB onto other drives to share the burden. Make sure to add them to a different file group and set that file group as the default since you're short on space for the PRIMARY filegroup.
December 17, 2009 at 10:03 am
rew-370421 (12/17/2009)
I'm still confused as to why I'm receiving this error when I see FREE Space in "FileGroup 4" it's complaining about as well as the TempDB database?
Sorry, forgot to answer this in my last post.
Basically what happens is there's a process running in your user database that requires tempDB space. So this error will pop up if you're having space issues in either and be totally and completely non-helpful in determining exactly where the problem is. It'll give you the user db name usually, because that's the calling process. But more often than not (in my experience) the problem is the tempDB whose resources are being called into play by the user db.
It's one of those bizarre Microsoft things.
December 17, 2009 at 12:33 pm
Thanks Brandie !
The TempDB is configured for AUTOGROWTH and there is plenty of disk space. So it's unclear as to why TempDB might be the cause of this.
I don't suppose there is a way to troubleshoot this issue?
If not, what then?
December 18, 2009 at 4:32 am
rew-370421 (12/17/2009)
Thanks for the quick response, Brandie.Regarding the actual Hard Drive space. Yes, it has 9.57 GB of FREE space. I also looked at TempDB and it has 1053MB Space Available.
Also, the FileGroup 4 I reference in my problem, I have it's AUTOGROWTH setting turned OFF. I did this trying to contain it's growth. Please also note that I am monitoring it for FREE space and when it dips below 5% I increase the size of the datafile. Currently, it has about 10% FREE Space available.
I'm still confused as to why I'm receiving this error when I see FREE Space in "FileGroup 4" it's complaining about as well as the TempDB database?
May be you are trying to update/insert data which is more than 10% of free space on your disk.
Switch on the AutoGrowth option which will increase if the data you are updatinig/inserting is more than 10% of the free space.
December 18, 2009 at 4:45 am
rew-370421 (12/17/2009)
The TempDB is configured for AUTOGROWTH and there is plenty of disk space. So it's unclear as to why TempDB might be the cause of this.
You said tempDB only has 1053 Megabytes available. That is NOT plenty of space for this database. My question: Is 1053 the free space in the database or the free space on the hard drive that tempDB resides on?
If the later, you need to follow my option instructions listed above.
If the former, look to see if you've set a max size on tempDB and compare that size to the available space on the hard drive where tempDB resides. You've probably got a limit on tempDB or, as I said before, tempDB is attempting to grow during the process bigger than the available space on the hard drive (or both). You'll need to up the max size or remove it (a very dangerous procedure unless you have so much free space on your hard drive there's no way the server will crash due to lack of disk space).
The only way to troubleshoot this is to watch tempDB very carefully during the process and see how it grows. tempDB tends to grow really quickly when you aren't looking and when you finally do look, you never see the max size it was because it's already shrunk.
December 18, 2009 at 8:38 am
My question: Is 1053 the free space in the database or the free space on the hard drive that tempDB resides on?
Answers:
- 1053MB is the FREE Space available in TempDB
- The hard drive on which TempDB resides has about 9.3 GB FREE space available
HERE is the info on the "D" drive where TempDB exists:
D:\>FreeDisk.Exe
INFO: 9,988,890,624 bytes free on current volume.
HERE is sp_helpdb on TempDB:
namedb_sizeownerdbidcreatedstatuscompatibility_level
tempdb 1269.06 MBsa2Oct 26 2009Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics90
namefileidfilenamefilegroupsizemaxsizegrowthusage
tempdev1D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdfPRIMARY1080256 KBUnlimited10%data only
templog2D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldfNULL219264 KBUnlimited10%log only
December 18, 2009 at 8:57 am
And the user db is on the same drive or a different drive? How much space does it have available to it and is its Autogrowth set to Unlimited?
Run the process again and keep an eye on tempDB's size as it's running. I've had a tempDB try growing over 100 GB before and it was due to poor coding.
December 18, 2009 at 9:13 am
The User Database (it's a Vendor application/database) is on the same drive as TempDB. The drives on located on a SAN configuration. And the FileGroup that the error is referencing is set to a MAXSIZE! So I'm assuming this is the problem.
December 18, 2009 at 10:02 am
do you have multiple filegroups? if one gets full, and you want to insert to it, the process fails. Objects cannot span filegroups.
Note that the recommendation is to turn on autogrow for emergencies. You still monitor as you've stated, but if you're not around when it dips below 5%, it grows.
I'd also grow it before 5%. I'd probably grow it at 10 or 15%and grow it enough to handle 3-6 months of data.
December 18, 2009 at 10:07 am
Yes, there are 7 FileGroups. Those are good recommendations.
thanks Steve !
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply