Running out of space in MSSQL

Posted on 2012-09-17
Last Modified: 2012-09-17
Could not allocate space for object 'dbo.#tbl_abc_together' in database 'tempdb' 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.
Do i need to clean the log files. any suggestion?

Log Size (MB)        Log Space Used (%)
19.61719               23.64596
2.492188               52.82132
5.554688               29.40401
54.24219                28.13715
51.17969               13.88528
3.742188       26.91806
26.17969       20.13205
5.492188              11.58428
3.367188      35.23782
3.367188      37.07947
8.992188      23.49044
132.8672      7.538807
6.992188      15.9567
4.554688      21.85892
230.0547      4.97886
146.1797      6.774758
7.429688      24.954
46.49219      14.32112
16.17969      24.76762
3.367188      31.97506
23.80469      19.21972
6.742188      28.70799
417.3672      3.878011
9.992188      15.63722
35.05469      11.41771
38.36719      16.12197
6.117188      27.09132
9.992188      23.27502
17.86719      22.13872
3089.867      1.742876
12.05469      25.90327
27.42969      19.38194
214.1172      5.092449
3.742188      24.50418
12.05469      24.77317
214.1172      5.241818
8.179688      24.02698
27.42969      18.95293
9.929688      23.18057
3.742188      28.52296
120.7422      7.950501
146.1797      6.825196
8.179688      21.29895
10.92969      25.54503
3.742188      29.16232
8.179688      30.45606
5.992188      11.57106
0.7421875      41.18421
0.7421875      68.48684
0.7421875      63.88158
0.7421875      66.9079
0.484375      77.62096
27.42969      19.24665
5.992188      14.80606
27.42969      19.22529
5.992188      15.31128
Question by:shpresa
    LVL 7

    Expert Comment

    1. check to see if the virtual memory files pagefile.sys locate the same drive with the database , if so move it to different drive.
    2. sometimes the files got fragmented ....stop sql and run the defrag command
    3. see if you can find anything from here
    LVL 68

    Expert Comment

    The PRIMARY file group is DATA space, NOT log space.  Logs are not in any file group.

    Author Comment

    I see,

    I noticed I have also set autogrowth by 10% unrestricted.

    I see also under general size 21.13 MB (of one database that I received the error)
    and size available 0.63 MB

    I do have lots of backups .bak from old years that I am deleting them tonight.

    Do you think that would be the reason that can not allocate space?
    LVL 68

    Expert Comment

    SQL is telling you that it cannot allocate more space for tempdb data.

    That means the data drive is too full for SQL to allocate what you are requesting from it.

    You need to check the drive that the tempdb is on and create enough space on it for SQL to be able to do another allocation for tempdb; typically that will involve deleting (or moving) existing files from that drive.  Since the amount is 10% rather than a fixed amount, you will need to know the total data size of tempdb to know how much space you need free on the drive.

    Author Comment

    Hi there ,

    I run a query to find out free space and found out I have
    Free  pages      free space in MB
    120                       0.937500

    Also I have  the total amount of disk space used by all files in tempdb = 12.18 MB

    Is that enough space?

    Thanks again

    Author Comment

    is it like the 10 % of the 12.18 MB?

    So I need to have at least 1.28 MB free space right?

    Any suggestion on how should i make free space.... I am deleting some back up . bak from old times...not sure if that would solve the problem...
    LVL 68

    Expert Comment

    That should solve the disk space problem.  Hopefully SQL will be able to resume running normally -- sometimes SQL can get "stuck" after a drive space shortage.

    Author Comment

    ohh.. If I take out all the old back ups .bak, there are a lot, I would say about  60 G.
    Do you think I should go and reduce it lil by litle? I am kind of scared to reduce it all together...
    LVL 68

    Accepted Solution

    All at once won't hurt anything.  As long as SQL has the space it needs, extra won't hurt anything, and could help a lot if the db needs to grow more.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now