Sometimes, when you have querys that need a lot of temporary space, you could have problems with the space in disk.
I recently have a query that have a lots of joins of very big tables and have aggregations too, which was consuming a lots of space in tempdb.
Just to give an ideia, I have 200 GB free in disk and the query doesn't over because doesn't have more space available.
So, I find a strange solution to resolve this.
First, with the BCP command I run the query and put the results into a file. This saves me lots of space and time. Quicly I just need 23 GB and I have the file ready in 30 minutes. You can use the BCP command this way:
DECLARE @bcpcom VARCHAR(2000)
SET @bcpcom = 'bcp "select * from table" queryout "c:/result.txt" -T -c'
EXEC master..xp_cmdshell @bcpcom
Now you have the result of your query in a file, a txt file. With the bcp command you can give many options, like fields separator, lines separator. For more information about BCP command please visit http://msdn.microsoft.com/en-us/library/ms162802.aspx
To move your results from file to a table in your database you can use the bulk insert command. This command command is really slow, so if you want a fast solution this is not the one. This solution only works for people that have little space available. You can use the bulk insert command like this:
BULK INSERT table FROM "c:/result.txt" WITH (batchsize=100)
You can add many options like the ones used in bcp command like row separator. In this example I only used the batchsize. This option indicates how many rows are processed after the insertion. So you not take up much space in tempdb. For more information about bulk insert you can visit http://msdn.microsoft.com/en-us/library/ms188365.aspx
Note: This solution isn't the best one, it's just one that saves space. I'm sure that if you search more you'll find a better solution to your problem.
