Solved

How to allow SQL Server 2005 and/or TempDB to use multiple disk drives for storage space?

Posted on 2006-11-17
5
6,165 Views
Last Modified: 2008-01-09
I am running SQL Server 2005 Developer edition on my home machine and i'm trying to execute a query that takes up most of the available space when I try to run it.  I have some other drives other than C on the computer that have 250+ GB available.  Is there any way to tell SQL Server it can use that space to expand into?

Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.SORT temporary run storage:  172560247422976' 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.
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

If it helps the log_reuse_wait_desc column says nothing.
0
Comment
Question by:endrec
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 8

Expert Comment

by:drydenhogg
ID: 17964992
In the SQL Management Studio, open the databases folder, system Databases, right click on tempdb and bring up properties.

go to the files option shown on the left and that will display the current files held for tempdb.

hit add, a new line will appear, name the file (logical name) type data, filegroup primary (this will be the default) initial size (what you wish to allocate to it) and the growth options.

hit ok and you should be set.
0
 
LVL 8

Expert Comment

by:drydenhogg
ID: 17964994
Oh, missed out, the obvious bit doh, set the path of the new file in the filegroup to the appropriate drive that has the space.
0
 

Author Comment

by:endrec
ID: 17965035
I am new to this.  Do you have an example of how to set multiple file(s) filegroups?  Do I need to do this through SQL Server Management Studio, through Alter statements to the DB?
0
 
LVL 8

Accepted Solution

by:
drydenhogg earned 500 total points
ID: 17965075
The SQL Server Management Studio can do it , using the guide above, or in TSQL as follows.

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'YourPreferredName', FILENAME = N'C:\PathToStoreTheFileOn\YourFileName.ndf' , SIZE = 102400KB , FILEGROWTH = 10240KB )
GO

The size / filegrowth you would alter, additional options on that are easier to see / configure via management studio.
0
 

Author Comment

by:endrec
ID: 17974543
Is there anything  I can do about the following error.  I have added an additional file for both the log and the PRIMARY file group (with the method described above).  I reduced the table i'm trying to run a query on to 1/10 of it's original size and I have alloted for over 300GB of disk space but I am still getting the error below.

Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.SORT temporary run storage:  190272201031680' 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.
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question