Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I installed Standard Edition on a server with a 500 GB drive. Now the system is giving me error message of disk space is not available, so I added a new hard disk. How can I tell SQL Server to start using disk space from the new drive.

share|improve this question
2  
Dettach database, move it to another disk, and re-attach. –  danihp May 20 '13 at 13:09
    
msdn.microsoft.com/en-us/library/ms189133.aspx –  outcoldman May 20 '13 at 13:10
    
Is your data data-file and log data-file(s) on different spindles (harddrives) ? –  granadaCoder May 20 '13 at 13:22
1  
Whilst SQL Server can use multiple files for a single database, there's more planning required around it than just "use the next hard disk" –  Damien_The_Unbeliever May 20 '13 at 13:31
add comment

migrated from stackoverflow.com May 20 '13 at 13:38

This question came from our site for professional and enthusiast programmers.

1 Answer

I'd suggest instead that you just move the database to the new, bigger drive. In addition to avoiding the complexity of splitting your database across multiple drives, you also free up disk space for the other databases that will stay on the original disk.

First, execute the following:

USE yourdb;
GO
EXEC sp_helpfile;

In most cases you should get two rows here, one for the data file, one for the log file (you can tell which is which by the usage column). You'll want to make note of the values in the name column for substitution below.

Then, in a period of downtime:

BACKUP DATABASE yourdb TO DISK = 'newdrive:\somepath\yourdb.bak' WITH INIT;
GO
RESTORE DATABASE yourdb WITH REPLACE, RECOVERY, 
  MOVE 'data_file_name' TO 'newdrive:\datapath\yourdb.mdf',
  MOVE 'log_file_name'  TO 'newdrive:\datapath\yourdb.ldf';

This is a little more work but a lot safer than detach / attach. Primarily because if something goes wrong with the restore, you still have the backup and/or the original database. If something goes wrong during the attach, you have 0 copies of your database.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.