Problem with tempdb space

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.

Install SQL Server 2008 with SP2 built-in (slipstream installation)

There are many situations where you want to install SQL Server 2008 with the latest service pack built-in, to solve bugs of the installation process.

One of those cases is the installation in Windows Server 2008 and Windows Server 2008 R2 in a clustered environment.

The process of installing the original SQL Server 2008 with the Service Pack at the same time is called splistream, and it's described here:

• First, be sure to download the SQL Server 2008 ISO from the Microsoft site, along with the latest Service Pack for SQL Server 2008.

• In the Service Pack ISO, you will find a file with a similar name to this one:
  
  SQLServer2008SP2-KBXXXXXX-x64-ENU.exe

• Choose the one that is for your CPU architecture (x86 or x64) and extract it with the following command:
  
  SQLServer2008SP2-KBXXXXXX-x64-ENU.exe /x:C:\SP2
  
  Be sure to choose a path with no spaces, or you will have issues later on.

• Before starting the SQL Server 2008 installation, be sure to install the SQL Support files, so that you won't have problems in the installation.
  
  This file should be in C:\SP2\x64\setup\1033\sqlsupport.msi.
  
  The installation is pretty straight-forward (just next-next...)

• Now you just have to start the setup from the original SQL Server 2008 files, with the following command line:
  
  Setup.exe /PCUSource=C:\SP2

• You can validate that you are installing correctly if in the "Ready to install" screen of the SQL Server 2008 installation, there is a "Slipstream" item in the "General Configuration" section.
  
  

Happy SQL Server 2008 configuration!