Running out of space in MSSQL

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
shpresaAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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.
0
 
dxdinhCommented:
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
http://support.microsoft.com/kb/913399
0
 
Scott PletcherSenior DBACommented:
The PRIMARY file group is DATA space, NOT log space.  Logs are not in any file group.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
shpresaAuthor Commented:
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?
0
 
Scott PletcherSenior DBACommented:
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.
0
 
shpresaAuthor Commented:
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
0
 
shpresaAuthor Commented:
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...
0
 
Scott PletcherSenior DBACommented:
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.
0
 
shpresaAuthor Commented:
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...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.