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
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
The PRIMARY file group is DATA space, NOT log space. Logs are not in any file group.
ASKER
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?
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?
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.
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.
ASKER
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
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
ASKER
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...
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...
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.
ASKER
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...
Do you think I should go and reduce it lil by litle? I am kind of scared to reduce it all together...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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