SQL SERVER – FIX : ERROR 1101 : Could not allocate a new page for database because of insufficient disk space in filegroup

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 (http://blog.SQLAuthority.com)

About these ads

9 thoughts on “SQL SERVER – FIX : ERROR 1101 : Could not allocate a new page for database because of insufficient disk space in filegroup

  1. 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

    Like

  2. 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

    Like

  3. 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.

    Like

  4. Worth pointing out that you get the exact same error message when you have reached the 4096 MB limit of SQL server express edition.

    Like

  5. 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.

    Like

  6. 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

    Like

  7. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s