September 17, 2009 at 1:04 am
Hi All,
I am trying to run DBCC Checkdb on one of databases.I am getting the following error:-
Could not allocate space for object 'dbo.SORT temporary run storage: 140744104804352' 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.
I have tried all the things:-
- Shrink log file, data file of tempdb database & of that particular database
- Check the autogrow of tempdb database
- Check the disk space
But nothing work out. Can somebody have any idea? This problem is specific to only one database and for all other databases DBCC Checkdb is working fine.
September 17, 2009 at 1:14 am
what is the database size?
What is the database recovery model?
Howmuch freespace is there in database as well as on disk?
what is the size of tempdb?
Looks like this is a space issue.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
September 17, 2009 at 1:23 am
Following are the details required by you:-
Database size - More than 200GB
Database recovery model - Simple
Freespace in database as well as on disk - Total Size of disk is 34 GB & free space is 32 GB but the scenario is like this -
This drive has 3 disk each of more than 200 GB mounted on it. On 2 of mounted disk, data & log file of database are.
Size of tempdb - 4 GB
September 17, 2009 at 1:23 am
September 17, 2009 at 1:27 am
Vishal,
That's the best practice.But in my scenario, it's not like that and even we can't do without appropriate permissions & all....Now what can be done to resolve that problem that i stated in first post..
September 17, 2009 at 1:34 am
vchawla-1033989 (9/17/2009)
Vishal,That's the best practice.But in my scenario, it's not like that and even we can't do without appropriate permissions & all....Now what can be done to resolve that problem that i stated in first post..
From your error, the primary filegroup of your TempDB is full.
Shrink may not help here because the space that shrink is generating may be too less for the operation you want to perform.
What you can do here is add a secondary data file in your temDB and make sure that the secondary file resides on some other drive.
A DBA should have permission to perform the mentioned activities.
September 17, 2009 at 2:42 am
Vishal Singh (9/17/2009)
vchawla-1033989 (9/17/2009)
Vishal,That's the best practice.But in my scenario, it's not like that and even we can't do without appropriate permissions & all....Now what can be done to resolve that problem that i stated in first post..
From your error, the primary filegroup of your TempDB is full.
Shrink may not help here because the space that shrink is generating may be too less for the operation you want to perform.
What you can do here is add a secondary data file in your temDB and make sure that the secondary file resides on some other drive.
A DBA should have permission to perform the mentioned activities.
How much space is left on the drive that tempdb is on, is autogrowth switched on for the tempdb and is the maximum file size set to restricted file growth or unrestricted file growth for the tempdb.
If the tempdb database was too big, some people would suggest that you restart the sql service which will recreate the tempdb database or as suggested by others add additional files/filegroups on another volume that has sufficent space.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 17, 2009 at 3:27 am
Total Size of disk is 34 GB & free space is 32 GB but the scenario is like this -
This drive has 3 disk each of more than 200 GB mounted on it. On 2 of mounted disk, data & log file of database are.
tempdb size is very small & set to unrestricted growth
September 17, 2009 at 4:47 am
I guess tempdb needs more diskspace.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply