October 22, 2009 at 4:55 pm
I am updating around 48 millons recods and I got this error messa:
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.
How can I fix it? I have for temp:
database_namedatabase_sizeunallocated space
tempdb119164.63 MB119081.59 MB
reserveddataindex_sizeunused
616 KB200 KB328 KB88 KB
October 22, 2009 at 5:47 pm
tempdb would have been flushed after the command fell over, so the temporary objects that had been created in the background by SQL Server no longer exist. It therefore looks as though there's heaps of room in tempdb now, but your command still won't run until you address the issue.
The tempdb database has needed to expand because of the (implicit) creation of temporary tables by your command. It's eventually run into a ceiling, either a maximum file size or a full drive. You need to address whatever the cause was before you can run your statement as is, either by removing the file size limit (you could potentially just increase it, but it's impossible to know how big it needs to be) in the first case or by increasing the free disk space on the drive or creating another tempdb data file on a drive with free space in the second case.
Alternatively, you can rewrite your statement so that it doesn't use tempdb as much, but there's not enough information to know how practical that is.
October 22, 2009 at 6:03 pm
What information you need. I just because of this update I was running,on the drive whe re my db leave we have 10MB left. My temp my mdf file is 121,960,160 kb. Should I shrink db?
Her is my update:
Update table1
set table1.Number = view.number
from view
My view has
select top 2000000 number from table 1 inner joint table1.sku = table3.sku
October 22, 2009 at 6:15 pm
SQL Server never uses the last 8-10MB on a drive.
It sounds like the easiest way to address your issue is going to be to create a second tempdb data file on another drive. Alternatively, or if that's not possible (eg. your server only has one drive), you'll need to rewrite the update statement.
October 22, 2009 at 6:48 pm
How can I re write my update statment? I need to update 48 million ,but now I need to clean my drive space asap. Like I said temp db got very big, what should I do
October 22, 2009 at 7:07 pm
Without knowing the objects involved it's impossible to give anything more than generic advice.
As tempdb is used for explicit temporary tables and table variables, eliminate these if possible.
The other major use is for implicit temporary tables created as part of joins, sorts and the like. Don't do a sort unless it's absolutely necessary and eliminate unnecessary joins.
Failing that, you'll need to look at reducing the volume of data being operated on. Instead of updating all 48m rows, try breaking it into chunks of eg. 6m. You'll have to run the update 8 times (if you go for 6m at a time), but if the command is written the right way then each individual update will use less of tempdb. Wrapping the individual updates into a single transaction will look after any consistency issues.
October 22, 2009 at 7:28 pm
I am only using 2 joint tables,one of that table contains 48 mil. I am not using temp tables. But how can I clean my drive space now?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply