Update Statistics fails with Error Number:–1073548784 “Could not allocate space for object ‘dbo.SORT”
Error Number: -1073548784
Executing the query “UPDATE STATISTICS [dbo].[***********]
WITH FULLSCAN
” failed with the following error: “Could not allocate space for object ‘dbo.SORT temporary run
storage: 142101814116352’ 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.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
Here are some great recommendation from David Levy, Microsoft Premier Field Engineer.
It sounds like TempDB is not large enough on that system. I would suggest running DBCC CHECKDB WITH ESTIMATEONLY to see how much TempDB space CheckDB thinks it needs. That should be more than enough to cover the spill from sp_updatestats.
In general, I try to steer people away from maintenance plans for things like this. My preference is for each database to get a reindex job using a script that first checks for fragmentation before reindexing to save on time and server resources. There are a number of really good scripts out there that do this. In the reindex job I also like to run sp_updatestats as a second step to update only the statistics that need updating. Finally, putting each database CheckDB in a separate job and logging to a text file makes it incredibly easy to chase down CheckDB failures when they happen. You get the exact database with the failure and the complete log every time where maintenance plans can lose some of that data.
Here are the big 3 links for re-indexing scripts.
http://ola.hallengren.com/
http://weblogs.sqlteam.com/tarad/archive/2009/11/03/DefragmentingRebuilding-Indexes-in-SQL-Server-2005-and-2008Again.aspx
http://sqlfool.com/2011/06/index-defrag-script-v4-1/
I then use this to run all of the jobs in sequence : http://adventuresinsql.com/2010/01/stored-procedure-to-sequentially-run-sql-agent-jobs/.
Chris Skorlinski
Microsoft SQL Server Escalation Services
