Running out of space in MSSQL

Posted on 2012-09-17
Medium Priority
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
  • 4
  • 4

Expert Comment

ID: 38407328
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 70

Expert Comment

by:Scott Pletcher
ID: 38407430
The PRIMARY file group is DATA space, NOT log space.  Logs are not in any file group.

Author Comment

ID: 38407445
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?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 70

Expert Comment

by:Scott Pletcher
ID: 38407483
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

ID: 38407542
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

ID: 38407569
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 70

Expert Comment

by:Scott Pletcher
ID: 38407591
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

ID: 38407599
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 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 38407605
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.

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

864 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