A SQL Server 2008 R2 database view unions several transaction table. The query has been slow over the couple of months and the dev team is planning to recreate the query. I need to ETL the results into a flat file and purge them from the DB. When I tried to query the view (of 5M records) this error showed up:
Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.SORT temporary run storage: 140744470495232' 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.
What is the recommended file growth I should set, how is this error managed or solved? Thanks in advance.