none
Could not allocate space for object dbo. because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additiona

    Question

  • Hi all .,

    I am getting thi s error in the version

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

    Msg 1105, Level 17, State 2, Procedure USP_SCHEDULER_REPORT, Line 397

    Could not allocate space for object 'dbo.RPT_HOUR_WISE' in database 'sgmsdb' because the 'PRIMARY' 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.

    Please help me out how to solve this

    Regards ,

    Subathra.

    <//span>
    Wednesday, September 11, 2013 12:08 PM

Answers

All replies

  • Hi all .,

    I am getting thi s error in the version

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

    Msg 1105, Level 17, State 2, Procedure USP_SCHEDULER_REPORT, Line 397

    Could not allocate space for object 'dbo.RPT_HOUR_WISE' in database 'sgmsdb' because the 'PRIMARY' 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.

    Please help me out how to solve this

    Regards ,

    Subathra.

    <//span>

    Hello Subathra,

    As the message suggests the disk drive holding the data file is not able to expand either becasue there is disk space issue or you have set limit on filegrowth on db file.To resolve this enable file growth on the data file

    You can add new file (ndf file) and disable autogrowth on previous one .This will allow it to grow and solve your issue.

    Also your SQl server 2008 R2 is not upgraded to SP2 please do it.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Wednesday, September 11, 2013 12:54 PM edited comment
    Wednesday, September 11, 2013 12:12 PM
  • Please let me know how to create ndf file .

    Am working in production server ., client is not allowing me to upgrade it

    Help me on this

    Wednesday, September 11, 2013 12:21 PM
  • Hi,

    Can you clarify that you have checked that you are out of physical disk space?  If you are then you need to create an ndf file on another disk array and use that instead.

    However, if your physical disk drive is not full then you could just have a limited size data file.  Changing this to autogrow would solve your problem.

    To check this, right click your database, select Properties.  In the window that opens, select "Files" in the menu on the left.  Look at the "Autogrowth" settings.  If it says Limited, then change it.

    If you are completely out of disk space then you can create an ndf file.  In the same "Files" window mentioned above...  click "Add" and it will add an ndf file.  You then just need to fill in all the details (size, autogrowth, location etc).

    Thanks,

    Kevin
    • Proposed as answer by udhayan Wednesday, September 11, 2013 12:42 PM
    Wednesday, September 11, 2013 12:33 PM
  • Please let me know how to create ndf file .

    Am working in production server ., client is not allowing me to upgrade it

    Help me on this

    Hello ,

    First confirm whether issue is realted to disk space crunch or Filegrowth restricted you can also get this message in other cases if its due to restriction for file growth  enabling autogrowth will solve the issue or if it Disk space issue use below query to create NDF file on other disk and then disable autogrowth for old one.

    ALTER DATABASE db_name   ADD FILE (
          NAME = Db_name_2,
          FILENAME = 'D:\db_name_data.ndf',
          SIZE = 50MB,
          MAXSIZE = unlimited,
          FILEGROWTH = 100 MB
          )
    go

    Or you can TK on database go to properties...files and then click add...give logical name as db_name_2..filegroup will be primary or whichever FG you want to add autogrowth 200MB and dont restrict it.

    More details on how to add file and filegroup here http://msdn.microsoft.com/en-us/library/bb522469.aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    Wednesday, September 11, 2013 12:40 PM
  • Can follow below steps:

    1) Right click database and select properties
    2) Click on database settings and in size column increase the space.
    Note: Before expansion check database location and space available at the location.
    if enough space simply add more space in the datafile and Click on OK.

    Also can refer below link for TSQL:

    http://technet.microsoft.com/en-us/library/ms17589

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Wednesday, September 11, 2013 12:57 PM
  • Thanks for all .

    I have executed this syntax

    DBCC ShrinkDatabase(tempdb,40)

    Error is resolved .

    But in future while adding records that time also i have to shrink this db ,

    Please clarify on this

    Thanks

    Thursday, September 12, 2013 6:26 AM
  • No , you do not have to shrink any databases

    http://technet.microsoft.com/en-us/library/cc966545.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, September 12, 2013 6:34 AM
  • Thanks for all .

    I have executed this syntax

    DBCC ShrinkDatabase(tempdb,40)

    Error is resolved .

    But in future while adding records that time also i have to shrink this db ,

    Please clarify on this

    Thanks

    Hello ,

    Your solution seems quite different from your question.So problem was tempdb was bloating and expanded to acquire all disk space.

    In future if you are using proc/runnig queries which involve lots of sort,group by ,having your tempdb will be utilized.Either you increase space for tempdb or you optimize your query so that it may not fill tempdb.If shrinking release space you have to do it.But this should be your LAST option

    Please tell me one more thing what about database sgmsdb,is its autogrowth i enabled or have you restricted it.I still feel tempdb might not be real issue.Also 'dbo.RPT_HOUR_WISE'  this table may be view is present in sgmsdb which was not able to grow ,so I just want to confirm from you


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Thursday, September 12, 2013 6:42 AM added line
    Thursday, September 12, 2013 6:37 AM
  • Hi Shanky,

    As it is already enabled in our production server,

    Please find the screen shot of it

    Thanks


    Thursday, September 12, 2013 8:34 AM
  • Hi Shanky,

    Please find the screen shot of it

    Thanks

    Hello ,

    First change your sgms autogrowth from 1 MB to atleast 200 MB .(can vary from 100MB to 500 MB)

    Second change auto_growth for sgms_log fro percent to In Megabytes and put 100 MB value there.These values are tentative to reach optimal value use this link

    https://www.simple-talk.com/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/

    Alo I see unrestricted filegrowth is set for data file which is good.So I assume it was disk Issue.

    Now your data file and tempdb are all on same drive(a Guess) and you ran some query which bloated tempdb and it used all space now there was no space for data file to grow so you got the above error.

    Find out the query and unless you tune it properly please dont run it on prod.If it is app code ask developers to tune it. I would also suggest you to increase space on drive if folowing all above steps does not give any relief (which is highly unlikely).


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Thursday, September 12, 2013 8:48 AM added line
    Thursday, September 12, 2013 8:48 AM
  • ok, My queries are running for halfanhour and has 10lakjs of records . will it cause any issue ? Please clarify on this Thanks
    Thursday, September 12, 2013 9:30 AM
  • ok, My queries are running for halfanhour and has 10lakjs of records . will it cause any issue ? Please clarify on this Thanks

    Hello Subathra,

    Its really difficult to tell.If you feel so that it is really taking long time.See for blockings if any.Please talk to your developer and tune it .

    select * from sys.sysprocesses where blocked <>0


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, September 12, 2013 9:50 AM