ERROR 1101 : Could not allocate a new page for database because of insufficient disk space in filegroup . Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Fix/Workaround/Solution:
Make sure there is enough Hard Disk space where database files are stored on server.
Turn on AUTOGROW for file groups.
Reference : Pinal Dave (https://blog.sqlauthority.com)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
11 Comments. Leave new
I am still getting the same error after shrinking the tempdb and change it to unrestricted grow.
Just to update you, the reason I was getting the same error even after selecting unrestricted grow for tempdb because my HD was full temp db went up to 30GB. I looked at the sp to see what the hell its doing. Sp has almost 75 to 100 case statement. I don’t know how to make that sp better. Any idea? Thanks
Hi Tariq ,
I have some Tips for you ,
1) Tempdb Allways be on another drive not defoult will help you to increase Performnace ! whenever your SQL server starts it recreate the Tempdb with your defoult size .
2) If you would like to tune your SP’s then you can use Indexs in frequenty use tables ! (Its all about logics and delopments , Less scanning table will give you less execution time , 100 case doesnt matter some we use 1000’s of cases !)
3) If you face a tempdb size problem the , only tempdb is the database which you can move online .
I hope Tips will inline your needs .
Cheers
Praveen
Guys,
I had the same issue, and I applied all the advices that you posted. Thank you very much for sharing your experience.
I’d like to share mine too:
The problem I had was the fact that MS SQL Server 2005 Express has the database size limit of 4GB. I had a few big tables in there, so when I deleted the tables that I don’t need, I got rid of the error that we’re discussing here.
That’s it.
Take care,
Alex.
Worth pointing out that you get the exact same error message when you have reached the 4096 MB limit of SQL server express edition.
Hi
I am running into similar issues…the db size has reached its limits that is 4GB…I was not aware about that limit till date(stupid me) but now I had no useless tables to delete and also I am not able set autogrow option to unrestricted…not sure how deal with this I know if I had to go for SQL server Enterprise edition or standard edition…but it would take time to dwnld install and restore db there..any quick way to get out of this temporaryliy…..Please somebody help me out this ASAP.
Hi Pinal,
I get this error on our server which is primarily for datawarehouse environment.
There are 4 tempdb files created in the default ( Primary ) filegroup and are on different drives which are 23 GB each with autogrowth disabled
But when i see the perfmon counter the details are as below
Disk reads/sec and writes/sec for tempdb1.mdf is approx 544
while for the other 3 ndf files it shows 0.
so i think its not using the space on those three files.
How can i make the server use all 4 files
Please advise.
Thanks,
Sonali
Dear Sonali,
There is no need of multiple Transaction logs there is no performance gain from multiple log files – SQL Server will write to them sequentially.
Reg
Shashank
Thanks you, this resolved issue.
If I pass one parameter to the stored procedure, I’m getting response from the stored procedure. But where as if I another parameter other than the above mentioned parameter, then I’m receiving “Could not allocate a new page for database ‘TEMPDB’ because of insufficient disk space in filegroup ‘DEFAULT’. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.”. I would like to know where the issue lies? Whether the issue is there in my stored procedure or with database? Please do the needful here.